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 或视图里被多次调用时，执行计划很容易失控。提前意识到这一点，就能更好地驾驭它。
