用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 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>
我的代码,高效,没有表或额外的变量:
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;
不幸的是,无论是TheJacobTaylor还是velcrow的答案都不会返回当前版本MySQL的准确结果。
从上面来看,魔术贴的答案是接近的,但它不能正确计算具有偶数行数的结果集。中位数定义为1)奇数集上的中间数,或2)偶数集上两个中间数的平均值。
所以,这里是魔术贴的解决方案修补处理奇数和偶数集:
SELECT AVG(middle_values) AS 'median' FROM (
SELECT t1.median_column AS 'middle_values' FROM
(
SELECT @row:=@row+1 as `row`, x.median_column
FROM median_table AS x, (SELECT @row:=0) AS r
WHERE 1
-- put some where clause here
ORDER BY x.median_column
) AS t1,
(
SELECT COUNT(*) as 'count'
FROM median_table x
WHERE 1
-- put same where clause here
) AS t2
-- the following condition will return 1 record for odd number sets, or 2 records for even number sets.
WHERE t1.row >= t2.count/2 and t1.row <= ((t2.count/2) +1)) AS t3;
要使用它,请遵循以下3个简单步骤:
将上面代码中的“median_table”(出现2次)替换为您的表名
将“median_column”(3次)替换为您希望为其查找中位数的列名
如果你有一个WHERE条件,用WHERE条件替换“WHERE 1”(2次)