SQL Server如何对分组数据进行中位数聚合_利用PERCENT

PERCENT_RANK()不能计算中位数，它仅返回相对排名（0到1），不插值且不保证0.5存在；应使用PERCENTILE_CONT(0.5)配合GROUP BY或WITHIN GROUP。

sql server如何对分组数据进行中位数聚合_利用percent_rank函数

PERCENT_RANK() 不能直接算中位数，它只返回相对排名（0 到 1 之间的值），不是分位数函数。想算分组中位数，请用 PERCENTILE_CONT(0.5)，别被名字带偏。

为什么 PERCENT_RANK() 不适合求中位数

PERCENT_RANK() 计算的是「当前行在分组内的相对位置」，公式为 (rank - 1) / (total_rows - 1)，首行永远是 0，末行永远是 1。它不保证 0.5 处有数据，也不做插值——哪怕某组只有 2 行，PERCENT_RANK() 也只会返回 0 和 1，中间没有 0.5 的行可取。

常见误用现象：
– 写 WHERE PERCENT_RANK() = 0.5 结果为空
– 试图用 AVG() 对 PERCENT_RANK() 接近 0.5 的行取值，逻辑错乱、结果不可靠

PERCENT_RANK() 是排名辅助函数，不是分位数计算工具
它对重复值的处理会导致多个行共享同一 PERCENT_RANK 值（比如 [10,10,20] 中两个 10 都得 0.0），进一步破坏中位定位
即使你强行筛选 PERCENT_RANK() BETWEEN 0.49 AND 0.51，结果也依赖数据分布，不具确定性

SQL Server 正确做法：用 PERCENTILE_CONT(0.5) 配合 GROUP BY

SQL Server 2012+ 支持聚合式写法 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col)，可直接与 GROUP BY 共用，无需子查询或窗口函数绕路。

示例：按部门算薪资中位数

SELECT 
  dept,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY dept;

ORDER BY salary 必须存在，且列类型要是数值型或日期型；字符串排序会报错
NULL 值自动被忽略，不影响计算；但如果整组全是 NULL，结果返回 NULL
该写法返回每组一个标量值（float 类型），无需 DISTINCT 或额外去重
不支持在同一个 SELECT 中混用非聚合列和这个函数，除非该列也在 GROUP BY 中

当必须用窗口函数时：PERCENTILE_CONT OVER + DISTINCT

如果业务需要保留原始行并附带中位数（例如计算离差、Z-score），就得用窗口形式，但要注意去重逻辑。

错误写法：
SELECT dept, PERCENTILE_CONT(0.5) OVER (PARTITION BY dept ORDER BY salary) FROM employees → 每行都出一个值，重复太多

正确写法（取每组唯一中位数）：

SELECT DISTINCT
  dept,
  PERCENTILE_CONT(0.5) OVER (PARTITION BY dept ORDER BY salary) AS median_salary
FROM employees;

DISTINCT 是必须的，否则结果行数 = 原表行数
ORDER BY salary 必须稳定：若 salary 有重复，建议加二级排序如 ORDER BY salary, id，否则不同执行可能返回不同结果
SQL Server 默认把 NULL 排在最前，可能导致中位数左偏；如需排除 NULL，先用 WHERE salary IS NOT NULL 过滤
返回值是 float，若原字段为 INT，业务上可能需要 ROUND(..., 0) 或 CAST(... AS INT)

MySQL 或旧版 SQL Server 用户注意

如果你用的是 MySQL 8.0 或 SQL Server 2008 及更早版本，PERCENTILE_CONT 不可用，PERCENT_RANK() 更不能补位。此时唯一可靠路径是手动模拟：

用 ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY val) 和 COUNT(*) OVER (PARTITION BY group_col) 算出中间位置
奇数行取第 (cnt + 1)/2 行，偶数行取第 cnt/2 和 cnt/2 + 1 行再 AVG()
注意 NULL 要提前过滤，否则 ROW_NUMBER() 仍会编号，但中位逻辑会失效

真正容易被忽略的点是：中位数不是“某个存在的值”，而是数学定义下的连续分布中心点；PERCENTILE_CONT(0.5) 的插值行为（如 [1,3] → 2.0）恰恰是它的设计目的，而非 bug。