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

percentile_cont(0.5)是计算中位数的正确写法，必须传入0.5（非50或'0.5'），配合within group(order by ...)或over()使用，返回线性插值结果，且排序字段不能为null。

如何利用sql的percentile_cont函数计算中位数？

PERCENTILE_CONT(0.5) 就是中位数计算的正确写法

SQL 标准里 PERCENTILE_CONT 是连续分布下的分位数函数，中位数对应第 50 百分位，所以必须传 0.5，不是 50，也不是 50%。传错参数会直接报错或返回空值，尤其在 PostgreSQL 和 Oracle 中很严格。

常见错误现象：PERCENTILE_CONT(50) 在 PostgreSQL 报 ERROR: percentile value must be between 0 and 1；在 SQL Server（2022+）里虽不报错但结果无意义。

PERCENTILE_CONT 的第一个参数是小数形式的分位比例，范围严格为 [0.0, 1.0]
第二个参数是 WITHIN GROUP (ORDER BY ...)，排序字段不能为 NULL，否则该行被忽略（不影响整体插值逻辑，但可能改变有效样本量）
必须搭配 OVER() 或聚合上下文使用；单独写 SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) 是合法聚合，但加了 GROUP BY 后才能按组算中位数

不同数据库对 NULL 和重复值的处理差异

PERCENTILE_CONT 默认跳过 NULL 值——这点所有支持该函数的数据库（PostgreSQL、Oracle、SQL Server、Snowflake）都一致。但重复值会影响插值位置，进而影响结果精度。

例如数据 [1, 2, 2, 3] 共 4 行，中位数理论应为第 2.5 个位置：即第 2 个（2）和第 3 个（2）的平均值 → 结果仍是 2.0。但如果误把 2 当作唯一值去重再算，就错了。

PostgreSQL：完全按原始行数 + 排序后位置插值，重复值参与计数
Oracle：行为相同，但若排序字段有 NULL，NULLS FIRST/LAST 会影响位置索引（需显式声明）
Snowflake：默认 NULLS LAST，且不支持在 WITHIN GROUP 中指定 NULLS 子句，容易和本地测试结果不一致

必须用 OVER() 才能和明细行一起返回中位数

如果想在查用户订单金额的同时附上「全量订单金额中位数」，就不能只写聚合查询，得用窗口函数。否则 GROUP BY user_id 会把中位数也按用户分组，失去全局参考意义。

SELECT
  user_id,
  amount,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) OVER() AS global_median
FROM orders;

注意：OVER() 里不能加 ORDER BY 或 PARTITION BY，否则变成「累积中位数」或「分组中位数」，和需求不符。如果真要按地区算中位数，才写 OVER(PARTITION BY region)。

漏写 OVER() 会导致语法错误：PostgreSQL 报 function requires a window definition
OVER() 和 WITHIN GROUP 必须共存，顺序不能颠倒
SQL Server 要求兼容级别 ≥ 150 才支持 PERCENTILE_CONT 窗口用法

替代方案：当 PERCENTILE_CONT 不可用时怎么安全降级

MySQL 8.0.23+ 才支持 PERCENTILE_CONT，旧版或某些嵌入式 SQLite 场景只能手动实现。最稳妥的方式是用 ROW_NUMBER() + 计数判断奇偶，而不是依赖 LIMIT/OFFSET（易受排序不稳定影响）。

WITH ranked AS (
  SELECT amount, ROW_NUMBER() OVER (ORDER BY amount) AS rn,
         COUNT(*) OVER() AS cnt
  FROM orders
  WHERE amount IS NOT NULL
)
SELECT AVG(amount) AS median
FROM ranked
WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0));

这个写法覆盖奇数（取中间一个）、偶数（取中间两个平均）两种情况，且显式过滤 NULL，比用 OFFSET 更可靠。