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

最新推荐文章于 2025-02-15 20:18:41 发布

xiaomifeng713

最新推荐文章于 2025-02-15 20:18:41 发布

阅读量5.8k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： DB_SQL 文章标签： SQL

本文链接：https://blog.youkuaiyun.com/xiaomifeng287/article/details/10997309

DB_SQL 专栏收录该内容

13 篇文章

订阅专栏

本文通过具体实例演示了Oracle数据库中分析函数SUM() OVER()的不同用法，包括按顺序累积求和、按部门分组累积求和等场景，并对比了不同ORDER BY及PARTITION BY参数设置下的结果差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

环境: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                            800
JAMES         950.00                           1750
ADAMS        1100.00                           2850
WARD         1250.00                           4100
MARTIN       1250.00                           5350
MILLER       1300.00                           6650
TURNER       1500.00                           8150
ALLEN        1600.00                           9750
CLARK        2450.00                          12200
BLAKE        2850.00                          15050
JONES        2975.00                          18025
SCOTT        3000.00                          21025
FORD         3000.00                          24025
KING         5000.00                          29025

14 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                            800
ALLEN        1600.00                           2400
WARD         1250.00                           3650
JONES        2975.00                           6625
MARTIN       1250.00                           7875
BLAKE        2850.00                          10725
CLARK        2450.00                          13175
SCOTT        3000.00                          16175
KING         5000.00                          21175
TURNER       1500.00                          22675
ADAMS        1100.00                          23775
JAMES         950.00                          24725
FORD         3000.00                          27725
MILLER       1300.00                          29025

14 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        800
JAMES         950.00       1750       1750
ADAMS        1100.00       2850       2850
WARD         1250.00       4100       5350
MARTIN       1250.00       5350       5350
MILLER       1300.00       6650       6650
TURNER       1500.00       8150       8150
ALLEN        1600.00       9750       9750
CLARK        2450.00      12200      12200
BLAKE        2850.00      15050      15050
JONES        2975.00      18025      18025
SCOTT        3000.00      21025      24025
FORD         3000.00      24025      24025
KING         5000.00      29025      29025

14 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                          29025

14 rows selected

SQL> 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                           9400

14 rows selected