本文转自:sum(x) over( partition by y ORDER BY z ) 分析
参考的博文出处:http://www.cnblogs.com/luhe/p/4155612.html,对博文进行了修改新增,修改了错误的地方
之前用过row_number(),rank()等排序与over( partition by … ORDER BY …),这两个比较好理解: 先分组,然后在组内排名。
今天突然碰到sum(…) over( partition by … ORDER BY … ),居然搞不清除怎么执行的,所以查了些资料,做了下实操。
1. 从最简单的开始
sum(…) over( ),对所有行求和
sum(…) over( order by … ),和 = 第一行 到 与当前行同序号行的最后一行的所有值求和,文字不太好理解,请看下图的算法解析。
with aa as
(
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 8 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,b,c,
sum(c) over(order by b) sum1,--有排序,求和当前行所在顺序号的C列所有值
sum(c) over() sum2--无排序,求和 C列所有值
from aa
补充 by 松门一枝花:
WITH aa AS
( SELECT 1 a,1 b, 3 c FROM dual
UNION
SELECT 2 a,2 b, 3 c FROM dual
UNION
SELECT 3 a,3 b, 3 c FROM dual
UNION
SELECT 4 a,4 b, 3 c FROM dual
UNION
SELECT 5 a,5 b, 3 c FROM dual
UNION
SELECT 6 a,5 b, 3 c FROM dual
UNION
SELECT 7 a,2 b, 3 c FROM dual
UNION
SELECT 8 a,2 b, 8 c FROM dual
UNION
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,
b,
c,
SUM(c) over(order by a) sum1,--有排序,求和当前行所在顺序号的C列所有值--【博主新增的】
SUM(c) over(order by b) sum2,--有排序,求和当前行所在顺序号的C列所有值
SUM(c) over() sum3 FROM aa order by a; --无排序,求和 C列所有值
2. 与 partition by 结合
sum(…) over( partition by… ),同组内所行求和
sum(…) over( partition by… order by … ),同第1点中的排序求和原理,只是范围限制在组内
with aa as
(
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 7 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,b,c,sum(c) over( partition by b ) partition_sum,
sum(c) over( partition by b order by a desc) partition_order_sum
FROM aa;
注:上面的语法是 oracle 中的。在 sqlserver 中也有类似的用法,把相关的 from dual 去掉就可以了。