SQL中如何获取指定范围内的中位数_窗口函数辅助计算

首页

数据库

热心网友

转载

2026-04-24

SQL标准无MEDIAN()聚合函数，需用ROW_NUMBER()与COUNT() OVER()配合：先按组排序编号，再根据总行数奇偶性取中间一行或两行平均。

SQL中如何获取指定范围内的中位数_窗口函数辅助计算

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

中位数不是聚合函数，不能直接 `SELECT MEDIAN(col)`

如果你尝试在SQL里直接调用MEDIAN()，大概率会碰壁。原因很简单：SQL标准里压根就没有定义这个聚合函数。虽然像PostgreSQL 9.4+和Oracle 12c+这些数据库自己实现了，但行为并不统一，跨数据库使用时兼容性是个大问题。所以，想在特定范围（比如按商品类别分组，或者筛选出某个时间段的数据）内计算中位数，我们得另辟蹊径——依靠窗口函数来手动定位中间行。整个过程的核心，其实就是先给数据排好队、编上号，然后精准地找到“队伍正中间”的那一位（或两位）。

用 `ROW_NUMBER()` 和 `COUNT() OVER()` 定位中间行

这里的关键点，其实不在于“把两个中间值求平均”，而在于先判断总行数是奇数还是偶数，再动态地选择目标行。一个常见的误区是试图用PERCENT_RANK()或者NTILE(2)来取巧，这两个函数对重复值非常敏感，边界情况下的处理也不够稳定，容易出岔子。

更可靠的方案分三步走：

首先，在每个分组内（比如PARTITION BY category），使用ROW_NUMBER() OVER (ORDER BY value)为每一行生成一个连续的序号（记为rn）。
同时，利用COUNT(*) OVER (PARTITION BY category)得到该分组的总行数（记为cnt）。
最后，中位数所在的行，需要满足这个条件：它的序号rn恰好等于FLOOR((cnt + 1) / 2.0)（当总行数为奇数时），或者rn是cnt/2和cnt/2 + 1这两个位置（当总行数为偶数时）。不过，有一个更稳妥、能兼容奇偶数的统一写法：rn IN (FLOOR((cnt + 1)/2.0), CEIL((cnt + 1)/2.0))。

来看一个具体的例子（适用于MySQL 8.0+或PostgreSQL）：

SELECT category, A VG(value) AS median
FROM (
  SELECT category, value,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY value) AS rn,
         COUNT(*) OVER (PARTITION BY category) AS cnt
  FROM sales
  WHERE sale_date BETWEEN '2024-01-01' AND '2024-06-30'
) t
WHERE rn IN (FLOOR((cnt + 1)/2.0), CEIL((cnt + 1)/2.0))
GROUP BY category;

WHERE 范围前置过滤比窗口内过滤更高效

如果只想计算某个特定时间范围或状态下的中位数，过滤条件放哪里就很有讲究了。务必记住：要把WHERE子句放在子查询的最外层，或者至少在窗口函数计算之前进行过滤。试图在窗口函数的OVER()子句里直接写WHERE是行不通的，语法上就不支持。

✅ 正确做法：先通过WHERE sale_date >= '2024-01-01'过滤数据，然后再进行窗口计算。
❌ 错误做法：幻想在OVER (PARTITION BY x)里面加入WHERE条件，这会导致语法错误。
⚠️ 注意特例：PostgreSQL特有的FILTER (WHERE ...)语法，仅适用于标准的聚合函数（如SUM, COUNT），不能用于ROW_NUMBER()这类排序窗口函数。

NULL 值和重复值会直接影响结果位置

数据本身的特性也会暗中影响中位数的计算结果。按照SQL标准，ORDER BY valueNULL值排在最前面，但MySQL和SQL Server可能反其道而行之，这就导致ROW_NUMBER()的编号起点可能偏移。另外，重复值不会在ROW_NUMBER()这里获得并列排名（比如两个相同的100，会一个排第3，一个排第4），这反而成了一个优点——可以防止中位数被大量重复值“挤”到偏离的位置。

处理这些细节，需要一些技巧：

为了明确NULL值的位置，可以在PostgreSQL或Oracle中使用ORDER BY value NULLS LAST；在MySQL中，则可以用ORDER BY value IS NULL, value。
如果业务逻辑要求完全忽略NULL值，那么必须在计算前就用WHERE value IS NOT NULL将其排除。否则，COUNT(*) OVER会把NULL算进总数，但排序时NULL又占了一个位置，最终会导致中位数定位错误。

说到底，最复杂的往往不是技术实现，而是业务语义的界定：计算中位数时，到底要不要包含NULL？重复值是视为同一个档次，还是分开计算？这些都需要根据实际需求来明确。窗口函数就像一个忠实的执行者，它只负责严格按照你定义的排序逻辑来编号，最终的结果是否符合预期，取决于你对数据和业务的理解是否到位。

来源:https://www.php.cn/faq/2338820.html

免责声明：游乐网为非赢利性网站，所展示的游戏/软件/文章内容均来自于互联网或第三方用户上传分享，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系youleyoucom@outlook.com。

上一篇：SQL如何进行按周分组统计_利用DATE_PART或TRUNC函数下一篇：如何防止SQL注入攻击_使用预编译语句参数化查询