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

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;

这里有几点值得注意：

• Oracle的MEDIAN()会自动忽略空值，所以不需要额外写WHERE sal IS NOT NULL来过滤。
• 它计算的是精确中位数。如果一组数据有偶数个，它会取中间两个数的平均值，而不是近似值。
• 由于MEDIAN() OVER是分析函数，你不能在GROUP BY子句里同时列出其他非聚合列。通常的解决办法是使用DISTINCT，或者用子查询把结果“收口”。

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

转到SQL Server这边，情况就不同了。它没有原生的MEDIAN()函数，但从2012版本开始，引入了PERCENTILE_CONT()。这个函数是用于计算连续分布的百分位数，把参数设为0.5，就能模拟出中位数的效果。不过，它有个硬性规定：必须搭配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 ...)，否则会直接报语法错误。
• 当sal字段包含NULL时，PERCENTILE_CONT默认会跳过它们，这点和Oracle的行为是一致的。
• 和Oracle类似，你必须使用DISTINCT或者外层的GROUP BY来去重，否则查询结果中的每一行都会重复显示该组的中位数值。
• 性能上也要留意：如果表的数据量非常大，这个窗口函数的计算开销可能比后面要提到的手写方案更大，尤其是在没有合适索引的情况下。

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

无论是Oracle还是SQL Server的方案，都需要考虑一些边界情况。比如，当某个分组里只有一行数据时，两种方法都能正常返回该行的值。但是，如果某个分组里所有的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,
       A VG(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)，或者直接用（这些函数SQL Server也支持）。这种方法的代价是多了一层子查询嵌套，但它的优势也非常明显：

• 逻辑完全透明，每一步都看得见，调试起来非常方便。
• 扩展性极强。例如，你可以轻松修改逻辑，先排除前5%的极端值，再计算剩余数据的中位数。
• 兼容性更广。它不依赖特定版本的高级函数，只要数据库支持ROW_NUMBER()（Oracle 9i+ / SQL Server 2005+）就能运行。

当然，这种方案也有需要注意的地方。当排序字段存在大量重复值时，Oracle和SQL Server为这些相同值分配ROW_NUMBER()的顺序可能不一致。如果业务对结果的强一致性有严格要求，就需要在ORDER BY子句中增加一个辅助排序字段（比如主键）来确保稳定性。