如何在SQL中通过窗口函数实现快速中位数估算-SQL-PHP中文网

不能直接用MEDIAN()函数，因其非标准SQL且MySQL、旧版SQL Server不支持；性能不可控，难以组合窗口逻辑；推荐用ROW_NUMBER()+COUNT()窗口函数手动计算，兼容性强、精度和性能可控。

如何在sql中通过窗口函数实现快速中位数估算

为什么不能直接用 `MEDIAN()` 函数？

多数主流 SQL 引擎（如 PostgreSQL、Oracle）确实支持 MEDIAN()，但它不是标准 SQL 函数，MySQL 和 SQL Server 2022 之前版本原生不提供，且即使支持，也常被实现为内部排序聚合，在大数据量下性能不可控、无法与其它窗口逻辑组合。真实生产中更常见的是用 PERCENTILE_CONT() 或手写窗口排序逼近——但后者才是你真正能掌控精度和性能的方式。

用 `ROW_NUMBER()` + `COUNT()` 窗口函数算中位数

这是兼容性最强、可控性最高的方案，适用于 MySQL 8.0+、PostgreSQL、SQL Server、BigQuery 等所有支持窗口函数的引擎。

中位数本质是「有序序列中位于 50% 位置的值」，分奇偶两种情况：
- 奇数行：取第 (n+1)/2 行
- 偶数行：取第 n/2 和 n/2 + 1 行的平均值
关键是避免两次扫描：用一个子查询或 CTE 同时算出 ROW_NUMBER() 和总行数 COUNT(*) OVER()

SELECT AVG(val) AS median_val
FROM (
  SELECT val,
         ROW_NUMBER() OVER (ORDER BY val) AS rn,
         COUNT(*) OVER() AS cnt
  FROM your_table
) t
WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0));

FLOOR((cnt + 1) / 2.0) 和 CEIL((cnt + 1) / 2.0) 自动覆盖奇偶：奇数时两者相等，偶数时正好取中间两行
注意：val 必须是数值类型；若含 NULL，需提前用 WHERE val IS NOT NULL 过滤，否则 ROW_NUMBER() 会把 NULL 排在最前（取决于 ORDER BY 的 nulls 处理策略）

`PERCENTILE_CONT(0.5)` 的坑比你想象的多

这个函数看似“开箱即用”，但实际行为差异极大：

PostgreSQL：返回精确连续插值结果（比如 [1,3] 区间中位数是 2.0），但要求 ORDER BY 列不能有 NULL，否则报错 ERROR: input has NULL values
SQL Server：仅支持在聚合上下文中使用，不支持作为窗口函数（即不能跟 OVER(PARTITION BY ...)），想按分组算中位数必须用 GROUP BY + 子查询绕开
BigQuery：支持 PERCENTILE_CONT(0.5) OVER(...)，但底层仍触发全排序，数据倾斜时易 OOM；且对重复值敏感——若大量相同 val，插值结果可能偏离直觉
如果你只要整数近似中位数（比如监控场景看数量级），用 PERCENTILE_DISC(0.5) 更快、更稳定，它直接返回已存在值，不插值

按分组快速估算中位数的性能关键点

当需要 GROUP BY category 后各组分别算中位数时，最容易掉进“为每组单独排序”的陷阱：

错误写法：ROW_NUMBER() OVER (PARTITION BY category ORDER BY val) —— 看似正确，但若 category 基数高、分布不均，小分组排序开销被放大，整体变慢
正确思路：先全局排序 + 编号，再用条件聚合聚合同一 category 内的有效序号范围

WITH ranked AS (
  SELECT category, val,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY val) AS rn,
         COUNT(*) OVER (PARTITION BY category) AS cnt
  FROM your_table
  WHERE val IS NOT NULL
)
SELECT category,
       AVG(val) AS median_val
FROM ranked
WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0))
GROUP BY category;