怎样在SQL中计算各分组的中位数与百分位数指标-SQL-PHP中文网

mysql 8.0.13+ 支持 percentile_cont 和 percentile_disc 窗口函数，需配合 over(partition by...order by...) 使用，不可与 group by 混用；postgresql 则支持 percentile_cont(0.5) within group(order by ...) 聚合语法。

怎样在sql中计算各分组的中位数与百分位数指标

MySQL 8.0+ 直接用 `PERCENTILE_CONT` 和 `PERCENTILE_DISC`

MySQL 8.0.13 起原生支持窗口函数计算百分位数，PERCENTILE_CONT 返回插值结果（连续型中位数），PERCENTILE_DISC 返回实际存在的值（离散型中位数）。两者都必须配合 OVER(PARTITION BY ... ORDER BY ...) 使用，不能用于普通聚合。

常见错误是写成 SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) FROM t GROUP BY dept —— 这是 PostgreSQL/SQL Server 写法，MySQL 不认。

PERCENTILE_CONT(0.5) 对偶数个值会做线性插值，比如 [1,3,5,7] 返回 4.0；PERCENTILE_DISC(0.5) 则返回排序后第 2 个值（即 3）
必须指定 ORDER BY，否则报错 Window function 'percentile_cont' requires an ORDER BY clause
不能和 GROUP BY 混用：想按部门算中位数，得用窗口函数 + 去重，例如先算出每部门的中位数，再 SELECT DISTINCT dept, median_score

PostgreSQL 中用 `PERCENTILE_CONT` 配合 `GROUP BY`

PostgreSQL 支持 WITHIN GROUP 语法，可直接在聚合中使用，不需要窗口函数。这是最接近直觉的写法。

典型场景：统计各部门薪资中位数、90 分位绩效得分。

写法是 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary，放在 SELECT 中与 GROUP BY dept 共存
注意 ORDER BY 是 WITHIN GROUP 的一部分，不是外层查询的 ORDER BY；写错位置会报错 syntax error at or near "ORDER"
如果字段含 NULL，默认被忽略；若需保留，得先用 COALESCE 或过滤
性能上，大数据量时该函数会触发排序，建议在 ORDER BY 字段建索引

兼容旧版 MySQL / SQLite / SQL Server 2012–2014 的手动实现

没有原生百分位函数时，得靠行号模拟：对每组数据排序后取中间一到两个位置的值。核心是正确处理奇偶数量、重复值、空值。

以 MySQL 5.7 为例，用变量模拟 ROW_NUMBER()：

SELECT dept, AVG(score) AS median_score
FROM (
  SELECT dept, score,
         @row := IF(@prev = dept, @row + 1, 1) AS row_num,
         @cnt := IF(@prev = dept, @cnt, cnt_by_dept) AS cnt,
         @prev := dept
  FROM (
    SELECT dept, score, COUNT(*) OVER (PARTITION BY dept) AS cnt_by_dept
    FROM scores
    WHERE score IS NOT NULL
    ORDER BY dept, score
  ) t
  JOIN (SELECT @row := 0, @cnt := 0, @prev := '') r
) ranked
WHERE row_num IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2))
GROUP BY dept;

必须先过滤 NULL，否则 ROW_NUMBER 逻辑混乱
FLOOR((cnt+1)/2) 和 CEIL((cnt+1)/2) 覆盖奇偶两种情况：cnt=4 → 取第 2 和第 3 行；cnt=5 → 两者都为 3，取同一行
SQLite 没有变量，得用 (SELECT COUNT(*) FROM t2 WHERE t2.dept=t1.dept AND t2.score 做相关子查询，性能差很多
SQL Server 2012+ 其实已有 PERCENT_RANK 和 NTILE，但不如 PERCENTILE_CONT 直观，容易误用成分桶而非精确分位点