MySQL 获取总数，最大数，最小数，平均数，众数，中位数

1、问题来源

接到一个需求，获取分数的各种统计数据，类似于学生考试成绩的一种情况。

主要涉及，总数，最大数，最小数，平均数，中位数，众数。

2、数据模拟

2.1、设计表 Stu

设计学生表，字段名称和分数，主键自增。

CREATE TABLE `stu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8;

模拟数据：

INSERT INTO stu ( NAME, score )
VALUES
	( 'a0', 60 ),
	( 'a1', 70 ),
	( 'a2', 61 ),
	( 'a3', 70 ),
	( 'a4', 81 ),
	( 'a5', 90 ),
	( 'a6', 60 ),
	( 'a7', 70 ),
	( 'a8', 88 ),
	( 'a9', 87 ),
	( 'a10', 67 ),
	( 'a11', 45 ),
	( 'a12', 99 ),
	( 'a13', 95 )

查看结果：（CSDN上传图片总是失败…）

3、执行统计

3.1、总数，最大数，最小数，平均数

这几个统计数据可以一起，只需要一条SQL即可。

注意： 平均数会出现小数位数多的情况，此处round(avg(x),2) 保留2位。

SELECT
	count( score ) count,
	max( score ) max,
	min( score ) min,
	round( avg( score ), 2 ) avg 
FROM
	stu

3.2、求众数

• 先进行分组统计

SELECT
	score,
	count( score ) count
FROM
	stu 
GROUP BY
	score

结果： score,count 组成 list，其实我们只需要最大的，这里我们需要数量最多的。

因为众数可能存在多个，假如严格要求每个众数都列举，那么我暂时想到的是程序处理，假如按照最多的其中之一，可以这样：

SELECT
	max( count ),
	score 
FROM
( SELECT score, count( score ) count 
	FROM stu 
	GROUP BY score 
	ORDER BY count DESC 
) t

修改补充：上述SQL在MySQL的高版本可能不适用，因为SQL要求严格。
错误提示：

Error querying database.  Cause: java.sql.SQLSyntaxErrorException: In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column ...

可以修改为：

select score, count( score ) count 
from stu
GROUP BY score 
ORDER BY count DESC 
limit 1

3.2、中位数

中位数存在2种情况，奇数的取最中间，偶数取中间两数的平均值。

在区分情况之前，我们先看下两个函数 group_concat 和 substring_index 。

• group_concat

执行SQL：

select GROUP_CONCAT(score ORDER BY score) from stu;

作用，把score 的值用逗号拼接。

• substring_index

执行SQL：

select SUBSTRING_INDEX('a,b,c',',',1); # a
select SUBSTRING_INDEX('a,b,c',',',2); # a,b
select SUBSTRING_INDEX('a,b,c',',',-1); # c
select SUBSTRING_INDEX('a,b,c',',',-2); # b,c

作用，按照逗号截取字符串，正数下标从1开始，从左向右。负数下标从-1开始，从右向左。

综合以上，我们就可以获取中位数：

• 奇数情况

select SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(score ORDER BY score),',',(count(0)+1)/2+1),',',-1)
from stu

• 偶数情况

select
( SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(score ORDER BY score),',',count(0)/2),',',-1) +
 SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(score ORDER BY score),',',count(0)/2+1),',',-1)) /2
as median
from stu

说明下：

奇数，(count(0)+1)/2 代表获取最中间的数，SUBSTRING_INDEX 函数获取1到中间数的字符串，然后再截取最后一个字符。
偶数，代表两数中间的平均值，和奇数类似。

测试验证：

select SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4,5',',',3),',',-1) as median ； # 结果 3

select (SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4',',',2),',',-1) +
            SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4',',',3),',',-1))/2 as median # 结果 2.5

这里查询中位数还会出现一个问题就是GROUP_CONCAT 超过数据库长度。默认长度是1024。

查询当前数据库GROUP_CONCAT 长度：

show variables like '%group_concat_max_len%'

修改GROUP_CONCAT 长度，这里设置成1024*1024，可以根据业务设置合适的长度：

SET GLOBAL group_concat_max_len = 1024*1024
SET session group_concat_max_len = 1024*1024

也可以在数据库配置文件my.conf 中设置长度

[mysqld]
group_concat_max_len=1048576

4、总结

统计总数，平均数，最大，最小，只需要调用函数即可，当然，调用次数越少越好。

众数注意可能不是一个的问题。

中位数比较麻烦，要考虑奇数偶数，并了解 函数 group_concat 和 substring_index。

中位数参考了博客 https://www.cnblogs.com/yujianchun/p/8529543.html
博客种统计中位数没有考虑+1,这应该是编写的小错误，不影响思路。

另外我这里需求还有统计分组的情况，类似成绩的不及格，良好，优秀。
这里我复用了统计 总数，最大数，最小数，平均数 的函数，这个增加两个参数上限分数和下限分数，并且只返回一个count，
我这里使用MyBatis 并使用Map返回。

我们在开发的过程中，在可读性的基础上，尽可能的复用代码，进行抽象。