Oracle分析函数与窗口函数

一、默认窗口与排名函数

最常用的分析函数是排名函数,用于返回当前记录在窗口中的位置。常用的排名函数包括rank()、dense_rank()、row_number()等。排名函数所对应的窗口函数必须指定排序规则,即必须使用order by 子句,而Oracle为每条记录提供的默认窗口为从表中第一条记录开始,直至与当前记录具有相同排名的所有记录。

先看下Oracle的示例表scott.emp
select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

1、rank()函数

将表中员工按入职时间升序排列,列出排名次序
select empno, ename, hiredate, rank() over(order by hiredate) hiredate_order from emp;

     EMPNO ENAME      HIREDATE            HIREDATE_ORDER
---------- ---------- ------------------- --------------
      7369 SMITH      1980-12-17 00:00:00              1
      7499 ALLEN      1981-02-20 00:00:00              2
      7521 WARD       1981-02-22 00:00:00              3
      7566 JONES      1981-04-02 00:00:00              4
      7698 BLAKE      1981-05-01 00:00:00              5
      7782 CLARK      1981-06-09 00:00:00              6
      7844 TURNER     1981-09-08 00:00:00              7
      7654 MARTIN     1981-09-28 00:00:00              8
      7839 KING       1981-11-17 00:00:00              9
      7900 JAMES      1981-12-03 00:00:00             10
      7902 FORD       1981-12-03 00:00:00             10
      7934 MILLER     1982-01-23 00:00:00             12
      7788 SCOTT      1987-04-19 00:00:00             13
      7876 ADAMS      1987-05-23 00:00:00             14

该函数的排名具有跳跃性,如7900和7902员工排名都是10,而下一位7934员工排名则为12。

2、dense_rank()函数

类似rank()函数,但排名不具有跳跃性
select empno, ename, hiredate, dense_rank() over(order by hiredate) hiredate_order from emp;

     EMPNO ENAME      HIREDATE            HIREDATE_ORDER
---------- ---------- ------------------- --------------
      7369 SMITH      1980-12-17 00:00:00              1
      7499 ALLEN      1981-02-20 00:00:00              2
      7521 WARD       1981-02-22 00:00:00              3
      7566 JONES      1981-04-02 00:00:00              4
      7698 BLAKE      1981-05-01 00:00:00              5
      7782 CLARK      1981-06-09 00:00:00              6
      7844 TURNER     1981-09-08 00:00:00              7
      7654 MARTIN     1981-09-28 00:00:00              8
      7839 KING       1981-11-17 00:00:00              9
      7900 JAMES      1981-12-03 00:00:00             10
      7902 FORD       1981-12-03 00:00:00             10
      7934 MILLER     1982-01-23 00:00:00             11
      7788 SCOTT      1987-04-19 00:00:00             12
      7876 ADAMS      1987-05-23 00:00:00             13

7900和7902员工排名都是10,而下一位7934员工排名则为11,排名不存在跳跃。

3、row_number()函数

row_number()函数单纯返回当前记录在窗口中所处位置的行号,该行号具有唯一性。以上查询用row_number()函数替代后如下
select empno, ename, hiredate, row_number() over(order by hiredate) hiredate_order from emp;

     EMPNO ENAME      HIREDATE            HIREDATE_ORDER
---------- ---------- ------------------- --------------
      7369 SMITH      1980-12-17 00:00:00              1
      7499 ALLEN      1981-02-20 00:00:00              2
      7521 WARD       1981-02-22 00:00:00              3
      7566 JONES      1981-04-02 00:00:00              4
      7698 BLAKE      1981-05-01 00:00:00              5
      7782 CLARK      1981-06-09 00:00:00              6
      7844 TURNER     1981-09-08 00:00:00              7
      7654 MARTIN     1981-09-28 00:00:00              8
      7839 KING       1981-11-17 00:00:00              9
      7900 JAMES      1981-12-03 00:00:00             10
      7902 FORD       1981-12-03 00:00:00             11
      7934 MILLER     1982-01-23 00:00:00             12
      7788 SCOTT      1987-04-19 00:00:00             13
      7876 ADAMS      1987-05-23 00:00:00             14

4、count()函数

通过count()函数可以查看窗口中实际的记录数目,以上查询用count()函数替代后如下
select empno, ename, hiredate, count(1) over(order by hiredate) hiredate_order from emp;

     EMPNO ENAME      HIREDATE            HIREDATE_ORDER
---------- ---------- ------------------- --------------
      7369 SMITH      1980-12-17 00:00:00              1
      7499 ALLEN      1981-02-20 00:00:00              2
      7521 WARD       1981-02-22 00:00:00              3
      7566 JONES      1981-04-02 00:00:00              4
      7698 BLAKE      1981-05-01 00:00:00              5
      7782 CLARK      1981-06-09 00:00:00              6
      7844 TURNER     1981-09-08 00:00:00              7
      7654 MARTIN     1981-09-28 00:00:00              8
      7839 KING       1981-11-17 00:00:00              9
      7900 JAMES      1981-12-03 00:00:00             11
      7902 FORD       1981-12-03 00:00:00             11
      7934 MILLER     1982-01-23 00:00:00             12
      7788 SCOTT      1987-04-19 00:00:00             13
      7876 ADAMS      1987-05-23 00:00:00             14

二、分区窗口

分区窗口是与当前记录拥有相同的分区标准的所有记录,语法为
partition by 列名
partition by语句首先根据列名获得当前记录的列值,接着获得表中具有相同列值的所有记录,并将该记录集合作为当前记录的窗口。
需要注意的是,当在分区窗口中使用order by排序后,窗口返回的不再是全部记录,而是只到当前记录,从而成为默认大小的窗口。

示例:
查询EMP表每个员工的职位和薪水,并给出该职位员工的平均薪水
select empno, ename, job, sal, round(avg(sal) over(partition by job)) avg_sal from emp;

     EMPNO ENAME      JOB              SAL    AVG_SAL
---------- ---------- --------- ---------- ----------
      7788 SCOTT      ANALYST         3000       3000
      7902 FORD       ANALYST         3000       3000
      7934 MILLER     CLERK           1300       1038
      7900 JAMES      CLERK            950       1038
      7369 SMITH      CLERK            800       1038
      7876 ADAMS      CLERK           1100       1038
      7698 BLAKE      MANAGER         2850       2758
      7566 JONES      MANAGER         2975       2758
      7782 CLARK      MANAGER         2450       2758
      7839 KING       PRESIDENT       5000       5000
      7844 TURNER     SALESMAN        1500       1400
      7654 MARTIN     SALESMAN        1250       1400
      7521 WARD       SALESMAN        1250       1400
      7499 ALLEN      SALESMAN        1600       1400

示例:
在查询每个员工职位和入职时间的同时,获得其在该职位中按入职时间进行的排序位置
select empno, ename, job, hiredate, dense_rank() over(partition by job order by hiredate) hiredata_order from emp;

     EMPNO ENAME      JOB       HIREDATE            HIREDATA_ORDER
---------- ---------- --------- ------------------- --------------
      7902 FORD       ANALYST   1981-12-03 00:00:00              1
      7788 SCOTT      ANALYST   1987-04-19 00:00:00              2
      7369 SMITH      CLERK     1980-12-17 00:00:00              1
      7900 JAMES      CLERK     1981-12-03 00:00:00              2
      7934 MILLER     CLERK     1982-01-23 00:00:00              3
      7876 ADAMS      CLERK     1987-05-23 00:00:00              4
      7566 JONES      MANAGER   1981-04-02 00:00:00              1
      7698 BLAKE      MANAGER   1981-05-01 00:00:00              2
      7782 CLARK      MANAGER   1981-06-09 00:00:00              3
      7839 KING       PRESIDENT 1981-11-17 00:00:00              1
      7499 ALLEN      SALESMAN  1981-02-20 00:00:00              1
      7521 WARD       SALESMAN  1981-02-22 00:00:00              2
      7844 TURNER     SALESMAN  1981-09-08 00:00:00              3
      7654 MARTIN     SALESMAN  1981-09-28 00:00:00              4

三、窗口子句

窗口子句可以进一步限制或扩展窗口的范围。

1、rows子句

rows子句的使用前提为窗口已经利用order by 进行了排序。此时可以利用rows子句按照位置向前或向后追溯,从而进一步调整窗口大小。语法如下
over(order by 列名 rows between 位移量 preceding and 位移量 following)

preceding向前追溯处于当前记录之前的记录,following向后追溯处于当前记录之后的记录。

示例:
在查询每个员工入职时间和薪水的同时,计算与其前后相邻入职时间的共三位员工的平均薪水
select empno, ename, job, hiredate, sal, round(avg(sal) over(order by hiredate rows between 1 preceding and 1 following)) avg_sal from emp;

     EMPNO ENAME      JOB       HIREDATE                   SAL    AVG_SAL
---------- ---------- --------- ------------------- ---------- ----------
      7369 SMITH      CLERK     1980-12-17 00:00:00        800       1200
      7499 ALLEN      SALESMAN  1981-02-20 00:00:00       1600       1217
      7521 WARD       SALESMAN  1981-02-22 00:00:00       1250       1942
      7566 JONES      MANAGER   1981-04-02 00:00:00       2975       2358
      7698 BLAKE      MANAGER   1981-05-01 00:00:00       2850       2758
      7782 CLARK      MANAGER   1981-06-09 00:00:00       2450       2267
      7844 TURNER     SALESMAN  1981-09-08 00:00:00       1500       1733
      7654 MARTIN     SALESMAN  1981-09-28 00:00:00       1250       2583
      7839 KING       PRESIDENT 1981-11-17 00:00:00       5000       2400
      7900 JAMES      CLERK     1981-12-03 00:00:00        950       2983
      7902 FORD       ANALYST   1981-12-03 00:00:00       3000       1750
      7934 MILLER     CLERK     1982-01-23 00:00:00       1300       2433
      7788 SCOTT      ANALYST   1987-04-19 00:00:00       3000       1800
      7876 ADAMS      CLERK     1987-05-23 00:00:00       1100       2050

对于第一条记录和最后一条记录来说,实际上对应的窗口只有两条记录。

2、range子句

rows子句以相对位置作为获取记录的标准,而range子句则以相对列值作为筛选记录的标准。语法如下
over(order by 列名 range between 差值 preceding and 差值 following)

示例:
在查询每个员工入职时间的同时,计算包括与其入职时间前后相差一年之内的员工数量
select empno, ename, job, hiredate, count(1) over(order by hiredate range between 365 preceding and 365 following) count_emp from emp;

     EMPNO ENAME      JOB       HIREDATE             COUNT_EMP
---------- ---------- --------- ------------------- ----------
      7369 SMITH      CLERK     1980-12-17 00:00:00         11
      7499 ALLEN      SALESMAN  1981-02-20 00:00:00         12
      7521 WARD       SALESMAN  1981-02-22 00:00:00         12
      7566 JONES      MANAGER   1981-04-02 00:00:00         12
      7698 BLAKE      MANAGER   1981-05-01 00:00:00         12
      7782 CLARK      MANAGER   1981-06-09 00:00:00         12
      7844 TURNER     SALESMAN  1981-09-08 00:00:00         12
      7654 MARTIN     SALESMAN  1981-09-28 00:00:00         12
      7839 KING       PRESIDENT 1981-11-17 00:00:00         12
      7900 JAMES      CLERK     1981-12-03 00:00:00         12
      7902 FORD       ANALYST   1981-12-03 00:00:00         12
      7934 MILLER     CLERK     1982-01-23 00:00:00         11
      7788 SCOTT      ANALYST   1987-04-19 00:00:00          2
      7876 ADAMS      CLERK     1987-05-23 00:00:00          2

3、current row与unbounded

在窗口子句中,除了可以用确定的数值来限定窗口外,还可以使用current row来指定当前记录,使用unbounded来代替数值,表示不受限制的窗口范围。

向前位移无限制
select empno, ename, job, hiredate, sal, round(avg(sal) over(order by hiredate rows between unbounded preceding and current row)) avg_sal from emp;

向后位移无限制
select empno, ename, job, hiredate, sal, round(avg(sal) over(order by hiredate rows between 1 preceding and unbounded following)) avg_sal from emp;

前后位移均无限制
select empno, ename, job, hiredate, sal, round(avg(sal) over(order by hiredate rows between unbounded preceding and unbounded following)) avg_sal from emp;

四、常用分析函数

很多聚合函数均可以和窗口函数结合使用,如max()、min()、sum()等。除此之外,Oracle还提供了另外几种常用的分析函数。

1、first_value()函数

first_value()函数返回已排序窗口中第一条记录的相关信息。

示例:
在查询员工入职时间和所在部门的同时,指出该部门入职时间最早的员工
select empno, ename, hiredate, deptno, first_value(empno) over(partition by deptno order by hiredate) first_emp from emp;

     EMPNO ENAME      HIREDATE                DEPTNO  FIRST_EMP
---------- ---------- ------------------- ---------- ----------
      7782 CLARK      1981-06-09 00:00:00         10       7782
      7839 KING       1981-11-17 00:00:00         10       7782
      7934 MILLER     1982-01-23 00:00:00         10       7782
      7369 SMITH      1980-12-17 00:00:00         20       7369
      7566 JONES      1981-04-02 00:00:00         20       7369
      7902 FORD       1981-12-03 00:00:00         20       7369
      7788 SCOTT      1987-04-19 00:00:00         20       7369
      7876 ADAMS      1987-05-23 00:00:00         20       7369
      7499 ALLEN      1981-02-20 00:00:00         30       7499
      7521 WARD       1981-02-22 00:00:00         30       7499
      7698 BLAKE      1981-05-01 00:00:00         30       7499
      7844 TURNER     1981-09-08 00:00:00         30       7499
      7654 MARTIN     1981-09-28 00:00:00         30       7499
      7900 JAMES      1981-12-03 00:00:00         30       7499

2、last_value()函数

与first_value()函数相反,last_value()函数返回分区中最后一条记录的相关信息。

示例:
在查询员工入职时间和所在部门的同时,指出该部门入职时间最迟的员工。首先说明,套用fist_value()的查询写法是不行的,如下
select empno, ename, hiredate, deptno, last_value(empno) over(partition by deptno order by hiredate) last_emp from emp;

     EMPNO ENAME      HIREDATE                DEPTNO   LAST_EMP
---------- ---------- ------------------- ---------- ----------
      7782 CLARK      1981-06-09 00:00:00         10       7782
      7839 KING       1981-11-17 00:00:00         10       7839
      7934 MILLER     1982-01-23 00:00:00         10       7934
      7369 SMITH      1980-12-17 00:00:00         20       7369
      7566 JONES      1981-04-02 00:00:00         20       7566
      7902 FORD       1981-12-03 00:00:00         20       7902
      7788 SCOTT      1987-04-19 00:00:00         20       7788
      7876 ADAMS      1987-05-23 00:00:00         20       7876
      7499 ALLEN      1981-02-20 00:00:00         30       7499
      7521 WARD       1981-02-22 00:00:00         30       7521
      7698 BLAKE      1981-05-01 00:00:00         30       7698
      7844 TURNER     1981-09-08 00:00:00         30       7844
      7654 MARTIN     1981-09-28 00:00:00         30       7654
      7900 JAMES      1981-12-03 00:00:00         30       7900

没有达到希望的查询结果,原因正如前面所说,当在分区窗口中使用order by排序后,窗口返回的不再是全部记录,而是只到当前记录,从而成为默认大小的窗口。正确的做法是可以利用rows子句来指定无限制的窗口,如下
select empno, ename, hiredate, deptno, last_value(empno) over(partition by deptno order by hiredate rows between unbounded preceding and unbounded following) last_emp from emp;

     EMPNO ENAME      HIREDATE                DEPTNO   LAST_EMP
---------- ---------- ------------------- ---------- ----------
      7782 CLARK      1981-06-09 00:00:00         10       7934
      7839 KING       1981-11-17 00:00:00         10       7934
      7934 MILLER     1982-01-23 00:00:00         10       7934
      7369 SMITH      1980-12-17 00:00:00         20       7876
      7566 JONES      1981-04-02 00:00:00         20       7876
      7902 FORD       1981-12-03 00:00:00         20       7876
      7788 SCOTT      1987-04-19 00:00:00         20       7876
      7876 ADAMS      1987-05-23 00:00:00         20       7876
      7499 ALLEN      1981-02-20 00:00:00         30       7900
      7521 WARD       1981-02-22 00:00:00         30       7900
      7698 BLAKE      1981-05-01 00:00:00         30       7900
      7844 TURNER     1981-09-08 00:00:00         30       7900
      7654 MARTIN     1981-09-28 00:00:00         30       7900
      7900 JAMES      1981-12-03 00:00:00         30       7900

由此可见,last_value()函数往往需要结合使用rows子句使用,因此,使用first_value()函数要优于使用last_value()函数。其实为了获得与last_value()相同的效果,使用first_value()函数时,只要为order by子句添加desc进行降序排列即可。

3、lag()函数

first_value()和last_value()可以返回窗口中第一条和最后一条记录,但不能处理其他记录。为此,Oracle提供了lag()函数和lead()函数来灵活处理其他记录。

lag()函数以当前记录为坐标,按照指定的位移量向上搜索,并尝试捕获记录,捕获不到时返回指定的默认值,语法如下
lag(列名或列表达式, 位移量, 默认值)

这里要求默认值的数据类型应当与列名或列表达式的类型兼容。

示例:
查询每个员工的工资信息,并给出工资排名在他前一位的员工
select empno, ename, sal, lag(empno, 1, null) over(order by sal) lag_empno, lag(ename, 1, 'N/A') over(order by sal) lag_ename from emp;

     EMPNO ENAME             SAL  LAG_EMPNO LAG_ENAME
---------- ---------- ---------- ---------- ----------
      7369 SMITH             800            N/A
      7900 JAMES             950       7369 SMITH
      7876 ADAMS            1100       7900 JAMES
      7521 WARD             1250       7876 ADAMS
      7654 MARTIN           1250       7521 WARD
      7934 MILLER           1300       7654 MARTIN
      7844 TURNER           1500       7934 MILLER
      7499 ALLEN            1600       7844 TURNER
      7782 CLARK            2450       7499 ALLEN
      7698 BLAKE            2850       7782 CLARK
      7566 JONES            2975       7698 BLAKE
      7788 SCOTT            3000       7566 JONES
      7902 FORD             3000       7788 SCOTT
      7839 KING             5000       7902 FORD

4、lead()函数

该函数与lag()函数类似,不同之处是lead()函数的位移量为向下偏移。

示例:
查询每个员工的工资信息,并给出工资排名在他后一位的员工,这里直接用lead()替换lag()即可
select empno, ename, sal, lead(empno, 1, null) over(order by sal) lag_empno, lead(ename, 1, 'N/A') over(order by sal) lag_ename from emp;

     EMPNO ENAME             SAL  LAG_EMPNO LAG_ENAME
---------- ---------- ---------- ---------- ----------
      7369 SMITH             800       7900 JAMES
      7900 JAMES             950       7876 ADAMS
      7876 ADAMS            1100       7521 WARD
      7521 WARD             1250       7654 MARTIN
      7654 MARTIN           1250       7934 MILLER
      7934 MILLER           1300       7844 TURNER
      7844 TURNER           1500       7499 ALLEN
      7499 ALLEN            1600       7782 CLARK
      7782 CLARK            2450       7698 BLAKE
      7698 BLAKE            2850       7566 JONES
      7566 JONES            2975       7788 SCOTT
      7788 SCOTT            3000       7902 FORD
      7902 FORD             3000       7839 KING
      7839 KING             5000            N/A

这里发现,当使用lead()函数做位移推进时,over(order by)这样原本只到当前记录的默认大小的窗口实际范围变大了,从而能够获取到往下的记录。

5、ratio_to_report()占比函数

查询每个员工的工资及其在所有员工和所在部门员工中的百分比
select empno, ename, sal, round((ratio_to_report(sal) over()) * 100, 2) pct1, round((ratio_to_report(sal) over(partition by deptno)) * 100, 2) pct2 from scott.emp;

     EMPNO ENAME             SAL       PCT1       PCT2
---------- ---------- ---------- ---------- ----------
      7782 CLARK            2450       8.44         28
      7839 KING             5000      17.23      57.14
      7934 MILLER           1300       4.48      14.86
      7566 JONES            2975      10.25      27.36
      7902 FORD             3000      10.34      27.59
      7876 ADAMS            1100       3.79      10.11
      7369 SMITH             800       2.76       7.36
      7788 SCOTT            3000      10.34      27.59
      7521 WARD             1250       4.31       13.3
      7844 TURNER           1500       5.17      15.96
      7499 ALLEN            1600       5.51      17.02
      7900 JAMES             950       3.27      10.11
      7698 BLAKE            2850       9.82      30.32
      7654 MARTIN           1250       4.31       13.3


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28974745/viewspace-2155267/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/28974745/viewspace-2155267/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值