MySQL计算中位数

 

方法1：（当数据量为偶数个时，产生的中位数为中间两位数的平均值）

SELECT AVG(dd.val) as median_val
FROM (
SELECT d.val, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
  FROM data d, (SELECT @rownum:=0) r
  WHERE d.val is NOT NULL
  -- put some where clause here
  ORDER BY d.val
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );

 

方法2：（当数据量为偶数个时，产生的中位数为null）

SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val))) = (COUNT(*)+1)/2

 

 

参考：

https://qastack.cn/programming/1291152/simple-way-to-calculate-median-with-mysql

https://qastack.cn/programming/1342898/function-to-calculate-median-in-sql-server