MEDIAN()

函数说明

MEDIAN() 用于返回一组数值的中值，即将一组数值排序后返回居于中间的数值。如果参数集合中包含偶数个数值，该函数将返回位于中间的两个数的平均值。可以将其用作聚合或分析函数。

函数语法

1. > MEDIAN(expr)

参数释义

返回类型

该函数返回与参数的数值数据类型相同的数据类型。

示例

1. mysql> select median(null);
2. +--------------+
3. | median(null) |
4. +--------------+
5. |         NULL |
6. +--------------+
7. 1 row in set (0.00 sec)

9. drop table if exists t1;
10. create table t1 (a int,b int);
11. insert into t1 values (1,null);

13. mysql> select median(b) from t1;
14. +-----------+
15. | median(b) |
16. +-----------+
17. |      NULL |
18. +-----------+
19. 1 row in set (0.01 sec)

21. insert into t1 values (1,1);

23. mysql> select median(b) from t1;
24. +-----------+
25. | median(b) |
26. +-----------+
27. |         1 |
28. +-----------+
29. 1 row in set (0.01 sec)

31. insert into t1 values (1,2);

33. mysql> select median(b) from t1;
34. +-----------+
35. | median(b) |
36. +-----------+
37. |       1.5 |
38. +-----------+
39. 1 row in set (0.01 sec)

41. mysql> select median(b) from t1 group by a order by a;
42. +-----------+
43. | median(b) |
44. +-----------+
45. |       1.5 |
46. +-----------+
47. 1 row in set (0.00 sec)

49. insert into t1 values (2,1),(2,2),(2,3),(2,4);

51. mysql> select median(b) from t1 group by a order by a;
52. +-----------+
53. | median(b) |
54. +-----------+
55. |       1.5 |
56. |       2.5 |
57. +-----------+
58. 2 rows in set (0.01 sec)