SQL 如何统计中位数与百分位？_技术教程

PostgreSQL用percentile_cont可直接计算中位数和任意百分位，需配合WITHIN GROUP(ORDER BY...)，自动插值且忽略NULL；MySQL 8.0+需窗口函数模拟，SQL Server必须作为窗口函数使用，旧版数据库需退化方案。

PostgreSQL 用 percentile_cont 直接算中位数和任意百分位

PostgreSQL 原生支持连续分布的百分位计算，percentile_cont 是最稳妥的选择。它会插值（比如两个中间值取平均），结果符合统计学定义的中位数。

中位数写法：SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY score) FROM students;
第90百分位：SELECT percentile_cont(0.9) WITHIN GROUP (ORDER BY score) FROM students;
必须搭配 WITHIN GROUP (ORDER BY ...)，否则报错 ERROR: window function call requires an OVER clause
如果字段含 NULL，默认被忽略；想保留需提前用 WHERE score IS NOT NULL
性能上，该函数会触发排序，大数据量时注意索引 —— 对 score 建 B-tree 索引能明显加速

MySQL 8.0+ 需用窗口函数模拟，没有内置 percentile_cont

MySQL 不提供直接的百分位聚合函数，得靠 ROW_NUMBER() 和总行数推算位置，再用条件聚合取值。逻辑清晰但容易写错边界。

中位数示例（偶数行取中间两值平均）：

SELECT AVG(score) AS median
FROM (
  SELECT score,
         ROW_NUMBER() OVER (ORDER BY score) AS rn,
         COUNT(*) OVER () AS cnt
  FROM students
  WHERE score IS NOT NULL
) t
WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2));

第95百分位要换位置计算：把 FLOOR((cnt + 1) / 2) 换成 FLOOR(0.95 * cnt)，再处理四舍五入与越界（如 cnt=100 时 0.95*100=95，取第95行；但若 cnt=99，0.95*99=94.05 → FLOOR=94，需确认是否要插值）
别漏 WHERE score IS NOT NULL，否则 ROW_NUMBER() 仍会为 NULL 分配序号，干扰计数
MySQL 5.7 及更早版本不支持窗口函数，只能用变量或应用层处理，基本不可行

SQL Server 的 PERCENTILE_CONT 和兼容性陷阱

SQL Server 2012+ 支持 PERCENTILE_CONT，语法和 PostgreSQL 几乎一致，但有关键区别：它**必须作为窗口函数使用**，不能直接用于聚合上下文。

错误写法（会报错）：SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) FROM students;

正确写法（加 OVER()）：

SELECT DISTINCT PERCENTILE_CONT(0.5) 
  WITHIN GROUP (ORDER BY score) 
  OVER () AS median 
FROM students 
WHERE score IS NOT NULL;

因为是窗口函数，若没加 DISTINCT 或 GROUP BY，结果会按原表行数重复输出，看起来像“全表每行都算一遍中位数”
SQL Server 对 NULL 的处理和 PostgreSQL 一样，默认跳过，但若所有值都是 NULL，返回 NULL 而非报错

跨数据库通用方案：用子查询 + LIMIT/OFFSET（仅限近似中位数）

当无法用窗口函数或插值函数时（比如旧版 MySQL、SQLite），可退而求其次用排序后取中间位置，但只适用于中位数，且不插值、不处理偶数情况，误差可能达一个量级。

SQLite 示例（无窗口函数，无 percentile_cont）：

SELECT AVG(score) FROM (
  SELECT score FROM students WHERE score IS NOT NULL ORDER BY score
  LIMIT 2 - (SELECT COUNT(*) FROM students WHERE score IS NOT NULL) % 2
  OFFSET (SELECT (COUNT(*) - 1) / 2 FROM students WHERE score IS NOT NULL)
);

这个写法本质是：奇数行取中间1个，偶数行取中间2个再平均。但 LIMIT/OFFSET 在大数据集上效率差，且 SQLite 不支持子查询中引用外层表，所以必须重复写 WHERE score IS NOT NULL
任何用 LIMIT 1 OFFSET N 单取一行的做法，对偶数行数都会错一半 —— 中位数定义要求“中间两个值的平均”，跳过这步就不是统计学意义的中位数
百分位完全无法用此法可靠实现，不要尝试硬凑 OFFSET 比例

实际用的时候，先确认数据库版本和函数可用性，再决定走原生函数还是手动模拟；插值与否影响结果精度，业务敏感场景（比如风控阈值、报表指标）必须明确是否允许插值，不能默认“差不多就行”。