用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 grp_field, t1。val FROM (
SELECT grp_field, @rownum:=IF(@s = grp_field, @rownum + 1,0) AS row_number,
@s:=IF(@s = grp_field, @s, grp_field) AS sec, d.val
FROM data d, (SELECT @rownum:=0, @s:=0
ORDER BY grp_field, d.val
)作为t1 JOIN (
SELECT grp_field, count(*)为total_rows
数据d
GROUP BY grp_field
)为t2
在t1。Grp_field = t2.grp_field
在t1.row_number =地板(total_rows / 2) + 1;
上面的大多数解决方案只适用于表中的一个字段,您可能需要获得查询中多个字段的中位数(第50百分位数)。
我用这个:
SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(
GROUP_CONCAT(field_name ORDER BY field_name SEPARATOR ','),
',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS `Median`
FROM table_name;
你可以将上面例子中的“50”替换为任何百分位数,这是非常有效的。
只要确保你有足够的内存给GROUP_CONCAT,你可以改变它:
SET group_concat_max_len = 10485760; #10MB max length
详情:http://web.performancerasta.com/metrics-tips-calculating-95th-99th-or-any-percentile-with-single-mysql-query/
根据魔术贴的答案,对于那些必须根据另一个参数分组的东西做中位数的人来说
SELECT grp_field, t1。val FROM (
SELECT grp_field, @rownum:=IF(@s = grp_field, @rownum + 1,0) AS row_number,
@s:=IF(@s = grp_field, @s, grp_field) AS sec, d.val
FROM data d, (SELECT @rownum:=0, @s:=0
ORDER BY grp_field, d.val
)作为t1 JOIN (
SELECT grp_field, count(*)为total_rows
数据d
GROUP BY grp_field
)为t2
在t1。Grp_field = t2.grp_field
在t1.row_number =地板(total_rows / 2) + 1;
下面的查询对于奇数行和偶数行都非常有效。在子查询中,我们正在寻找前后行数相同的值。对于奇数行的情况,having子句的值将为0(前后相同的行数将抵消符号)。
类似地,对于偶数行,having子句对于两行(中间的两行)的计算结果为1,因为它们(总的来说)前后的行数相同。
在外层查询中,我们将平均出单个值(奇数行)或(偶数行2个值)。
select avg(val) as median
from
(
select d1.val
from data d1 cross join data d2
group by d1.val
having abs(sum(sign(d1.val-d2.val))) in (0,1)
) sub
注意:如果你的表有重复的值,上面的having子句应该更改为下面的条件。在这种情况下,可能有一些值超出了原来的可能性(0,1)下面的条件将使这个条件动态,并在重复的情况下工作。
having sum(case when d1.val=d2.val then 1 else 0 end)>=
abs(sum(sign(d1.val-d2.val)))