sql实战解析-sum()over(partition by xx order by xx)

该窗口函数功能sum( c )over( partition by a order by b) 按照一定规则汇总c的值具体规则为以a分组每组内按照b进行排序汇总第一行至当前行的c的加和值。从简单开始一步一步讲1、sum( )over( ) 对所有行进行求和2、sum( )over( order by ) 按照order by 对应字段的顺序进行累计求和即第一行到当前行默认order by 是升序排序asc也可以通过指定降序排序desc生成测试数据with aa as ( SELECT 1 a,1 b, 3 c union all SELECT 2 a,2 b, 3 c union all SELECT 3 a,3 b, 3 c union all SELECT 4 a,4 b, 3 c union all SELECT 5 a,5 b, 3 c union all SELECT 6 a,5 b, 3 c union all SELECT 7 a,2 b, 3 c union all SELECT 8 a,2 b, 8 c union all SELECT 9 a,3 b, 3 c ) SELECT a,b,c, sum(c) over(order by b) sum1,--有排序求和当前行所在顺序号的C列所有值 sum(c) over() sum2--无排序求和 C列所有值 from aa结果讲解3、sum( )over( partition by xx order by xx) 在 sum( )over( order by xx) 基础之上增加一个分组动作所有的计算都在分组内生效即在每个分区内进行sum( )over( order by xx) 的操作。with aa as ( SELECT 1 a,1 b, 3 c union all SELECT 2 a,2 b, 3 c union all SELECT 3 a,3 b, 3 c union all SELECT 4 a,4 b, 3 c union all SELECT 5 a,5 b, 3 c union all SELECT 6 a,5 b, 3 c union all SELECT 7 a,2 b, 3 c union all SELECT 8 a,2 b, 8 c union all SELECT 9 a,3 b, 3 c ) SELECT a,b,c, sum(c) over(partition by a order by b) sum3 --分组排序求和当前行所在顺序号的C列所有值 from aa结果分析