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 比例

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