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

语法:sum(col1) over(partition by col2 order by col3 ) 准备数据:   DEPT_ID      ENAME                   SAL1  1000            A                    25002  1000            B                    35003  1000            C                    15004  1000            D                    20005  2000            E                    25006  2000            F                    20007  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     TOTAL1 1000       A          3500    35002 1000       B          3500    70003 1000       C          1500    85004 1000       D          2000    105005 2000       E          2500    25006 2000       F          2000    45007 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   TOTAL1 1000       C      1500  15002 1000       D      2000  35003 1000       A      3500  105004 1000       B      3500  105005 2000       F      2000  20006 2000       E      2500  45007 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   TOTAL1 1000 A 3500 105002 1000 B 3500 105003 1000 C 1500   105004 1000 D 2000 105005 2000 E 2500 80006 2000 F 2000 80007 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    TOTAL1 1000        A     3500   35002 1000          B      3500   70003 1000          C      1500   85004 1000          D      2000   105005 2000          E      2500   130006 2000          F      2000   150007 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 TOTAL1 1000 A 3500 105002 1000 B 3500 105003 1000 C 1500 105004 1000 D 2000 105005 2000 E 2500 185006 2000 F 2000 185007 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 TOTAL1 1000 A 3500 185002 1000 B 3500 185003 1000 C 1500 185004 1000 D 2000 185005 2000 E 2500 185006 2000 F 2000 185007 2000 G 3500 18500