MySQL中计算中位数的简单方法

Question

MySQL中计算中位数的简单方法

266

如何在 MySQL 中计算中位数，最好是简单快速的方法？我已经使用 AVG(x) 找到了平均值，但我很难找到一种简单的方式来计算中位数。目前，我正在将所有行返回到 PHP 中，排序，然后选择中间行，但肯定有一些简单的方法可以在单个 MySQL 查询中完成。

示例数据：

按照val排序得到2 2 3 4 7 8 9，因此中位数应为4，而SELECT AVG(val)的结果为5。

- davr

7

自MariaDB 10.3版本以来，新增了一个函数——中位数函数，请参考 https://mariadb.com/kb/en/library/median/。 - berturion

50个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Danylo Zherebetskyy · Answer 1

通常情况下，我们可能需要计算中位数不仅针对整个表，而且还要针对与我们的ID相关的聚合。换句话说，在我们的表中为每个ID计算中位数，其中每个ID有许多记录。（良好的性能并适用于许多SQL + 修复偶数和奇数的问题，有关不同中位数方法的性能更多信息请参见https://sqlperformance.com/2012/08/t-sql-queries/median）

SELECT our_id, AVG(1.0 * our_val) as Median
FROM
( SELECT our_id, our_val, 
  COUNT(*) OVER (PARTITION BY our_id) AS cnt,
  ROW_NUMBER() OVER (PARTITION BY our_id ORDER BY our_val) AS rn
  FROM our_table
) AS x
WHERE rn IN ((cnt + 1)/2, (cnt + 2)/2) GROUP BY our_id;

希望有所帮助。

- rhanqtl · Answer 2

自 MySQL 8.0 版本以来，支持窗口函数，您可以使用 ROW_NUMBER 或 DENSE_RANK（不要使用 RANK，因为它会给相同的值分配相同的排名，就像体育排名一样）：

SELECT AVG(t1.val) AS median_val
  FROM (SELECT val, 
               ROW_NUMBER() OVER(ORDER BY val) AS rownum
          FROM data) t1,
       (SELECT COUNT(*) AS num_records FROM data) t2
 WHERE t1.row_num IN
       (FLOOR((t2.num_records + 1) / 2), 
        FLOOR((t2.num_records + 2) / 2));

- Chris Knoll · Answer 3

我有一个包含约十亿行的数据库，我们需要确定该组中位数年龄。对十亿行进行排序很困难，但是如果您汇总可以找到的不同值（年龄从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
;

这个查询取决于你的数据库支持窗口函数（包括 ROWS UNBOUNDED PRECEDING），但如果不支持，可以通过将 aggData CTE 自连接并将所有先前的总计聚合到“累积”列中来解决，该列用于确定哪个值包含指定的百分位数。上面的示例计算了 p10、p25、p50（中位数）、p75 和 p90。

-Chris

- mr.baby123 · Answer 4

摘自: http://mdb-blog.blogspot.com/2015/06/mysql-find-median-nth-element-without.html

我建议另一种方法，不需要联接，但使用字符串来处理

我没有在大型数据表中进行测试，但在小/中型表中它的表现非常好。

好处在于它还可以通过分组来返回多个项目的中位数。

这里有一个测试表的测试代码：

DROP TABLE test.test_median
CREATE TABLE test.test_median AS
SELECT 'book' AS grp, 4 AS val UNION ALL
SELECT 'book', 7 UNION ALL
SELECT 'book', 2 UNION ALL
SELECT 'book', 2 UNION ALL
SELECT 'book', 9 UNION ALL
SELECT 'book', 8 UNION ALL
SELECT 'book', 3 UNION ALL

SELECT 'note', 11 UNION ALL

SELECT 'bike', 22 UNION ALL
SELECT 'bike', 26

以及每个组中寻找中位数的代码：

SELECT grp,
         SUBSTRING_INDEX( SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val), ',', COUNT(*)/2 ), ',', -1) as the_median,
         GROUP_CONCAT(val ORDER BY val) as all_vals_for_debug
FROM test.test_median
GROUP BY grp

输出：

grp | the_median| all_vals_for_debug
bike| 22        | 22,26
book| 4         | 2,2,3,4,7,8,9
note| 11        | 11

- yuhanluo · Answer 5

这种方式似乎可以在不使用子查询的情况下包含偶数和奇数计数。

SELECT AVG(t1.x)
FROM table t1, table t2
GROUP BY t1.x
HAVING SUM(SIGN(t1.x - t2.x)) = 0

- jentek llc · Answer 6

你可以使用窗口函数 row_number() 来回答查询以找到中位数。

select val 
from (select val, row_number() over (order by val) as rownumber, x.cnt 
from data, (select count(*) as cnt from data) x) abc
where rownumber=ceil(cnt/2);

- Victor Vulovic · Answer 7

我没有将这个解决方案的性能与此处发布的其他答案进行比较，但我发现这是最直观易懂的，并且涵盖了用于计算中位数的数学公式的全部范围。换句话说，这个解决方案足够强大，适用于奇数和偶数数据集：

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>

- Abhishek Sengupta · Answer 8

尝试类似这样的内容：

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开始索引，即行号现在从0开始而不是1。

**

- Nikhil Agarwal · Answer 9

对于一个表站和列lat_n，这里是获取中位数的MySQL代码：

set @rows := (select count(1) from station);
set @v1 := 0;
set @sql1 := concat('select lat_n into @v1 from station order by lat_n asc limit 1 offset ', ceil(@rows/2) - 1);
prepare statement1 from @sql1;
execute statement1;
set @v2 := 0;
set @sql2 := concat('select lat_n into @v2 from station order by lat_n asc limit 1 offset ', ceil((@rows + 1)/2) - 1);
prepare statement2 from @sql2;
execute statement2;
select (@v1 + @v2)/2;

- Cameron · Answer 10

如果这是MySQL，现在有窗口函数，您可以按照以下方式执行（假设您想要四舍五入到最近的整数 - 否则只需将ROUND替换为CEIL或FLOOR等）。以下解决方案适用于表格，无论它们具有偶数行还是奇数行：


WITH CTE AS (
    SELECT val,
            ROW_NUMBER() OVER (ORDER BY val ASC) AS rn,
            COUNT(*) OVER () AS total_count
    FROM data
)
SELECT ROUND(AVG(val)) AS median
FROM CTE
WHERE
    rn BETWEEN
    total_count / 2.0 AND
    total_count / 2.0 + 1;

我认为这个帖子上一些较新的回答已经在探讨这种方法，但似乎人们过于深思熟虑，所以请考虑这个改进版本。无论使用哪种SQL语言，2021年没有理由写一个巨大的代码段，其中包含多个子查询，只是为了获取中位数。然而，请注意，如果要查找连续系列的中位数，则上述查询仅适用于此。当然，无论行数如何，有时人们会区分所谓的离散中位数和连续系列的插值中位数。

如果您被要求查找离散系列的中位数，并且表格具有偶数行，则上述解决方案对您无效，您应该恢复使用其他解决方案之一，例如TheJacobTaylor的解决方案。

下面的第二个解决方案是TheJacobTaylor的略微修改版本，其中我明确说明了。这适用于具有奇数行的表格，无论您被要求找到连续系列还是离散系列的中位数，但我会在要求找到离散系列的中位数时特别使用它。否则，请使用第一个解决方案。这样，您就永远不必考虑数据包含“偶数”或“奇数”个数据点的问题。


SELECT x.val AS median
FROM data x
CROSS JOIN data y
GROUP BY x.val
HAVING SUM(SIGN(1 - SIGN(y.val - x.val))) = (COUNT(*) + 1) / 2;

最后，您可以使用内置函数轻松地在PostgreSQL中完成此操作。这里有一个很好的解释，以及关于离散中位数与插值中位数的有效摘要。

https://leafo.net/guides/postgresql-calculating-percentile.html#calculating-the-median