- 测试数据
select * from wmg_test;
- 按照 v2 排序,累计 n+n-1+....+1
select v1,v2,sum(v2) over(order by v2) as sum
from wmg_test;
- 先分组,组内在进行上一个案例中的操作
select v1,v2,sum(v2) over(partition by v1 order by v2) as sum
from wmg_test;
- 稳定排序
select v1,v2,sum(v2) over(partition by v1 order by v1) as sum
from wmg_test;
- 相同 key 的进行回填处理
select v1,v2,sum(v2) over(partition by v1) as sum
from wmg_test;
- 取一条
select distinct v1,sum_01
from (
select v1,sum(v2) over(partition by v1) as sum_01
from wmg_test
) a;
- 当然也可以逆序累加,只需 order by desc 即可
总结(group by & partition by)
- group 单纯分组
- partition 也能分组,但还具备累计的功能