SQL如何计算分组数据的分位数_使用PERCENTILE_CONT函数

SQL如何计算分组数据的分位数：使用PERCENTILE_CONT函数

PERCENTILE_CONT 为什么必须配合 OVER() 使用

很多朋友第一次用 PERCENTILE_CONT 时，很容易掉进一个语法坑：直接把它当成普通的聚合函数来写。比如，想当然地写成 SELECT PERCENTILE_CONT(0.5) FROM t GROUP BY x，结果立刻就会收到报错：ERROR: window function calls require an OVER clause。

问题出在哪？关键在于，PERCENTILE_CONT 本质上是一个窗口函数，而不是聚合函数。这意味着它必须搭配 OVER() 子句才能工作，并且在 WITHIN GROUP 里必须指定排序依据（ORDER BY），否则语法就不合法。

另一个常见的失误是，想计算“分组中位数”，却忘了在 OVER() 里使用 PARTITION BY。这样一来，算出来的其实是整个数据集的分位数，而不是每个组独立的分位数。

• 正确的语法结构是这样的：PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY group_col)
• 需要注意的是，数据库支持情况各异：PostgreSQL 从 9.4 版本开始支持此语法；而 MySQL 目前并不原生支持 PERCENTILE_CONT，通常需要用变量或 ROW_NUMBER() 来模拟实现。
• SQL Server 从 2012 版本开始也支持，但语法上有个小细节：PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY group_col) —— 注意，它的 OVER 子句里只允许有 PARTITION BY，不能再放 ORDER BY。

分组中位数的两种写法：窗口 vs 子查询

实际应用中，根据你想要的结果集形式，通常有两种思路。

如果目标很简单，只需要每个分组返回一个中位数值（例如，直接列出每个部门的工资中位数），那么使用窗口函数后去重，通常是最高效的写法。

示例（PostgreSQL）：

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

但是，如果需求更复杂一些，比如要关联出原始行的其他字段（例如，查出“工资不低于其所在部门中位数的所有员工，并显示员工姓名和中位工资”），这时候再用上面的方法就会导致重复计算。更优的策略是先用子查询或公共表表达式（CTE）计算出分组中位数，再进行关联。

WITH dept_med AS (
  SELECT dept,
         PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS med
  FROM employees
  GROUP BY dept
)
SELECT e.name, e.salary, d.med
FROM employees e
JOIN dept_med d ON e.dept = d.dept
WHERE e.salary >= d.med;

PERCENTILE_CONT(0.5) 和 PERCENTILE_DISC(0.5) 的关键区别

两者都用来计算中位数，但背后的逻辑截然不同，选错了可能直接影响业务结论。

PERCENTILE_CONT 采用的是线性插值法。简单说，如果中位数的位置落在两个数据点之间，它会计算出一个中间值。这个结果是一个浮点数，很可能在原始数据中并不真实存在。

而 PERCENTILE_DISC 则采取取离散值的策略。它总是返回排序后，实际存在于数据集中的那个值。

• 举个例子就明白了：假设数据是 [100, 200, 300, 400]。那么 PERCENTILE_CONT(0.5) 会返回 250.0（即200和300的中间值），而 PERCENTILE_DISC(0.5) 会返回 200（即第二个值）。
• 如何选择？这得看业务场景。如果业务要求“中位数必须是真实出现过的工资数额”（例如某些合规报告），那么 PERCENTILE_DISC 是唯一选择。
• 另外，对空值的处理也需要留意：PERCENTILE_CONT 默认会忽略 NULL 值，但如果整组数据全是 NULL，结果也是 NULL。稳妥起见，提前用 WHERE value IS NOT NULL 过滤掉空值是个好习惯。

性能隐患：大数据量下 ORDER BY 在 OVER 中的开销

语法搞懂了，接下来就是性能关。PERCENTILE_CONT 的内部实现高度依赖排序操作。当你写下 OVER (PARTITION BY x ORDER BY y) 时，数据库会为每一个分组单独执行一次排序。

想象一下这个场景：按城市对百万级用户计算收入的90分位数。如果城市很多，每个城市的数据量也很大，这个排序成本就会急剧上升，有时甚至会比用 ROW_NUMBER() 配合自连接的“土办法”还要慢。

有几个优化方向值得考虑：

• 索引是关键：确保 ORDER BY 所用的字段建立了索引。如果能建立复合索引 (group_col, value)，对这类分组排序查询的提升会非常显著。
• 避免重复排序：不要在同一个 SELECT 语句里，对同一字段反复调用多个分位数计算（比如同时算0.25、0.5、0.75分位），因为每一次调用都会触发独立的排序。可以考虑使用数据库提供的高级功能，例如 PostgreSQL 14+ 支持 PERCENTILE_CONT(ARRAY[0.25,0.5,0.75]) WITHIN GROUP... 这样的数组形式，一次计算多个分位。
• 考虑近似计算：如果业务可以接受近似结果，那么像 Trino/Presto 提供的 APPROX_PERCENTILE 函数，或者采用数据采样的方式进行估算，可以完全避开全量排序的巨大开销。

说到底，很多时候性能瓶颈的根源，不是不知道语法，而是没有意识到一句简洁的 PERCENTILE_CONT 背后，隐藏着一次甚至多次全量排序操作——尤其是在复杂的嵌套 CTE 或视图里被多次调用时，执行计划很容易失控。提前意识到这一点，就能更好地驾驭它。