mysql 计算众数_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))