oracle分析函数系列之sum(col1) over(partition by col2 order by col3):实现分组汇总或递增汇总

rfb0204421 分享于 2012-06-18

2019阿里云全部产品优惠券(新购或升级都可以使用，强烈推荐)
领取地址：https://promotion.aliyun.com/ntms/yunparter/invite.html

语法:sum(col1) over(partition by col2 order by col3 )

准备数据:

   DEPT_ID   ENAME          SAL
1 1000            A                     2500
2 1000            B                    3500
3 1000           C                   1500
4 1000           D                    2000
5 2000            E                    2500
6 2000           F                    2000
7 2000           G                    3500

主要有四种情况:

sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over (order by deptno，ename) 不按部门“连续”求总和
sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。

1.有partition by有order by : 在partition by分组下,按照不同的order by col3实现递增汇总..

SQL>>select DEPT_ID,ENAME,SAL,sum(SAL) over(partition by dept_id order by ENAME) AS TOTAL from dept_sal

结果:按照部门分组,按名字排序实现递增汇总.

DEPT_ID ENAME SAL TOTAL

1 1000       A           3500   3500
2 1000       B           3500  7000
3 1000       C          1500   8500
4 1000       D          2000 10500
5 2000       E          2500   2500
6 2000       F          2000   4500
7 2000       G         3500    8000

如果col3重复会只加总一次(当然在本例中这种写法毫无意义):

SQL>> select DEPT_ID,ENAME,SAL,sum(SAL) over(partition by dept_id order by SAL) AS TOTAL from dept_sal

DEPT_ID ENAME SAL TOTAL

11000 C 1500 1500
2 1000 D 2000 3500
3 1000 A 3500 10500
4 1000 B 3500 10500
5 2000 F 2000 2000
6 2000 E 2500 4500
7 2000 G 3500 8000

2.有partition by无order by: 实现分组内所有数据的汇总

SQL>>select DEPT_ID,ENAME,SAL,sum(SAL) over(partition by dept_id) AS TOTAL from dept_sal

DEPT_ID ENAME SAL TOTAL
1 1000 A 3500 10500
2 1000 B 3500 10500
3 1000 C 1500 10500
4 1000 D 2000 10500
5 2000 E 2500 8000
6 2000 F 2000 8000
7 2000 G 3500 8000

3.无partition by有order by : 直接按order by 字段实现递增汇总

SQL>>select DEPT_ID,ENAME,SAL,sum(SAL) over(order by ENAME) AS TOTAL from dept_sal

DEPT_ID ENAME SAL TOTAL
1 1000 A 3500 3500
2 1000 B 3500 7000
3 1000 C 1500 8500
4 1000 D 2000 10500
5 2000 E 2500 13000
6 2000 F 2000 15000
7 2000 G 3500 18500

如果order by 的值相同,会进行汇总,但汇总后显示的值会是一样的,如下:

SQL>>select DEPT_ID,ENAME,SAL,sum(SAL) over(order by DEPT_ID) AS TOTAL from dept_sal

DEPT_ID ENAME SAL TOTAL
1 1000 A 3500 10500
2 1000 B 3500 10500
3 1000 C 1500 10500
4 1000 D 2000 10500
5 2000 E 2500 18500
6 2000 F 2000 18500
7 2000 G 3500 18500

4.无partition by无order by: 所有数据相加.

SQL>>select DEPT_ID,ENAME,SAL,sum(SAL) over() AS TOTAL from dept_sal

DEPT_ID ENAME SAL TOTAL
1 1000 A 3500 18500
2 1000 B 3500 18500
3 1000 C 1500 18500
4 1000 D 2000 18500
5 2000 E 2500 18500
6 2000 F 2000 18500
7 2000 G 3500 18500

-----------------------------------------------------------------------------------------------------------------------

分析sum(x) over(partition by y ORDER BY z) [分析函数]

Oracle 作者：SmartWilson 时间：2015-05-14 10:34:21 1672 0

本文来源于http://www.cnblogs.com/luhe/p/4155612.html

sum(x) over( partition by y ORDER BY z ) 分析

之前用过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,-