Mysql 开窗函数 sum()over( partition by) 的用法

–用法详解

表内容

select * from wmg_test;     ---测试数据

2020/08/image-e3312bde.png

1

select v1,v2,sum(v2) over(order by v2) as sum     --按照 v2排序,累计n+n-1+....+1
from wmg_test;

2020/08/image-c9cc2c5c.png

2

select v1,v2,sum(v2) over(partition by v1 order by v2) as sum     --先分组,组内在进行 1 中的操作
from wmg_test;

2020/08/image-bbdb3fdb.png

3

select v1,v2,sum(v2) over(partition by v1 order by v1) as sum   ---稳定排序
from wmg_test;

2020/08/image-b3a55743.png

4

select v1,v2,sum(v2) over(partition by v1) as sum    --相同key的进行回填处理
from wmg_test;

2020/08/image-edd5da6b.png

5

select distinct v1,sum_01                                        --取一条
from (
select v1,sum(v2) over(partition by v1) as sum_01 
from wmg_test
) a;

2020/08/image-c4ac752c.png

6

当然也可以逆序累加,只需 order by desc 即可

总结区别:group by 和 partition by 的区别

group 单纯分组

partition 也能分组,但还具备累计的功能