Oracle分组查询 over (parttion by xxx order by xxx)

最新推荐文章于 2023-07-25 11:23:51 发布

bamuta

最新推荐文章于 2023-07-25 11:23:51 发布

阅读量3.4k

点赞数

CC 4.0 BY-SA版权

分类专栏： PL/SQL开发 SQL Oracle

本文链接：https://blog.youkuaiyun.com/bamuta/article/details/40863607

Oracle 同时被 3 个专栏收录

173 篇文章

订阅专栏

PL/SQL开发

9 篇文章

订阅专栏

SQL

6 篇文章

订阅专栏

本文详细介绍SQL中的窗口函数用法，包括over子句的配合使用、求和(sum)、排名(rank, dense_rank, row_number)及最大最小(max, min)值等操作，并通过具体示例进行说明。

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

over不能单独使用，要和函数：rank(),dense_rank(),row_number(),sum(),min(),max()等一起使用，下面以实例说明

采用的数据来源于scott用户。

SQL> select * from emp;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
14 rows selected

一。.各种求和

select deptno,
       ename,
       sal,
       sum(sal) over() 公司总工资,
       100 * round(sal / （sum(sal) over() ）, 4) 工资百分比,
       sum(sal) over(order by ename) 公司内工资递加,
       sum(sal) over(partition by deptno order by ename) 部门内工资递加
  from emp
 order by deptno;
DEPTNO ENAME       SAL     公司总工资  工资百分比  公司内工资递加 部门内工资递加
------ ---------- --------- ---------- ---------- -------------- --------------
    10 CLARK        2450.00      29025       8.44           8000           2450
    10 KING         5000.00      29025      17.23          19925           7450
    10 MILLER       1300.00      29025       4.48          22475           8750
    20 ADAMS        1100.00      29025       3.79           1100           1100
    20 FORD         3000.00      29025      10.34          11000           4100
    20 JONES        2975.00      29025      10.25          14925           7075
    20 SCOTT        3000.00      29025      10.34          25475          10075
    20 SMITH         800.00      29025       2.76          26275          10875
    30 ALLEN        1600.00      29025       5.51           2700           1600
    30 BLAKE        2850.00      29025       9.82           5550           4450
    30 JAMES         950.00      29025       3.27          11950           5400
    30 MARTIN       1250.00      29025       4.31          21175           6650
    30 TURNER       1500.00      29025       5.17          27775           8150
    30 WARD         1250.00      29025       4.31          29025           9400
14 rows selected

二。分组排序

rank(),dense_rank(),row_number() 都是排序，但有区别

rank() 是允许并行并跳跃拍序，会跳过空序号
dense_rank() 是允许并行并连续排序
row_number() 是不允许并行

1.部门内按工资排名,允许并行并跳过空序号

select deptno,
       ename,
       sal,
       rank() over(partition by deptno order by sal desc) rank
  from emp;
DEPTNO ENAME            SAL       RANK
------ ---------- --------- ----------
    10 KING         5000.00          1
    10 CLARK        2450.00          2
    10 MILLER       1300.00          3
    20 SCOTT        3000.00          1
    20 FORD         3000.00          1
    20 JONES        2975.00          3
    20 ADAMS        1100.00          4
    20 SMITH         800.00          5
    30 BLAKE        2850.00          1
    30 ALLEN        1600.00          2
    30 TURNER       1500.00          3
    30 MARTIN       1250.00          4
    30 WARD         1250.00          4
    30 JAMES         950.00          6
14 rows selected

2.部门内按工资排名,允许并列不跳过空序号

select deptno,
       ename,
       sal,
       dense_rank() over(partition by deptno order by sal desc) rank
  from emp;
DEPTNO ENAME            SAL       RANK
------ ---------- --------- ----------
    10 KING         5000.00          1
    10 CLARK        2450.00          2
    10 MILLER       1300.00          3
    20 SCOTT        3000.00          1
    20 FORD         3000.00          1
    20 JONES        2975.00          2
    20 ADAMS        1100.00          3
    20 SMITH         800.00          4
    30 BLAKE        2850.00          1
    30 ALLEN        1600.00          2
    30 TURNER       1500.00          3
    30 MARTIN       1250.00          4
    30 WARD         1250.00          4
    30 JAMES         950.00          5
14 rows selected

3.部门内按工资排名,不允许并列

select deptno,
       ename,
       sal,
       row_number() over(partition by deptno order by sal desc) rank
  from emp;
  
DEPTNO ENAME            SAL       RANK
------ ---------- --------- ----------
    10 KING         5000.00          1
    10 CLARK        2450.00          2
    10 MILLER       1300.00          3
    20 SCOTT        3000.00          1
    20 FORD         3000.00          2
    20 JONES        2975.00          3
    20 ADAMS        1100.00          4
    20 SMITH         800.00          5
    30 BLAKE        2850.00          1
    30 ALLEN        1600.00          2
    30 TURNER       1500.00          3
    30 MARTIN       1250.00          4
    30 WARD         1250.00          5
    30 JAMES         950.00          6
14 rows selected

三。求分组最高最低 max() min()

select deptno,
       ename,
       sal,
       max(sal) over() 公司最高,
       min(sal) over(partition by deptno) 部门最低,
       max(sal) over(partition by deptno) 部门最高,
       nvl(sal - min(sal) over(partition by deptno), 0) 比最低多
  from emp;
DEPTNO ENAME       SAL       公司最高  部门最低   部门最高       比最低多
------ ---------- --------- ---------- ---------- ---------- ----------
    10 CLARK        2450.00       5000       1300       5000       1150
    10 KING         5000.00       5000       1300       5000       3700
    10 MILLER       1300.00       5000       1300       5000          0
    20 JONES        2975.00       5000        800       3000       2175
    20 FORD         3000.00       5000        800       3000       2200
    20 ADAMS        1100.00       5000        800       3000        300
    20 SMITH         800.00       5000        800       3000          0
    20 SCOTT        3000.00       5000        800       3000       2200
    30 WARD         1250.00       5000        950       2850        300
    30 TURNER       1500.00       5000        950       2850        550
    30 ALLEN        1600.00       5000        950       2850        650
    30 JAMES         950.00       5000        950       2850          0
    30 BLAKE        2850.00       5000        950       2850       1900
    30 MARTIN       1250.00       5000        950       2850        300
14 rows selected