Purpose
You can call this function to return the median of a group of values after sorting. If the parameter set contains an even number of values, the function returns the average of the two numbers in the middle. You can use it as an aggregate or analytic function.
Note
- When you use it 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 it 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 | Specifies the name of the array in which the median is required. The parameter is of the numeric data type or can be implicitly converted to the 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 about analytic functions, see Analytic functions. |
Return types
If you only specify
expr, the function returns the same numeric data type as the parameter value.If the
OVERclause is specified, the database determines the parameter with the highest numeric precedence, implicitly converts the remaining parameter values to this data type, and returns a result of this data type.
Examples
Execute the following statement to create table 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
Aggregate example
Calculate the median of the col4 column.
obclient> SELECT MEDIAN(col4) FROM tbl1;
+--------------+
MEDIAN(COL4)
+--------------+
15
+--------------+
1 row in set
Analytic example
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