如何利用SQL中的PERCENTILE_CONT函数计算数据分布的中位数或分位数？-SQL-PHP中文网

percentile_cont 是基于线性插值的连续分位数函数，参数为0.5时返回中位数（可能不在原数据中）；必须配合 over() 或 within group 使用，不支持 mysql，各数据库语法细节差异显著。

如何利用sql中的percentile_cont函数计算数据分布的中位数或分位数？

PERCENTILE_CONT 是什么，它和中位数有什么关系

PERCENTILE_CONT 是一个连续分布的分位数计算函数，不是简单取排序后某一行，而是基于线性插值估算。当参数为 0.5 时，它返回的就是中位数（median），但这个中位数可能并不存在于原始数据中——比如 [1, 3] 的中位数是 2.0，PERCENTILE_CONT(0.5) 就会算出这个插值结果。

它必须配合 OVER() 窗口子句使用，不能直接在普通 SELECT 中裸用；也不支持在 GROUP BY 聚合中直接替代 MEDIAN()（某些数据库如 Oracle 有独立 MEDIAN 函数，但行为不同）。

不同数据库对 PERCENTILE_CONT 的语法支持差异

PostgreSQL、SQL Server、Oracle 和 BigQuery 都支持 PERCENTILE_CONT，但细节差别明显：

PostgreSQL 要求 ORDER BY 子句必须是确定性排序（不能用 random() 或无主键的多行相同值场景下未加 ROW_NUMBER() 辅助排序）
SQL Server 要求 PERCENTILE_CONT 必须搭配 WITHIN GROUP (ORDER BY ...)，且不接受窗口别名（即不能写 OVER w，只能写完整 OVER (ORDER BY ...)）
BigQuery 中该函数只在 ANALYTIC 模式下可用，且 PERCENTILE_CONT(0.5) 默认返回 FLOAT64，即使输入是整数
MySQL **完全不支持** PERCENTILE_CONT，得用变量模拟或升级到 8.0+ 后借助 ROW_NUMBER() + 条件判断来逼近

常见错误：为什么总是报错 “must be used with OVER()” 或 “invalid window function”

这两个错误几乎都源于没写对 OVER 子句结构。典型翻车点包括：

爱派AiPy

一款AI开发辅助工具，主要用于融合LLM与Python生态的开源AI智能体，适合需要提升相关任务效率的用户。

下载

漏掉 ORDER BY：例如 PERCENTILE_CONT(0.5) OVER () —— 报错，必须写 OVER (ORDER BY col)
在聚合查询里混用：比如 SELECT dept, PERCENTILE_CONT(0.5) OVER (ORDER BY salary) FROM emp GROUP BY dept —— 报错，窗口函数和 GROUP BY 不能这样直连，应先窗口再外层分组，或改用 PARTITION BY dept
嵌套在子查询却忘了传递排序列：子查询里用了 PERCENTILE_CONT，但外层 SELECT 没包含用于排序的字段，导致逻辑断裂
PostgreSQL 中对 NULL 值默认排在最前，若业务要求忽略 NULL，得显式写 ORDER BY col NULLS LAST

实用示例：按部门算薪资中位数，并避免浮点精度干扰

假设表 employees 有 dept 和 salary 两列，想查每个部门的薪资中位数（保留两位小数）：

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

注意几点：

这里用 WITHIN GROUP 是 SQL Server / PostgreSQL 写法；BigQuery 要换成 OVER (PARTITION BY dept ORDER BY salary)
DISTINCT 是因为窗口函数会为每行输出一个值，而我们只需要每个部门一行结果
如果 salary 是整数但结果出现 .0 尾部（如 7500.0），可用 CAST(... AS DECIMAL(10,2)) 或 TRUNC(..., 2) 控制显示
若某部门只有 1 人，PERCENTILE_CONT(0.5) 就等于那人薪资，没问题；但若全为 NULL，结果也是 NULL，需提前 WHERE salary IS NOT NULL

真正容易被忽略的是：PERCENTILE_CONT 对重复值敏感，排序相同时插值逻辑可能偏离直觉，必要时应在 ORDER BY 后追加唯一列（如 id）保证稳定性。