SQL 如何用 PERCENTILE_CONT / PERCENTILE_DISC 计算精确中位数

PERCENTILE_CONT 和 PERCENTILE_DISC 的核心区别在哪

二者都用于计算分位数，但中位数（50% 分位）结果可能不同：PERCENTILE_CONT 线性插值，返回连续分布下的理论值；PERCENTILE_DISC 直接取排序后存在的实际值（向下取整逻辑），不插值。

比如数据 [1, 3, 5, 7]： – PERCENTILE_CONT(0.5) 插值得到 4.0（3 和 5 中间） – PERCENTILE_DISC(0.5) 返回 3（第 2 个值，即 floor((n+1)/2) = 2）

• 当需要严格等于原始数据中的某个值（如报表要求“真实出现过的中位销售额”），用 PERCENTILE_DISC
• 当需数学上更平滑的中心趋势（如统计建模输入），用 PERCENTILE_CONT
• 空值默认被忽略，但若列含大量 NULL，实际参与计算的行数会减少，结果易偏移

语法写错会导致直接报错或静默错误

常见硬伤：漏掉 WITHIN GROUP、括号位置错、ORDER BY 缺失。postgresql / SQL Server / oracle 都强制要求该结构，mysql 不支持这两个函数（8.0+ 仅支持 PERCENT_RANK 等替代方案）。

正确写法长这样：

select PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sales_amount) AS median_cont,        PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY sales_amount) AS median_disc FROM orders;

• PERCENTILE_CONT 和 PERCENTILE_DISC 是聚合函数，不能出现在 WHERE 或 GROUP BY 中，只能在 SELECT 或 HAVING（配合 GROUP BY）里用
• WITHIN GROUP 括号内必须是单列 ORDER BY，不支持表达式如 ORDER BY ABS(price)（部分数据库报错）
• 参数必须是 0 到 1 的常量小数，不能是列名或子查询（如 PERCENTILE_CONT(pct_col) 会失败）

按组算中位数时容易漏掉 PARTITION BY

想算每个部门的中位薪资？别只加 GROUP BY dept——那会把整个函数当普通聚合用，报错或结果错乱。必须用窗口函数写法，靠 OVER + PARTITION BY。

例如 PostgreSQL 中正确写法：

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

• 如果误写成 GROUP BY dept, salary 再套 PERCENTILE_CONT，多数引擎会拒绝执行（因 WITHIN GROUP 要求无其他分组维度）
• Oracle 支持 PERCENTILE_CONT 同时作为聚合和窗口函数；SQL Server 只支持窗口形式（即必须带 OVER）
• 注意：窗口版无法过滤掉中位数行本身（比如“只显示薪资高于本部门中位数的员工”），得用 CTE 先算中位数再 JOIN

NULL 值和数据类型会影响结果精度

这两函数对 NULL 统一跳过，但若字段是 String 类型（如评分用 ‘A’/’B’/’C’），ORDER BY 依赖字典序，PERCENTILE_DISC 返回的可能是 ‘B’，但业务上未必是“中间等级”——本质是排序位置中位，不是语义中位。

• 数值型字段推荐显式转为 double PRECISION 或 DECIMAL，避免整数除法截断（如 PERCENTILE_CONT(0.5) 在全 int 列上仍返回 DECIMAL，但某些旧版 PostgreSQL 可能截成整数）
• 日期字段可直接用（按毫秒排序），但注意时区：若 created_at 是 timestamp WITH TIME ZONE，不同地区用户看到的“中位创建时间”可能跨天
• 大数据量下（千万级），PERCENTILE_CONT 性能通常比 PERCENTILE_DISC 略差，因插值需额外浮点运算，不过差距一般在毫秒级

真正麻烦的是跨数据库移植：Snowflake 和 BigQuery 用 appROX_QUANTILES 近似，redshift 直到 2023 年才支持 PERCENTILE_CONT，且不支持窗口形式。业务 SQL 里硬写这两个函数前，先查清目标平台版本。