Purpose
MEDIAN() returns the median of a group of values after sorting. If the value set contains an even number of values, the function returns the average of the two numbers in the middle. You can use this function as an aggregate or analytic function.
Note
- When you use this function as an analytic function, use the
OVERclause to define a window over the data on which the function operates. The function operates on a group of rows to return a list of values. - When you use this function as an aggregate function, the function operates on a set of rows and returns a single value. You do not need to add the
OVERclause.
Syntax
MEDIAN(expr) [ OVER (query_partition_clause) ]
Parameters
| Parameter | Description |
|---|---|
| expr | The name of the array in which the median is required. The parameter is of a numeric data type or a data type that can be implicitly converted to a numeric data type. |
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Return type
If you specify only
expr, the function returns the same numeric data type as the argument.If the
OVERclause is specified, the database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to this data type, and returns a result of this data type.
Examples
Create a table named tbl1 and insert data into it.
obclient> CREATE TABLE tbl1(col1 INT,col2 VARCHAR(10),col3 INT,col4 INT);
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES(1,'A1',8,12),(1,'A2',10,15),(1,'A3',11,16),
(2,'B1',9,14),(2,'B2',10,15),(2,'B3',8,13),(2,'B4',11,16),(3,'C1',8,18),
(3,'C2',9,16),(3,'C3',10,15),(3,'C4',11,12),(3,'C5',12,10);
Query OK, 12 rows affected
Records: 12 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM tbl1;
+------+------+------+------+
| COL1 | COL2 | COL3 | COL4 |
+------+------+------+------+
| 1 | A1 | 8 | 12 |
| 1 | A2 | 10 | 15 |
| 1 | A3 | 11 | 16 |
| 2 | B1 | 9 | 14 |
| 2 | B2 | 10 | 15 |
| 2 | B3 | 8 | 13 |
| 2 | B4 | 11 | 16 |
| 3 | C1 | 8 | 18 |
| 3 | C2 | 9 | 16 |
| 3 | C3 | 10 | 15 |
| 3 | C4 | 11 | 12 |
| 3 | C5 | 12 | 10 |
+------+------+------+------+
12 rows in set
Example of an aggregate function
Calculate the median of the col4 column.
obclient> SELECT MEDIAN(col4) FROM tbl1;
+--------------+
| MEDIAN(COL4) |
+--------------+
| 15 |
+--------------+
1 row in set
Example of an analytic function
Group data by the col1 column and obtain the median of col4.
obclient> SELECT col1,col4,MEDIAN(col4) OVER(PARTITION BY col1) "MEDIAN"
FROM tbl1;
+------+------+--------+
| COL1 | COL4 | MEDIAN |
+------+------+--------+
| 1 | 12 | 15 |
| 1 | 15 | 15 |
| 1 | 16 | 15 |
| 2 | 14 | 14.5 |
| 2 | 15 | 14.5 |
| 2 | 13 | 14.5 |
| 2 | 16 | 14.5 |
| 3 | 18 | 15 |
| 3 | 16 | 15 |
| 3 | 15 | 15 |
| 3 | 12 | 15 |
| 3 | 10 | 15 |
+------+------+--------+
12 rows in set
