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

语法: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

1 1000       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