MySQL 求众数、平均数、中位数

求众数、平均数、中位数，我们有很多方法可以实现，下面记录一下在数据库中求众数、中位数、平均数的方法

我们先随便写入一些数据供测试用(ceshi_salaries)

求众数

1.使用having

首先用count()对工资计数，找到出现最多的次数

select COUNT(*) from ceshi_salaries GROUP BY salary

然后就是嵌套找到出现次数大于等于最多次数的元素

SELECT salary,COUNT(*) AS cnt
FROM ceshi_salaries
GROUP BY salary
HAVING count(*) >= ALL(SELECT COUNT(*) FROM ceshi_salaries GROUP BY salary) 

查询结果

2. 使用视图＋max

//创建视图
create view max_a as 
SELECT salary,COUNT(*) as cnt
FROM ceshi_salaries 
GROUP BY salary;

//查询
select salary,cnt
from max_a
where cnt=(select max(cnt) from max_a)

求平均数

求平均数就是正常的sum()求和，然后除去记录数量

SELECT SUM(salary)/COUNT(DISTINCT emp_no) AS avg_salary
FROM ceshi_salaries

mysql中也有直接的函数能够得到结果

SELECT AVG(salary) from ceshi_salaries

求中位数

求中位数，最重要的是要知道总数是奇数还是偶数

• N为奇数，中位数排序编号是(N+1)/2=N/2+0.5
• N为偶数，中位数排序编号是N/2和N/2+1

通过ROW_NUMBER()窗口函数计算出记录的个数，通过OVER()进行排序

SELECT AVG(salary) FROM (
	SELECT salary, ROW_NUMBER() OVER(ORDER BY salary ASC) AS 'row_num',
	count(*) over() AS 'cnt'
	FROM ceshi_salaries) t
	WHERE row_num in (FLOOR(cnt/2)+1, IF(cnt%2!=0,FLOOR(cnt/2)+1,cnt/2))

SELECT AVG(DISTINCT a.salary) AS median_salary
FROM
	(SELECT a.salary
	 FROM ceshi_salaries AS a, ceshi_salaries AS b
	 GROUP BY a.salary
	 HAVING SUM(CASE WHEN b.salary >= a.salary THEN 1 ELSE 0 END) >= COUNT(*) / 2 
	 AND SUM(CASE WHEN b.salary <= a.salary THEN 1 ELSE 0 END) >= COUNT(*) / 2 ) AS a;