用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。
当前回答
我的代码，高效，没有表或额外的变量:
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;
其他回答
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(
GROUP_CONCAT(field ORDER BY field),
',',
((
ROUND(
LENGTH(GROUP_CONCAT(field)) -
LENGTH(
REPLACE(
GROUP_CONCAT(field),
',',
''
)
)
) / 2) + 1
)),
',',
-1
)
FROM
table
上面的方法似乎对我有用。
我没有将这个解决方案的性能与这里发布的其他答案进行比较，但我发现这个解决方案是最容易理解的，并且涵盖了计算中位数的全部数学公式。换句话说，这个解决方案对于偶数和奇数数据集足够健壮:
SELECT CASE
-- odd-numbered data sets:
WHEN MOD(COUNT(*), 2) = 1 THEN (SELECT median.<value> AS median
FROM
(SELECT t1.<value>
FROM (SELECT <value>,
ROW_NUMBER() OVER(ORDER BY <value>) AS rownum
FROM <data>) t1,
(SELECT COUNT(*) AS num_records FROM <data>) t2
WHERE t1.rownum =(t2.num_records) / 2) as median)
-- even-numbered data sets:
ELSE (select (low_bound.<value> + up_bound.<value>) / 2 AS median
FROM
(SELECT t1.<value>
FROM (SELECT <value>,
ROW_NUMBER() OVER(ORDER BY <value>) AS rownum
FROM <data>) t1,
(SELECT COUNT(*) AS num_records FROM <data>) t2
WHERE t1.rownum =(t2.num_records - 1) / 2) as low_bound,
(SELECT t1.<value>
FROM (SELECT <value>,
ROW_NUMBER() OVER(ORDER BY <value>) AS rownum
FROM station) t1,
(SELECT COUNT(*) AS num_records FROM data) t2
WHERE t1.rownum =(t2.num_records + 1) / 2) as up_bound)
END
FROM <data>
如果MySQL有ROW_NUMBER，那么MEDIAN是(受SQL Server查询的启发):
WITH Numbered AS
(
SELECT *, COUNT(*) OVER () AS Cnt,
ROW_NUMBER() OVER (ORDER BY val) AS RowNum
FROM yourtable
)
SELECT id, val
FROM Numbered
WHERE RowNum IN ((Cnt+1)/2, (Cnt+2)/2)
;
如果您有偶数个条目，则使用IN。
如果你想找到每个组的中位数，那么只需要在你的OVER子句中PARTITION BY组。
Rob
知道确切的行数，你可以使用这个查询:
SELECT <value> AS VAL FROM <table> ORDER BY VAL LIMIT 1 OFFSET <half>
Where <half> = ceiling(<size> / 2.0) - 1
这些方法从同一个表中选择两次。如果源数据来自一个昂贵的查询，这是一种避免运行两次的方法:
select KEY_FIELD, AVG(VALUE_FIELD) MEDIAN_VALUE
from (
select KEY_FIELD, VALUE_FIELD, RANKF
, @rownumr := IF(@prevrowidr=KEY_FIELD,@rownumr+1,1) RANKR
, @prevrowidr := KEY_FIELD
FROM (
SELECT KEY_FIELD, VALUE_FIELD, RANKF
FROM (
SELECT KEY_FIELD, VALUE_FIELD
, @rownumf := IF(@prevrowidf=KEY_FIELD,@rownumf+1,1) RANKF
, @prevrowidf := KEY_FIELD
FROM (
SELECT KEY_FIELD, VALUE_FIELD
FROM (
-- some expensive query
) B
ORDER BY KEY_FIELD, VALUE_FIELD
) C
, (SELECT @rownumf := 1) t_rownum
, (SELECT @prevrowidf := '*') t_previd
) D
ORDER BY KEY_FIELD, RANKF DESC
) E
, (SELECT @rownumr := 1) t_rownum
, (SELECT @prevrowidr := '*') t_previd
) F
WHERE RANKF-RANKR BETWEEN -1 and 1
GROUP BY KEY_FIELD
