MEDIAN()
Function Description
MEDIAN() is used to return the median value of a set of values, that is, sort a set of values and return the value in the middle. If the parameter set contains even numbers, the function returns the average of the two numbers in the middle. This can be used as an aggregation or analysis function.
Function Syntax
> MEDIAN(expr)
Parameter definition
| Parameters | Description |
|---|---|
| expr | Required parameters. Specifies the array name of the required value, the parameter type belongs to the numeric data type or can be implicitly converted to a numeric data type. |
Return type
This function returns the same data type as the numeric data type of the parameter.
Example
mysql> select median(null);
+-------------------+
| median(null) |
+-------------------+
| NULL |
+-------------------+
1 row in set (0.00 sec)
drop table if exists t1;
create table t1 (a int,b int);
insert into t1 values (1,null);
mysql> select median(b) from t1;
+---------------+
| median(b) |
+---------------+
| NULL |
+---------------+
1 row in set (0.01 sec)
insert into t1 values (1,1);
mysql> select median(b) from t1;
+---------------+
| median(b) |
+---------------+
| 1 |
+---------------+
1 row in set (0.01 sec)
insert into t1 values (1,2);
mysql> select median(b) from t1;
+---------------+
| median(b) |
+---------------+
| 1.5 |
+---------------+
1 row in set (0.01 sec)
mysql> select median(b) from t1 group by a order by a;
+---------------+
| median(b) |
+---------------+
| 1.5 |
+---------------+
1 row in set (0.00 sec)
insert into t1 values (2,1),(2,2),(2,3),(2,4);
mysql> select median(b) from t1 group by a order by a;
+---------------+
| median(b) |
+---------------+
| 1.5 |
| 2.5 |
+---------------+
2 rows in set (0.01 sec)