SQL怎么计算分组后的中位数_Oracle与SQL Server实现方案对比-SQL-PHP中文网

Oracle的MEDIAN()不能直接用于GROUP BY，需用OVER(PARTITION BY)配合DISTINCT；SQL Server用PERCENTILE_CONT(0.5)WITHIN GROUP(ORDER BY)OVER(PARTITION BY)；跨库推荐ROW_NUMBER()手写方案。

sql怎么计算分组后的中位数_oracle与sql server实现方案对比

Oracle 用 `MEDIAN()` 最省事，但只支持单列且不能和 `GROUP BY` 混用？

Oracle 确实内置了 MEDIAN() 聚合函数，但它有个关键限制：不能直接配合 GROUP BY 对多组分别求中位数——它会被当作分析函数处理，或报错 ORA-00937: not a single-group group function。真正能用的写法是把它放在分析函数上下文中，配合 PARTITION BY。

正确姿势是：

SELECT DISTINCT deptno,
       MEDIAN(sal) OVER (PARTITION BY deptno) AS median_sal
FROM emp;

注意点：

MEDIAN() 在 Oracle 中对空值自动忽略，无需额外 WHERE sal IS NOT NULL
结果是精确中位数（偶数个时取中间两数平均），不是近似值
不能在 GROUP BY 子句里同时出现其他非聚合列，必须用 DISTINCT 或子查询收口

SQL Server 没有 `MEDIAN()`，得靠 `PERCENTILE_CONT(0.5)` + `OVER`

SQL Server 2012+ 支持 PERCENTILE_CONT()，它是连续分布计算，能模拟中位数，但必须搭配 OVER 子句，且 ORDER BY 必须指定、不可为空。

典型写法：

SELECT DISTINCT deptno,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sal)
           OVER (PARTITION BY deptno) AS median_sal
FROM emp;

常见踩坑：

漏写 WITHIN GROUP (ORDER BY ...) → 报错 Incorrect syntax near the keyword 'OVER'
sal 含 NULL 时，PERCENTILE_CONT 默认跳过，行为和 Oracle 一致
必须用 DISTINCT 或外层 GROUP BY 去重，否则每行都返回相同中位数值
如果表很大，这个窗口函数可能比手写 ROW_NUMBER() 方案更慢，尤其没建好索引时

两个数据库都得小心：分组内只有 1 行或全为 `NULL` 的情况

两种方案在单行分组下都能正常返回该行值；但如果某组所有 sal 都是 NULL，Oracle 的 MEDIAN() OVER 返回 NULL，SQL Server 的 PERCENTILE_CONT 也返回 NULL——这符合预期，但容易被误认为“没算出来”。

若业务要求这类组返回 0 或默认值，得显式处理：

Oracle：NVL(MEDIAN(sal) OVER (PARTITION BY deptno), 0)
SQL Server：COALESCE(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sal) OVER (PARTITION BY deptno), 0)

别指望 ISNULL() 或 NVL2() 在窗口函数外层直接套——语法不认。

真要兼容双库？别硬套函数，用 `ROW_NUMBER()` 手写更可控

当需要跨 Oracle/SQL Server 写统一逻辑，或者要加自定义规则（比如强制向下取整、排除异常值），基于 ROW_NUMBER() 的方案反而更稳。

核心思路：对每组按值排序，算出中间位置，再用条件聚合捞出对应行。例如 Oracle：

SELECT deptno,
       AVG(sal) AS median_sal
FROM (
  SELECT deptno, sal,
         ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal) rn,
         COUNT(*) OVER (PARTITION BY deptno) cnt
  FROM emp
  WHERE sal IS NOT NULL
)
WHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2))
GROUP BY deptno;

SQL Server 几乎一样，只是把 FLOOR/CEIL 换成 ROUND(..., 0) 或保持原样（它们也支持）。这种写法的代价是多一层嵌套，但好处是：