SQL窗口函数中PARTITION BY搭配ORDER BY时MAX结果为何不符预期
核心逻辑:窗口聚合的范围由「窗口帧」决定,而非默认覆盖整个分区
很多人对窗口函数的认知停留在“用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

