如何运用PERCENTILE_CONT函数在SQL中计算分组中位数？

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

在支持窗口函数的数据库（如PostgreSQL、SQL Server、Oracle、BigQuery）中，`PERCENTILE_CONT(0.5)` 是一种计算连续分布中位数的位置的标准方式。它不依赖于排序后取中间值的整数索引，而是自动处理偶数个值时的情况，例如通过取中间两个值的平均来得到结果，从而使结果符合统计学的定义。

注意：MySQL 8.0+ 和 SQLite 不支持该函数；SQLite 完全无原生中位数函数；MySQL 需用变量或 ROW_NUMBER() + COUNT() 手动模拟。

必须配合 PARTITION BY 和 ORDER BY 使用

PERCENTILE_CONT 是窗口函数，不能单独出现在 SELECT 列表里而不声明窗口范围。漏掉 PARTITION BY 会导致全表计算一个中位数；漏掉 ORDER BY 会报错（SQL 标准强制要求）。

常见写法：

SELECT dept, salary, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept) AS median_salary FROM employees;

关键点：

• WITHIN GROUP (ORDER BY ...) 决定中位数的排序依据，必须是标量列（不能是表达式或 NULL-heavy 字段）
• OVER (PARTITION BY ...) 控制分组粒度，可多列，如 PARTITION BY dept, year
• 该函数返回值类型与输入列一致（如 salary 是 DECIMAL，结果也是 DECIMAL）

NULL 值会被自动忽略，但可能影响业务语义

PERCENTILE_CONT 默认跳过 NULL 值参与排序和计数——这通常是期望行为，但容易被忽视。例如某部门有 10 行记录，其中 3 行 salary IS NULL，实际用于计算中位数的只有 7 个非空值。

如果你需要把 NULL 当作“0”或“最低值”处理，必须显式转换：

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY COALESCE(salary, 0)) OVER (PARTITION BY dept)

否则，以下情况会出问题：

• 分组内全为 NULL → 返回 NULL（不是错误）
• 字段含大量 NULL 且业务上代表“未入职”，但中位数却被当成“在职人员薪资中位数”误用
• ORDER BY 列存在 NULL 且数据库对 NULLS FIRST/LAST 处理不一致（PostgreSQL 默认 NULLS LAST，Oracle 默认 NULLS FIRST）

性能敏感场景要警惕执行计划变化

相比简单聚合（如 AVG 或 MAX），PERCENTILE_CONT 需要完整排序分组数据，无法利用普通 B-tree 索引跳过排序——即使你在 ORDER BY 列上有索引，优化器仍可能选择物化中间结果。

实操建议：

• 在大表上使用前，先加 EXPLAIN 确认是否出现 Sort 节点及对应行数
• 若仅需近似中位数（允许误差±5%），考虑用 APPROX_QUANTILES（BigQuery）或采样 + OFFSET（PostgreSQL）替代
• 避免在 WHERE 条件未过滤的前提下，对高基数分组（如 PARTITION BY user_id）直接计算，极易触发内存溢出

真正的难点不在语法，而在于确认：你用的数据库版本是否真的支持这个函数，以及 NULL 和排序规则是否与你的业务假设一致。