SQL如何高效计算分组内的中位数_利用PERCENTILE_CONT函数

SQL分组中位数计算：避开PERCENTILE_CONT的那些“坑”

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

说到在SQL里计算分组中位数，PERCENTILE_CONT函数绝对是首选利器。但工具好用，不等于用起来就顺手。不少朋友照着语法写，结果却报错或者算出个莫名其妙的值，问题往往出在细节上。今天咱们就来聊聊，怎么把这个函数用得既稳当又高效。

PERCENTILE_CONT 在 PostgreSQL 和 SQL Server 中怎么写才不出错

开门见山，最可靠的写法就是：PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ...) OVER (PARTITION BY ...)。不过，这里头有几个关键点，一个没注意就可能踩坑。

首先，排序字段绝不能有NULL值。数据库版本也得跟上，PostgreSQL得是9.4以上，SQL Server得是2012及以上。最常见的错误是什么？就是把PERCENTILE_CONT当成普通聚合函数，直接写在SELECT后面却忘了配OVER子句，结果数据库直接抛回一个“must be used with OVER clause”的错误提示。

• 处理NULL值：建议在ORDER BY子句里，先用COALESCE(col, 0)这类函数给个默认值，或者干脆在前期用WHERE col IS NOT NULL过滤掉。不同数据库对NULL的默认排序行为可能不一致，提前处理掉最省心。
• 注意数据类型：在PostgreSQL里，这个函数默认返回DOUBLE PRECISION。如果原始字段是整数类型，记得用::INT做个显式转换，不然结果带着小数位，可能会干扰后续的业务逻辑判断。
• SQL Server的精度问题：它对datetime类型支持很好，但处理datetime2时，在毫秒级数据上计算出的中位数，偶尔会有1毫秒的微小偏差，这点在极端精确的场景下需要留意。

MySQL 没有 PERCENTILE_CONT 怎么办

MySQL直到8.0.11版本才引入了窗口函数，但至今也没有原生的PERCENTILE_CONT。这时候就得自己动手模拟了。模拟中位数最怕什么？怕分组内数据行数是偶数时，那个“取中间两个数平均值”的逻辑没对齐，结果算偏了。

一个相对稳妥的思路是借助ROW_NUMBER()来定位。比起用GROUP_CONCAT再截取字符串那种“野路子”，这个方法更稳定，尤其能避开group_concat_max_len设置导致的数据截断问题。

• 第一步：标序号：先按分组和需要计算中位数的字段排序，利用ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY val_col)给每组内的数据编上号。
• 第二步：算位置：同时，用COUNT(*) OVER (PARTITION BY group_col)算出每组的总行数。根据总行数奇偶性，推导出中位数应该取的位置（比如总数是6，就取第3和第4行）。
• 第三步：取平均值：最后，通过子查询或连接，精准定位到那些序号对应的行，用A VG(val_col)算出结果。这里要特别注意WHERE条件，必须把偶数情况下的两个位置都覆盖到，一个都不能漏。

Oracle 的 PERCENTILE_CONT 和其他库行为不一致？

如果你正在做跨数据库迁移，那可得当心了。Oracle里的PERCENTILE_CONT，默认行为和其他几家有点不一样。它默认是RESPECT NULLS的，而且排序时，NULL值默认排在最前面。相比之下，PostgreSQL和SQL Server默认会把NULL排在最后。

这个差异意味着，如果数据里混着NULL，又没有事先统一处理，那么同一份数据在Oracle和PostgreSQL里算出的中位数，很可能天差地别。这不是函数有bug，纯粹是大家对“NULL该怎么排”的约定不同。

• 统一排序规则：最直接的办法，就是在写ORDER BY时显式声明NULL的位置。在PostgreSQL或SQL Server里用NULLS LAST，在Oracle里用NULLS FIRST，确保所有环境下的逻辑一致。
• 过滤NULL值：如果业务上这些NULL值无意义，更推荐先用WHERE col IS NOT NULL子句过滤干净。注意，Oracle的PERCENTILE_CONT不支持IGNORE NULLS修饰符，这个修饰符只在它的“兄弟”函数PERCENTILE_DISC上有效。
• 慎用MEDIAN()：Oracle 12c之后提供了MEDIAN()这个聚合函数，但它有个局限：不支持窗口语法。这意味着你没法用它来方便地计算“分组内”的中位数，只能做全局计算，选用前得想清楚场景。

为什么用 PERCENTILE_CONT 而不是 PERCENTILE_DISC

这俩函数名字像，但脾气不同。PERCENTILE_CONT（CONT是continuous的缩写）会进行线性插值，返回一个理论上连续的结果。而PERCENTILE_DISC（DISC是discrete的缩写）则比较“实在”，只返回数据集中实际存在的某个值。

大多数统计场景，比如计算用户支付金额的中位数，我们想要的是那个“理论上的中间值”。这时候PERCENTILE_CONT就更合适，它给出的插值结果更符合数学期望。而PERCENTILE_DISC可能会硬生生返回一个数据里根本没有的金额，反而显得奇怪。

不过，PERCENTILE_CONT也不是没有“软肋”。当分组数据量非常小的时候，它的插值结果可能有点反直觉。比如数据是[100, 300]，它算出200，这很合理。但如果数据是[100, 100]，它算出来还是100，这也没问题。真正的风险藏在像[NULL, 100]这样的数据里——如果没控制好NULL的排序位置，结果就完全无法预测了。

• 数学意义明确：只要分组里至少有2个非空的数值，PERCENTILE_CONT(0.5)的数学定义就是清晰的。
• DISC的“安全感”陷阱：PERCENTILE_DISC(0.5)在分组只有一行数据时，会直接返回那个值，看起来好像更安全。但这其实掩盖了“样本量不足”这个根本问题，可能误导判断。
• 性能考量：两者在性能上几乎没有显著差别。PERCENTILE_CONT因为多了点浮点运算，开销理论上大一丁点，但在TB级的数据量下，这点差异基本可以忽略不计。