用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。
当前回答
create table med(id integer);
insert into med(id) values(1);
insert into med(id) values(2);
insert into med(id) values(3);
insert into med(id) values(4);
insert into med(id) values(5);
insert into med(id) values(6);
select (MIN(count)+MAX(count))/2 from
(select case when (select count(*) from
med A where A.id<B.id)=(select count(*)/2 from med) OR
(select count(*) from med A where A.id>B.id)=(select count(*)/2
from med) then cast(B.id as float)end as count from med B) C;
?column?
----------
3.5
(1 row)
OR
select cast(avg(id) as float) from
(select t1.id from med t1 JOIN med t2 on t1.id!= t2.id
group by t1.id having ABS(SUM(SIGN(t1.id-t2.id)))=1) A;
其他回答
归档完美中位数的单个查询:
SELECT
COUNT(*) as total_rows,
IF(count(*)%2 = 1, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val SEPARATOR ','), ',', 50/100 * COUNT(*)), ',', -1) AS DECIMAL), ROUND((CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val SEPARATOR ','), ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) + CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val SEPARATOR ','), ',', 50/100 * COUNT(*)), ',', -1) AS DECIMAL)) / 2)) as median,
AVG(val) as average
FROM
data
我刚刚在网上的评论中找到了另一个答案:
对于几乎所有SQL中的中位数: SELECT x.val from data x, data y GROUP BY x.val 总和(符号(1-SIGN (y.val-x.val))) = (COUNT (*) + 1) / 2
确保列有良好的索引，并且索引用于筛选和排序。与解释计划核对。
select count(*) from table --find the number of rows
计算“中值”行号。可能使用:median_row = floor(count / 2)。
然后把它从列表中挑出来:
select val from table order by val asc limit median_row,1
这将返回您想要的值的一行。
我发现这个答案非常有用——https://www.eversql.com/how-to-calculate-median-value-in-mysql-using-a-simple-sql-query/
SET @rowindex := -1;
SELECT
AVG(g.grade)
FROM
(SELECT @rowindex:=@rowindex + 1 AS rowindex,
grades.grade AS grade
FROM grades
ORDER BY grades.grade) AS g
WHERE
g.rowindex IN (FLOOR(@rowindex / 2) , CEIL(@rowindex / 2));
我有一个包含大约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。
屁股的
试着这样做:
SELECT
CAST (AVG(val) AS DECIMAL(10,4))
FROM
(
SELECT
val,
ROW_NUMBER() OVER( ORDER BY val ) -1 AS rn,
COUNT(1) OVER () -1 AS cnt
FROM STATION
) as tmp
WHERE rn IN (FLOOR(cnt/2),CEILING (cnt/2))
**
注意:-1的原因是使它的索引为0 .i。E行号 现在从0开始，而不是1
**
