sum() over(partition by)函数1
sum(...) over( ),对所有行求和
sum(...) over( order by ... ),和 = 第一行 到 与当前行同序号行的最后一行的所有值求和,
测试用的数据
1. sum(LABST) over()
2. sum(LABST) over(order by MATNR)
3.sum(LABST) over(order by MATNR,ZWEEK)
4.sum(LABST) over(partition by MATNR order by MATNR)
5.sum(LABST) over(partition by MATNR order by MATNR,ZWEEK)
转载于:https://www.cnblogs.com/lgx5/p/13156600.html
仅学习使用。
--用法详解
0、select * from wmg_test; ---测试数据
1、select v1,v2,sum(v2) over(order by v2) as sum --按照 v2排序,累计n+n-1+....+1
from wmg_test;
2、select v1,v2,sum(v2) over(partition by v1 order by v2) as sum --先分组,组内在进行 1 中的操作
from wmg_test;
3、select v1,v2,sum(v2) over(partition by v1 order by v1) as sum ---稳定排序
from wmg_test;
4、select v1,v2,sum(v2) over(partition by v1) as sum --相同key的进行回填处理
from wmg_test;
5、select distinct v1,sum_01 --取一条
from (
select v1,sum(v2) over(partition by v1) as sum_01
from wmg_test
) a;
6、当然也可以逆序累加,只需order by desc 即可
总结区别:group by 和partition by的区别
group 单纯分组
partition 也能分组,但还具备累计的功能