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返回。

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

  • 0
    点赞
  • 10
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值