MEDIAN

MariaDB starting with 10.3.3

The MEDIAN() window function was first introduced with in MariaDB 10.3.3.

Syntax

MEDIAN(median expression) OVER (
  [ PARTITION BY partition_expression ] 
)

Description

MEDIAN() is a window function that returns the median value of a range of values.

It is a specific case of PERCENTILE_CONT, with an argument of 0.5 and the ORDER BY column the one in MEDIAN's argument.

MEDIAN(<median-arg>) OVER ( [ PARTITION BY partition_expression] )

Is equivalent to:

PERCENTILE_CONT(0.5) WITHIN 
  GROUP (ORDER BY <median-arg>) OVER ( [ PARTITION BY partition_expression ])

Examples

CREATE TABLE book_rating (name CHAR(30), star_rating TINYINT);

INSERT INTO book_rating VALUES ('Lord of the Ladybirds', 5);
INSERT INTO book_rating VALUES ('Lord of the Ladybirds', 3);
INSERT INTO book_rating VALUES ('Lady of the Flies', 1);
INSERT INTO book_rating VALUES ('Lady of the Flies', 2);
INSERT INTO book_rating VALUES ('Lady of the Flies', 5);

SELECT name, median(star_rating) OVER (PARTITION BY name) FROM book_rating;
+-----------------------+----------------------------------------------+
| name                  | median(star_rating) OVER (PARTITION BY name) |
+-----------------------+----------------------------------------------+
| Lord of the Ladybirds |                                 4.0000000000 |
| Lord of the Ladybirds |                                 4.0000000000 |
| Lady of the Flies     |                                 2.0000000000 |
| Lady of the Flies     |                                 2.0000000000 |
| Lady of the Flies     |                                 2.0000000000 |
+-----------------------+----------------------------------------------+

See Also

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

© 2021 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/median/