分析函数sum() over (order by ) 使用记录

来源：互联网发布：nodejs 返回json对象编辑：程序博客网时间：2024/06/09 19:52

环境:oracle 10.2.0.1.0

用户:scott / tiger

分析函数sum() over (order by ) 使用记录:

SQL> select ename,sal,sum(sal) over(order by sal,empno) from emp order  by 2;ENAME            SAL SUM(SAL)OVER(ORDERBYSAL,EMPNO)---------- --------- ------------------------------SMITH         800.00                            800JAMES         950.00                           1750ADAMS        1100.00                           2850WARD         1250.00                           4100MARTIN       1250.00                           5350MILLER       1300.00                           6650TURNER       1500.00                           8150ALLEN        1600.00                           9750CLARK        2450.00                          12200BLAKE        2850.00                          15050JONES        2975.00                          18025SCOTT        3000.00                          21025FORD         3000.00                          24025KING         5000.00                          2902514 rows selected

第二种方法:

SQL> select ename,sal,(select sum(d.sal) from emp d where d.empno<=e.empno) from emp e order by 3;ENAME            SAL (SELECTSUM(D.SAL)FROMEMPDWHERE---------- --------- ------------------------------SMITH         800.00                            800ALLEN        1600.00                           2400WARD         1250.00                           3650JONES        2975.00                           6625MARTIN       1250.00                           7875BLAKE        2850.00                          10725CLARK        2450.00                          13175SCOTT        3000.00                          16175KING         5000.00                          21175TURNER       1500.00                          22675ADAMS        1100.00                          23775JAMES         950.00                          24725FORD         3000.00                          27725MILLER       1300.00                          2902514 rows selected

其他: total2 列示意了存在重复值时可能带来的问题.

SQL> select ename,sal,sum(sal) over(order by sal,empno) as total1,sum(sal) over(order by sal)as total2 from emp order  by 2;ENAME            SAL     TOTAL1     TOTAL2---------- --------- ---------- ----------SMITH         800.00        800        800JAMES         950.00       1750       1750ADAMS        1100.00       2850       2850WARD         1250.00       4100       5350MARTIN       1250.00       5350       5350MILLER       1300.00       6650       6650TURNER       1500.00       8150       8150ALLEN        1600.00       9750       9750CLARK        2450.00      12200      12200BLAKE        2850.00      15050      15050JONES        2975.00      18025      18025SCOTT        3000.00      21025      24025FORD         3000.00      24025      24025KING         5000.00      29025      2902514 rows selected

在over()内partition by 使用与不使用的区别显示：

SQL> select deptno,ename,sal,sum(sal) over(order by sal,empno) from emp ;DEPTNO ENAME            SAL SUM(SAL)OVER(ORDERBYSAL,EMPNO)------ ---------- --------- ------------------------------    20 SMITH         800.00                            800    30 JAMES         950.00                           1750    20 ADAMS        1100.00                           2850    30 WARD         1250.00                           4100    30 MARTIN       1250.00                           5350    10 MILLER       1300.00                           6650    30 TURNER       1500.00                           8150    30 ALLEN        1600.00                           9750    10 CLARK        2450.00                          12200    30 BLAKE        2850.00                          15050    20 JONES        2975.00                          18025    20 SCOTT        3000.00                          21025    20 FORD         3000.00                          24025    10 KING         5000.00                          2902514 rows selectedSQL> select deptno,ename,sal,sum(sal) over(partition by deptno order by sal,empno) from emp ;DEPTNO ENAME            SAL SUM(SAL)OVER(PARTITIONBYDEPTNO------ ---------- --------- ------------------------------    10 MILLER       1300.00                           1300    10 CLARK        2450.00                           3750    10 KING         5000.00                           8750    20 SMITH         800.00                            800    20 ADAMS        1100.00                           1900    20 JONES        2975.00                           4875    20 SCOTT        3000.00                           7875    20 FORD         3000.00                          10875    30 JAMES         950.00                            950    30 WARD         1250.00                           2200    30 MARTIN       1250.00                           3450    30 TURNER       1500.00                           4950    30 ALLEN        1600.00                           6550    30 BLAKE        2850.00                           940014 rows selected