如何用PERCENTILE_CONT函数在SQL中求每组数据的中位数？

本文共计1031个文字，预计阅读时间需要5分钟。

《PERCENTILE_CONT》是标准SQL中计算中位数最直接的方法，但在不同数据库中的支持和语法细节差异很大——例如，PostgreSQL、Oracle和SQL Server（2012版）支持该功能，而BigQuery也支持。相比之下，MySQL和SQLite原生不支持此功能。

PERCENTILE_CONT 的基本用法和参数含义

该函数本质是「连续分布下的分位数插值」，中位数对应 0.5。它必须配合 OVER 子句使用，不能直接用于普通 GROUP BY；若要按组计算中位数，需用窗口函数 + 去重逻辑，或结合子查询/CTE。

• PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column_name) 是聚合形式（无 OVER），仅在 PostgreSQL、Oracle、SQL Server 中可用，此时可直接搭配 GROUP BY
• PERCENTILE_CONT(0.5) OVER (PARTITION BY group_col ORDER BY value_col) 是窗口形式，返回每行对应的插值结果，不是每组一个值，需额外取每组首行
• 输入列必须为数值类型，NULL 会被自动忽略

PostgreSQL / Oracle 中按组算中位数的正确写法

用聚合形式最简洁，且结果确定。注意：不能把 PERCENTILE_CONT 和普通列混在同一个 SELECT 里而不加 GROUP BY，否则报错 column must appear in the GROUP BY clause。

SELECT category, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount FROM sales GROUP BY category;

• 如果 amount 有偶数个非空值（如 4 个），函数会线性插值（如第 2 和第 3 大值的平均）
• 若想强制返回实际存在的某个值（即离散中位数），应改用 PERCENTILE_DISC(0.5)
• 排序方向影响结果：默认升序，若用 ORDER BY amount DESC，逻辑不变，但中间位置映射不同——建议统一用升序避免混淆

MySQL 或 SQLite 用户的替代方案

这两个系统不支持 PERCENTILE_CONT，必须手动模拟。核心思路是：对每组数据编号，找出中间位置的 1 个或 2 个索引，再用条件聚合或连接取值。

• MySQL 8.0+ 可用 ROW_NUMBER() + COUNT() OVER 计算每组总行数，再筛选 rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2))
• SQLite 需嵌套子查询模拟窗口函数，性能较差；建议先导出到临时表加序号列
• 注意：当某组只有 1 行时，FLOOR 和 CEIL 结果相同，不会重复计数
• 如果业务允许近似值，可考虑用 AVG 配合 QUANTILE UDF（如 MySQL 的 quantile_cont 插件），但非标准、部署成本高

容易被忽略的边界情况

中位数计算看似简单，但真实数据常触发隐性问题：

• 所有值都为 NULL 的组，PERCENTILE_CONT 返回 NULL，不会报错，但可能掩盖数据质量问题
• 浮点精度影响插值结果，比如 1.1 和 1.2 的中位数理论上是 1.15，但某些数据库可能截断为 1.1499999999999999
• 字符型字段误传入 ORDER BY（如 '10', '2' 字典序排在前面），导致逻辑错误——务必确认列类型和排序语义一致
• 分区键（PARTITION BY）含 NULL 时，整组会被单独归为一类，容易被漏查

真正麻烦的从来不是函数怎么写，而是确认「你定义的中位数是否匹配业务场景里的‘中间值’预期」——比如是否接受插值、是否忽略异常值、NULL 算不算参与排序。这些得跟下游使用者对齐，而不是只看 SQL 跑出数字。