用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。
当前回答
set @r = 0;
select
case when mod(c,2)=0 then round(sum(lat_N),4)
else round(sum(lat_N)/2,4)
end as Med
from
(select lat_N, @r := @r+1, @r as id from station order by lat_N) A
cross join
(select (count(1)+1)/2 as c from station) B
where id >= floor(c) and id <=ceil(c)
其他回答
我发现这个答案非常有用——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));
set @r = 0;
select
case when mod(c,2)=0 then round(sum(lat_N),4)
else round(sum(lat_N)/2,4)
end as Med
from
(select lat_N, @r := @r+1, @r as id from station order by lat_N) A
cross join
(select (count(1)+1)/2 as c from station) B
where id >= floor(c) and id <=ceil(c)
MariaDB / MySQL:
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) );
Steve Cohen指出，在第一次传递之后，@rownum将包含总行数。这可用于确定中值，因此不需要第二次传递或连接。
此外，AVG(dd.val)和dd.row_number IN(…)用于在有偶数条记录时正确地产生中位数。推理:
SELECT FLOOR((3+1)/2),FLOOR((3+2)/2); -- when total_rows is 3, avg rows 2 and 2
SELECT FLOOR((4+1)/2),FLOOR((4+2)/2); -- when total_rows is 4, avg rows 2 and 3
最后，MariaDB 10.3.3+包含一个MEDIAN函数
知道确切的行数，你可以使用这个查询:
SELECT <value> AS VAL FROM <table> ORDER BY VAL LIMIT 1 OFFSET <half>
Where <half> = ceiling(<size> / 2.0) - 1
在某些情况下，中位数的计算如下:
“中位数”是数字列表中按值排序时的“中间”值。对于偶数集，中位数是两个中间值的平均值。 我为此创建了一个简单的代码:
$midValue = 0;
$rowCount = "SELECT count(*) as count {$from} {$where}";
$even = FALSE;
$offset = 1;
$medianRow = floor($rowCount / 2);
if ($rowCount % 2 == 0 && !empty($medianRow)) {
$even = TRUE;
$offset++;
$medianRow--;
}
$medianValue = "SELECT column as median
{$fromClause} {$whereClause}
ORDER BY median
LIMIT {$medianRow},{$offset}";
$medianValDAO = db_query($medianValue);
while ($medianValDAO->fetch()) {
if ($even) {
$midValue = $midValue + $medianValDAO->median;
}
else {
$median = $medianValDAO->median;
}
}
if ($even) {
$median = $midValue / 2;
}
return $median;
返回的$中位数将是所需的结果:-)
