用MySQL计算中位数最简单(希望不会太慢)的方法是什么?我已经使用AVG(x)来寻找平均值，但我很难找到一个简单的方法来计算中位数。现在，我将所有的行返回到PHP，进行排序，然后选择中间的行，但是肯定有一些简单的方法可以在一个MySQL查询中完成它。
示例数据:
id | val
--------
1 4
2 7
3 2
4 2
5 9
6 8
7 3
对val排序得到2 2 3 4 7 8 9，因此中位数应该是4，而SELECT AVG(val) == 5。
当前回答
MySQL从8.0版本开始支持窗口函数，您可以使用ROW_NUMBER或DENSE_RANK(不要使用RANK，因为它将相同的RANK分配给相同的值，就像在体育排名):
SELECT AVG(t1.val) AS median_val
FROM (SELECT val,
ROW_NUMBER() OVER(ORDER BY val) AS rownum
FROM data) t1,
(SELECT COUNT(*) AS num_records FROM data) t2
WHERE t1.row_num IN
(FLOOR((t2.num_records + 1) / 2),
FLOOR((t2.num_records + 2) / 2));
其他回答
不幸的是，无论是TheJacobTaylor还是velcrow的答案都不会返回当前版本MySQL的准确结果。
从上面来看，魔术贴的答案是接近的，但它不能正确计算具有偶数行数的结果集。中位数定义为1)奇数集上的中间数，或2)偶数集上两个中间数的平均值。
所以，这里是魔术贴的解决方案修补处理奇数和偶数集:
SELECT AVG(middle_values) AS 'median' FROM (
SELECT t1.median_column AS 'middle_values' FROM
(
SELECT @row:=@row+1 as `row`, x.median_column
FROM median_table AS x, (SELECT @row:=0) AS r
WHERE 1
-- put some where clause here
ORDER BY x.median_column
) AS t1,
(
SELECT COUNT(*) as 'count'
FROM median_table x
WHERE 1
-- put same where clause here
) AS t2
-- the following condition will return 1 record for odd number sets, or 2 records for even number sets.
WHERE t1.row >= t2.count/2 and t1.row <= ((t2.count/2) +1)) AS t3;
要使用它，请遵循以下3个简单步骤:
将上面代码中的“median_table”(出现2次)替换为您的表名 将“median_column”(3次)替换为您希望为其查找中位数的列名 如果你有一个WHERE条件，用WHERE条件替换“WHERE 1”(2次)
你可以使用这里的用户定义函数。
按维度分组的中位数:
SELECT your_dimension, avg(t1.val) as median_val FROM (
SELECT @rownum:=@rownum+1 AS `row_number`,
IF(@dim <> d.your_dimension, @rownum := 0, NULL),
@dim := d.your_dimension AS your_dimension,
d.val
FROM data d, (SELECT @rownum:=0) r, (SELECT @dim := 'something_unreal') d
WHERE 1
-- put some where clause here
ORDER BY d.your_dimension, d.val
) as t1
INNER JOIN
(
SELECT d.your_dimension,
count(*) as total_rows
FROM data d
WHERE 1
-- put same where clause here
GROUP BY d.your_dimension
) as t2 USING(your_dimension)
WHERE 1
AND t1.row_number in ( floor((total_rows+1)/2), floor((total_rows+2)/2) )
GROUP BY your_dimension;
我的代码，高效，没有表或额外的变量:
SELECT
((SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(val order by val), ',', floor(1+((count(val)-1) / 2))), ',', -1))
+
(SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(val order by val), ',', ceiling(1+((count(val)-1) / 2))), ',', -1)))/2
as median
FROM table;
我有下面的代码，我在HackerRank上找到的，它很简单，适用于每一种情况。
SELECT M.MEDIAN_COL FROM MEDIAN_TABLE M WHERE
(SELECT COUNT(MEDIAN_COL) FROM MEDIAN_TABLE WHERE MEDIAN_COL < M.MEDIAN_COL ) =
(SELECT COUNT(MEDIAN_COL) FROM MEDIAN_TABLE WHERE MEDIAN_COL > M.MEDIAN_COL );
