深度解读sum()over()和group by sum的本质区别

sum()over(partition by xxx order by xxx)

别的开窗函数主要取决于partition by，order by只起到排序作用，sum()over()则不太一样，它的值更大程度取决于order by的字段。

partition by的字段只能让它实现分组，再每个组单独进行累计求和；而order by后的字段顺序或逆序，

会直接影响sum()over()这个列求和的结果，如下图1、2。

既然受排序影响，那就想到了，如果order by后的字段值相同，会出现什么情况？

首先，会出现每次的行位置不固定的情况（类似dense_rank()over()）；其次，它的值也会彻底改变，它的累计求和中，会把相同值的二者直接加在一起，不会有谁先加的过程。由此也可以推测，这个开窗函数的底层逻辑，应该就是把多个重复的行，直接看成同一行，并直接加在一起！如下图，原本的输出应该是4、7、5、11、8、15、17比较合理，但受重复影响，直接显示了加在一起的结果。

所以，当你看到用了sum()over()后，累计求和的结果出现了相同值（同一个partition中），就要考虑可能是order by后的字段不唯一导致的。

我仔细思考了一下这么做的意义，可能是如果你要对生成的值进行去重，即使order by后的字段有重复，也能保证去重后的sum()over()值是一定正确的。由此可知，sum()over()的值进行去重，应该就和group by sum的值是完全一样的了。

那么group by sum 和sum()over()的区别究竟在哪呢？下面演示下二者完全等价的情况，就知道了。

--sum()over()的写法 with m(id,cnt)as ( select 2,3 from dual union all select 4,5 from dual union all select 6,7 from dual union all select 4,6 from dual union all select 6,8 from dual union all select 2,4 from dual union all select 8,9 from dual ) select distinct id,a from ( select m.*,sum(cnt) over(partition by id order by id )as a from m) --group by sum的写法 with m(id,cnt)as ( select 2,3 from dual union all select 4,5 from dual union all select 6,7 from dual union all select 4,6 from dual union all select 6,8 from dual union all select 2,4 from dual union all select 8,9 from dual ) select id,sum(cnt) from m group by id

group by sum中的group by id，完全等价于sum()over()中的partition by id order by id。

group by后只有一个字段，要求必须以此为分组，并以此为聚合字段；sum()over()把这个拆分成了两个字段。

结论：在group by后的字段与partition by和order by后的字段完全一样（a=b=c）的条件下，sum()over()去重的值才是group by 的值。