用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 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
其他回答
基于@bob的回答，这将查询泛化为能够返回多个中位数，并按某些标准分组。
想想，例如，一个车场二手车的中位数销售价格，按年-月分组。
SELECT
period,
AVG(middle_values) AS 'median'
FROM (
SELECT t1.sale_price AS 'middle_values', t1.row_num, t1.period, t2.count
FROM (
SELECT
@last_period:=@period AS 'last_period',
@period:=DATE_FORMAT(sale_date, '%Y-%m') AS 'period',
IF (@period<>@last_period, @row:=1, @row:=@row+1) as `row_num`,
x.sale_price
FROM listings AS x, (SELECT @row:=0) AS r
WHERE 1
-- where criteria goes here
ORDER BY DATE_FORMAT(sale_date, '%Y%m'), x.sale_price
) AS t1
LEFT JOIN (
SELECT COUNT(*) as 'count', DATE_FORMAT(sale_date, '%Y-%m') AS 'period'
FROM listings x
WHERE 1
-- same where criteria goes here
GROUP BY DATE_FORMAT(sale_date, '%Y%m')
) AS t2
ON t1.period = t2.period
) AS t3
WHERE
row_num >= (count/2)
AND row_num <= ((count/2) + 1)
GROUP BY t3.period
ORDER BY t3.period;
我的代码，高效，没有表或额外的变量:
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
上面的方法似乎对我有用。
我有一个包含大约10亿行的数据库，我们需要它来确定集合中的年龄中位数。对十亿行进行排序是困难的，但如果你将可以找到的不同值(年龄范围从0到100)聚合在一起，你可以对这个列表进行排序，并使用一些算术魔术来找到你想要的任何百分位数，如下所示:
with rawData(count_value) as
(
select p.YEAR_OF_BIRTH
from dbo.PERSON p
),
overallStats (avg_value, stdev_value, min_value, max_value, total) as
(
select avg(1.0 * count_value) as avg_value,
stdev(count_value) as stdev_value,
min(count_value) as min_value,
max(count_value) as max_value,
count(*) as total
from rawData
),
aggData (count_value, total, accumulated) as
(
select count_value,
count(*) as total,
SUM(count(*)) OVER (ORDER BY count_value ROWS UNBOUNDED PRECEDING) as accumulated
FROM rawData
group by count_value
)
select o.total as count_value,
o.min_value,
o.max_value,
o.avg_value,
o.stdev_value,
MIN(case when d.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,
MIN(case when d.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,
MIN(case when d.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,
MIN(case when d.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,
MIN(case when d.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value
from aggData d
cross apply overallStats o
GROUP BY o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value
;
这个查询取决于你的db支持窗口函数(包括ROWS UNBOUNDED precede)，但如果你没有，这是一个简单的事情，将aggData CTE与自身连接，并将所有先前的总数聚合到' cumulative '列，用于确定哪个值包含指定的预分词。上面的示例计算p10、p25、p50(中位数)、p75和p90。
屁股的
一个简单的方法来计算中位数在MySQL
set @ct := (select count(1) from station);
set @row := 0;
select avg(a.val) as median from
(select * from table order by val) a
where (select @row := @row + 1)
between @ct/2.0 and @ct/2.0 +1;
