MySQL求中位数、众数

MySQL并未内置计算中位数、众数的函数。因此,我们需要自己创建查询。

MySQL插入测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `income` (
`name` VARCHAR(10) NOT NULL DEFAULT '',
`income` INT(11) NOT NULL DEFAULT '0'
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;

INSERT INTO test.income (name, income) VALUES ('麻子', 20000);
INSERT INTO test.income (name, income) VALUES ('李四', 12000);
INSERT INTO test.income (name, income) VALUES ('张三', 10000);
INSERT INTO test.income (name, income) VALUES ('王二', 16000);
INSERT INTO test.income (name, income) VALUES ('王五', 16000);
INSERT INTO test.income (name, income) VALUES ('土豪', 40000);
1
2
3
4
5
6
7
8
9
10
+--------+--------+
| name | income |
+--------+--------+
| 麻子 | 20000 |
| 李四 | 12000 |
| 张三 | 10000 |
| 王二 | 16000 |
| 土豪 | 40000 |
| 王五 | 16000 |
+--------+--------+

中位数查询

具体题目分析

假如我们想从“income”列中查找中位数。来看看我们要创建的查询是什么算法:

  • 对“income”排序并为排序后的每一行附加序号(行号)
  • 如果总行数是奇数,找到位于有序列表中间位置的值。
  • 如果总行数是偶数,找到位于有序列表中间两个位置的值。
  • 计算上面第 (2) 步和第 (3) 取得值的平均值。
  • 返回这个平均值,它就是中位数。

MySQL实现中位数查询

1
2
3
4
5
6
7
8
9
10
SET @rownum := -1;

SELECT AVG(t.income)
FROM
(
SELECT @rownum:=@rownum + 1 AS rowindex,income.income AS income
FROM income
ORDER BY income.income
) AS t
WHERE t.rowindex IN (FLOOR(@rownum / 2) , CEIL(@rownum / 2));
1
2
3
4
5
+---------------+
| AVG(t.income) |
+---------------+
| 16000.0000 |
+---------------+

下面解释一下上面的代码:

  • 先从内部的一个子查询开始 —— 为 @rowindex 赋值的子查询会为每个收入赋予一个自增后的序号,并对 income 排序。
  • 有了排好序的收入列表之后,外层查询会获取位中间位置的值。如果数组元素是奇数个,两个值都是取的位于正中那个值。
  • 然后,外层查询的 SELECT 子句返回上述两个值的平均值作为中位数。

众数查询

MySQL实现众数查询

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT income,COUNT(income)
FROM income
GROUP BY income
HAVING COUNT(income) >=
(
SELECT MAX(t1.cnt)
FROM
(
SELECT COUNT(*) AS cnt
FROM income
GROUP BY income
) AS t1
);
1
2
3
4
5
+--------+---------------+
| income | COUNT(income) |
+--------+---------------+
| 16000 | 2 |
+--------+---------------+
赞赏一杯咖啡
0%