mySQL求中位数、众数

mysql 取中位数、众数

-- 求众数
SELECT score, count(score) from scores 
 GROUP BY score
HAVING count(score) >=
(SELECT max(a.b) from 
 (SELECT count(score) as b from scores
GROUP BY score) a )

思路：
对数据进行排序，利用id确定中位数的位置，最大id跟最小id取平均值即中位数。
– 数据量为基数，平均值刚好为中位数
– 数据量为偶数，平局值通过FLOOR() ceil () 函数可定位到中位数，取均值即可。

1.如自带ID

SELECT AVG(score) from 
(SELECT score , id FROM scores ) a 
  WHERE id in (SELECT FLOOR(((MAX(id)+MIN(id))/2)) as m from scores b UNION
SELECT CEIL(((MAX(id)+MIN(id))/2)) as m from scores c );

2.如果没有ID

-- 求中位数
-- 申明变量
-- @rowindex := @rowindex + 1 自增长
-- FLOOR()向下取整 ceil 向上取整

set @rowindex := -1;
SELECT AVG(a.score) from (
 SELECT (@rowindex := @rowindex + 1) as rowindex,score from scores
ORDER BY score )a 
 WHERE rowindex in (FLOOR(@rowindex/2+1),CEIL(@rowindex/2+1))