You need to enable JavaScript to run this app. SQL窗口函数中PARTITION BY搭配ORDER BY时MAX结果为何不符预期
优惠活动
大模型
产品
解决方案
定价
更多

SQL窗口函数中PARTITION BY搭配ORDER BY时MAX结果为何不符预期

为什么窗口函数中的ORDER BY会直接改变聚合结果?

核心逻辑:窗口聚合的范围由「窗口帧」决定,而非默认覆盖整个分区

很多人对窗口函数的认知停留在“用PARTITION BY划分子组,在子组内计算聚合或排名”,但忽略了一个关键规则:当OVER()子句中加入ORDER BY后,SQL会自动为每个分区内的行设定一个默认的计算子集(即窗口帧),聚合函数只会计算这个子集内的记录,不会默认覆盖整个分区的所有行。

按照SQL标准,这个默认窗口帧的规则是:

当OVER()子句包含ORDER BY时,所有聚合类窗口函数(MAX/MIN/SUM/COUNT/AVG等)的默认计算范围为:从当前分区的第一行开始,到当前行结束(RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)。
排名类窗口函数(ROW_NUMBER/RANK/DENSE_RANK等)不依赖窗口帧,直接基于分区内的ORDER BY顺序计算排名。

逐场景对应你的观察结果

1. 升序排序场景(ORDER BY ts)

分区内的行按ts从小到大排列:第1行是id=1(ts=2021-03-25),第2行是id=2(ts=2021-03-30)。
按默认窗口帧逐行计算MAX(ts):

  • 计算id=1对应的max_ts:当前行的窗口帧只包含自己,最大值就是2021-03-25
  • 计算id=2对应的max_ts:当前行的窗口帧覆盖从分区开头到自己的全部2行记录,最大值就是2021-03-30

这就是你看到max_ts逐行变化的根本原因——不是MAX函数的逻辑变了,是计算范围从来就不是整个分区,而是逐行扩大的累计范围。

2. 降序排序场景(ORDER BY ts DESC)

分区内的行按ts从大到小排列:第1行是id=2(ts=2021-03-30),第2行是id=1(ts=2021-03-25)。
同样按默认窗口帧逐行计算MAX(ts):

  • 计算id=2对应的max_ts:窗口帧只包含自己,最大值是2021-03-30
  • 计算id=1对应的max_ts:窗口帧覆盖从分区开头到自己的2行记录,因为分区开头就是整个分区的最大值,所以这部分的最大值依然是2021-03-30

你觉得这个结果“符合预期”本质是场景巧合:因为你用来聚合的字段就是排序用的ts,降序时最大值天然排在分区最开头,逐行扩大的计算范围永远会包含这个最大值,所以最终所有行的max_ts都等于整个分区的最大值。这不是SQL规则在升降序时有区别,只是刚好这个场景下的累计结果和全分区聚合结果一致。

可以换个聚合字段戳破这个巧合:还是原来的两张表,把MAX(ts)换成SUM(ts),同样用降序排序:

SELECT *, SUM(ts) OVER (PARTITION BY name ORDER BY ts DESC) sum_ts FROM dd;

得到的结果就会变成:

  • id=2的行sum_ts=2021-03-30
  • id=1的行sum_ts=两个ts的数值总和

这时候你就能清晰看到,降序排序下的聚合依然是逐行累计的范围计算,根本不是算整个分区的总和。

如何拿到真正的全分区聚合值?

如果需求是“不管分区内行的排序规则是什么,都要拿到整个分区的聚合结果”,需要在OVER子句中显式指定窗口帧覆盖整个分区,覆盖默认的累计范围:

SELECT *, 
  MAX(ts) OVER (
    PARTITION BY name 
    ORDER BY ts  -- 此处的ORDER BY仅影响行的展示顺序,不改变聚合范围
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) max_ts 
FROM dd;

这种写法下,不管ORDER BY是升序、降序,甚至省略ORDER BY,两行的max_ts都会是整个分区的最大值2021-03-30,完全符合最初的预期。


内容的提问来源于stack exchange,提问作者zuffbq

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.29 03:06:02