SQL如何实现分组后的中位数统计_PERCENTILE窗口函数

首页

数据库

热心网友

转载

2026-04-30

要算分组后的中位数，优先用PERCENTILE_CONT(0.5)，因其返回插值结果（真正数学中位数），而PERCENTILE_DISC(0.5)仅返回实际存在的某个值；二者均需配合OVER(PARTITION BY...ORDER BY...)使用，不支持纯GROUP BY语法。

SQL如何实现分组后的中位数统计_PERCENTILE窗口函数

PERCENTILE_CONT 和 PERCENTILE_DISC 的核心区别

先说结论：计算分组中位数，PERCENTILE_CONT(0.5)通常是更优的选择，而不是PERCENTILE_DISC(0.5)。原因很简单，前者能给出数学意义上的“真”中位数，因为它会进行插值计算；后者则比较“实在”，只返回数据集里实际存在的某个值，这可能导致结果出现偏差。

举个例子就明白了。假设某个分组的数据是 [1, 3, 5, 7]。用PERCENTILE_CONT(0.5)算，它会取中间两个数3和5的平均值，返回4.0。而PERCENTILE_DISC(0.5)呢？它只会老老实实地返回排序后处于中间位置的那个值，也就是3。哪个更符合你对“中位数”的直觉？显然是前者。

使用这两个函数时，排序的列不能包含NULL值，否则含有NULL的行会被直接忽略。
它们都属于窗口函数，必须配合OVER (PARTITION BY ... ORDER BY ...)子句使用。想用纯GROUP BY聚合的写法是行不通的，直接报错。
支持情况方面，PostgreSQL、Oracle、SQL Server（2012及以上版本）以及Snowflake都支持。但MySQL用户得注意了，截至目前（8.0版本），它还不支持这两个函数。

在 GROUP BY 场景下正确调用 PERCENTILE_CONT

很多朋友容易在这里踩坑：想按部门分组计算薪资中位数，下意识就写成了类似SELECT dept, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) FROM emp GROUP BY dept的语句。这其实是PostgreSQL或Oracle里的一种聚合函数语法，并非通用的窗口函数写法，而且并非所有数据库都支持。

更通用、跨平台的写法是利用窗口函数：

SELECT DISTINCT
  dept,
  PERCENTILE_CONT(0.5) OVER (PARTITION BY dept ORDER BY salary) AS median_salary
FROM emp;

这里有个关键点：DISTINCT是必须的。因为窗口函数会为原始表的每一行都计算并返回一个值，同一个部门内所有行的中位数结果是一样的，不去重的话，你会得到重复的多行。用DISTINCT压缩一下，每个部门就只剩一行清晰的结果了。

当然，如果你的业务场景需要保留每一行原始数据，同时附带上该组的中位数（比如后续要做薪资离差分析），那就别加DISTINCT，直接把窗口函数作为一列计算即可。
ORDER BY子句是强制性的，而且通常只允许一个排序表达式。试图用多列排序（比如ORDER BY salary, id）可能会导致结果不稳定，尤其在薪资有重复值的时候。
不同数据库对数据类型有要求，比如在BigQuery里，PERCENTILE_CONT的ORDER BY列必须是数值或日期类型，用字符串会直接报错。

MySQL 用户的替代方案：用 ROW_NUMBER 模拟中位数

对于还在使用MySQL 8.0的朋友，虽然没有原生PERCENTILE_CONT，但完全可以利用ROW_NUMBER()和COUNT(*)这两个窗口函数来手动定位中位数。

WITH ranked AS (
  SELECT
    dept,
    salary,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary) AS rn,
    COUNT(*) OVER (PARTITION BY dept) AS cnt
  FROM emp
)
SELECT
  dept,
  A VG(salary) AS median_salary
FROM ranked
WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2))
GROUP BY dept;

这个逻辑巧妙地覆盖了数据量为奇数和偶数的两种情况：奇数行时，取最中间的那一个数；偶数行时，取中间两个数的平均值。

务必记得用A VG()函数包裹最终结果。如果不这么做，当数据量为偶数时，查询会返回两行数据，而不是一个平均值。
公式里的FLOOR和CEIL在计数（cnt）为1时，都会返回1，确保了边界情况的安全性。
性能上，这种模拟方法肯定比不上数据库原生的PERCENTILE_CONT函数。如果数据量很大，建议在(dept, salary)上建立复合索引来加速。

容易被忽略的 NULL 和数据类型陷阱

中位数计算对NULL值特别敏感——默认情况下，PERCENTILE_CONT会直接跳过NULL。但问题来了，如果你的业务逻辑里，NULL代表“薪资未发放”，并且你希望这些记录参与排序（比如把它们视为最小值），那就需要提前处理。

把NULL当最小值处理：可以使用ORDER BY COALESCE(salary, -999999)。不过要小心，替换的值必须与原始列的数据类型匹配。
把NULL当最大值处理：在PostgreSQL或Oracle中，可以用ORDER BY salary DESC NULLS LAST。但请注意，NULLS LAST这个语法并非所有数据库都支持。
例如在SQL Server中，不支持NULLS LAST，就需要变通一下，写成：ORDER BY CASE WHEN salary IS NULL THEN 1 ELSE 0 END, salary。
数据类型也会捣乱。在BigQuery中，PERCENTILE_CONT对整型（INT64）和浮点型（FLOAT64）的返回类型不同：如果输入是整型，结果也会被截断为整型。为了避免精度损失，建议显式转换：CAST(salary AS FLOAT64)。

说到底，中位数并不是一个“开箱即用”的统计指标。它的具体行为高度依赖于数据库的实现细节以及你对数据分布的预先假设。有个非常实用的建议：在编写复杂的中位数查询之前，不妨先执行一句SELECT * FROM emp WHERE dept = 'xxx' ORDER BY salary，亲眼看一下目标分组内数据的真实排序情况。很多时候，这比反复查阅文档能更快地帮你定位问题所在。

来源:https://www.php.cn/faq/2333776.html

免责声明：游乐网为非赢利性网站，所展示的游戏/软件/文章内容均来自于互联网或第三方用户上传分享，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系youleyoucom@outlook.com。

上一篇：SQL如何在查询中实现条件求和_利用SUM配合CASE WHEN实现下一篇：Oracle RAC如何监控高负载节点？利用AWR报告定位性能瓶颈