SQL窗口函数sum() over两种order by写法的计算逻辑差异咨询

测试数据表 #

fname|o_details|cost
eva  |coat|125
eva  |coat|225
eva  |shirt|60
eva  |slipper|20
farida|coat|100
farida|shirt|50
farida|shoes|80
farida|skirt|30
henry|shoes|80

核心前置规则 #

很多人对窗口函数的默认窗口帧存在误解：当over子句中同时指定partition by和order by时，默认的窗口计算范围是「RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW」，也就是从分区的第一行开始，到所有和当前行排序权重相同的行结束，不是直接覆盖整个分区。只有当over子句没有写order by时，窗口聚合才会基于整个分区计算。

第一个写法sum(cost) over(partition by fname order by fname desc) as part_by_fname计算逻辑 #

1. 先按fname字段分区，共分成eva、farida、henry三个独立分区
2. 分区内按fname desc排序，因为同一个分区里所有行的fname值完全相同，所以分区内所有行的排序权重一致
3. 按照默认窗口帧规则，每一行计算sum时，都会覆盖整个分区的所有行
4. 最终结果：同一个分区的所有行的part_by_fname值都等于该分区cost的总和。比如eva分区总和为125+225+60+20=430，eva的4行数据该字段值都是430。

第二个写法sum(cost) over(partition by fname order by fname,o_details desc) as part_by_both计算逻辑 #

1. 同样按fname字段分成三个独立分区
2. 分区内排序规则为fname升序+o_details降序，同一分区内fname值相同，实际排序规则等价于按o_details降序排序
3. 此时分区内不同行的o_details值可能不同，排序权重存在差异，按照默认窗口帧规则，每一行计算sum时，只会累加从分区第一行到当前排序权重行的所有cost值，也就是滚动累计求和
4. 以eva分区为例，o_details降序排序后的顺序是shirt→slipper→coat→coat：
   • shirt行：仅累加自身cost，结果为60
   • slipper行：累加前两行cost，结果为60+20=80
   • 两个coat行：排序权重相同，累加所有4行cost，结果为60+20+125+225=430

二者差异的核心原因 #

• 第一个写法的order by字段是分区键本身，同分区所有行排序权重一致，默认窗口帧覆盖整个分区，结果为分区整体求和，同分区所有行值相同
• 第二个写法的order by新增了非分区键字段o_details，同分区内不同行排序权重存在差异，默认窗口帧为滚动累计范围，结果为逐行累加的滚动和，同分区不同行值可能不同

内容的提问来源于stack exchange，提问作者Lokesh Varshney