Purpose
This function returns the median of a set of values, which is the middle value when the values are sorted. If the set contains an even number of values, the function returns the average of the two middle values. It can be used as an aggregate or analytic function.
Note
- When used as an analytic function, the
OVERclause is required to define the window for calculation. It calculates over a set of rows and returns multiple values. - When used as an aggregate function, it calculates over a set of rows and returns a single value. In this case, the
OVERclause is not required.
Syntax
MEDIAN(expr) [ OVER (query_partition_clause) ]
Parameters
Parameter |
Description |
|---|---|
| expr | The name of the array for which to calculate the median. The parameter type must be a numeric data type or implicitly convertible to a numeric data type. |
| OVER | Use the OVER clause to define the window for calculation. For more information, see Analytic Function Description. |
Return type
If only
expris specified, the function returns a value of the same numeric data type as the parameter.If the
OVERclause is specified, the database determines the parameter with the highest numeric precedence, implicitly converts the other parameters to this data type, and returns the value of this data type.
Examples
Create a table named tbl1 and insert data.
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
Aggregate function example
Calculate the median of column col4.
obclient> SELECT MEDIAN(col4) FROM tbl1;
+--------------+
| MEDIAN(COL4) |
+--------------+
| 15 |
+--------------+
1 row in set
Analytic function example
Group by column col1 and calculate the median of column 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