分析函数


概念

官方定义:

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVINGclauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

有以下几个关键点:

  • 对一组数据进行计算,返回多行

  • 不需要进行多表联合,提高性能

  • 在所有表连接和所有WHERE, GROUP BY和HAVING字句之后处理,在ORDER BY子句之前处理

  • 只能位于SELECT或者ORDER BY子句

语法

  • 常用analytic_function

    • AVG,MAX,MIN,SUM,COUNT
    • DENSE_RANK,RANK,ROW_NUMBER, CUME_DIST
    • LAG,LEAD
    • FIRST,LAST
    • NTILE
    • FIRST_VALUE/LAST_VALUE
    • LISTAGG
    • RATIO_TO_REPORT
  • arguments个数:0~3

  • arguments类型:数字类型或可以隐式转为为数字类型的非数字类型

  • analytic_clause

    • 在FROM,WHERE,GROUP BY和HAVING子句之后进行计算

    • 在SELECT和ORDER BY子句指定带analytic_clause的分析函数

    • query_partition_clause

      • 根据expr对查询结果进行分组

      • 忽略该语句则查询结果为一个分组

      • 分析函数使用上面的分支,不带括号

      • Expr可以是常量,字段,非分析函数,函数表达式

    • order_by_clause

      • 指定分区中数据的排序方式
      • 当排序结果有相同值时:
        • DENSE_RANK, RANK返回相同值
        • ROW_NUMBER 返回不同值,根据处理行的顺序排序
      • 限制
        • 在分析函数中只能使用exprpositionc_alias无效
        • 在分析函数中使用RANGE关键字且使用以下窗口就可以使用多个排序键
          • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(RANGE UNBOUNDED PRECEDING)
          • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
          • RANGE BETWEEN CURRENT ROW AND CURRENT ROW
          • RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    • windowing_clause

      • 支持windowing_clause的分析函数:AVG,MAX,MIN,SUM,COUNT
      • ROWS | RANGE
        • 为每行定义一个窗口用于计算函数结果
        • ROWS:以行指定窗口
        • RANGE:以逻辑偏移量指定窗口
      • BETWEEN ... AND
        • 指定窗口的起始点和结束点
        • 省略BETWEEN,则指定的点为起始点,结束点默认为当前行(current row)
      • 只有指定了order_by_clause才能使用windowing_clause
      • 如果省略了windowing_clause,则默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      • UNBOUNDED PRECEDING:从分区的第一行开始,起始点
      • UNBOUNDED FOLLOWING:到分区的最后一行结束,结束点
      • CURRENT ROW
        • 作为起始点时,CURRENT ROW指定窗口开始于当前行或者某个值(取决于使用ROW还是RANGE),这时结束点不能是value_expr PRECEDING。
        • 作为结束点时,CURRENT ROW指定窗口结束于当前行或者某个值(取决于使用ROW还是RANGE),这时开始点不能是value_expr FOLLOWING。
      • value_expr PRECEDING or value_expr FOLLOWING
        • 对于RANGE或者ROW
          • 如果起始点是value_expr FOLLOWING,则结束点必须是value_expr FOLLOWING
          • 如果结束点是value_expr PRECEDING,则起始点必须是value_expr PRECEDING
        • 如果指定了ROWS
          • value_expr是一个物理偏移量。必须是常量或表达式, 并且必须计算为正数数值
          • 如果value_expr是起始点的一部分,则必须位于结束点之前的行
        • 如果指定了RANGE
          • value_expr是一个逻辑偏移量。必须是一个常量或表达式, 计算结果为正值数值或间隔文本
          • order_by_clause只能使用一个排序键
          • 如果value_expr为数值,则ORDER BY expr必须为数字或日期类型
          • 如果value_expr为间隔值,则ORDER BY expr必须为日期类型

分类

TypeUsed For
ReportingCalculating shares, for example, market share. Works with these functions: SUM, AVG, MIN, MAX, COUNT (with/without DISTINCT), VARIANCE, STDDEV, RATIO_TO_REPORT, and new statistical functions. Note that the DISTINCT keyword may be used in those reporting functions that support DISTINCT in aggregate mode.
WindowingCalculating cumulative and moving aggregates. Works with these functions: SUM, AVG, MIN, MAX, COUNT, VARIANCE, STDDEV, FIRST_VALUE, LAST_VALUE, and new statistical functions. Note that the DISTINCTkeyword is not supported in windowing functions except for MAX and MIN.
RankingCalculating ranks, percentiles, and n-tiles of the values in a result set.
LAG/LEADFinding a value in a row a specified number of rows from a current row.
FIRST/LASTFirst or last value in an ordered group.
Hypothetical Rank and DistributionThe rank or percentile that a row would have if inserted into a specified data set.

Reporting

  • 查询人员信息以及公司平均薪水,最小薪水,最大薪水,薪水总计以及人数
select employee_id,last_name,department_id,salary,
avg(salary) over () avg_sal,
max(salary) over () max_sal,
min(salary) over () min_sal,
sum(salary) over () sum_sal,
count(salary) over () count_sal
from employees order by department_id;

EMPLOYEE_ID LAST_NAME       DEPARTMENT_ID     SALARY    AVG_SAL    MAX_SAL    MIN_SAL    SUM_SAL  COUNT_SAL
----------- --------------- ------------- ---------- ---------- ---------- ---------- ---------- ----------
        200 Whalen                     10       4400 6461.83178      24000       2100     691416        107
        201 Hartstein                  20      13000 6461.83178      24000       2100     691416        107
        202 Fay                        20       6000 6461.83178      24000       2100     691416        107
        114 Raphaely                   30      11000 6461.83178      24000       2100     691416        107
        119 Colmenares                 30       2500 6461.83178      24000       2100     691416        107
        115 Khoo                       30       3100 6461.83178      24000       2100     691416        107
        116 Baida                      30       2900 6461.83178      24000       2100     691416        107
        117 Tobias                     30       2800 6461.83178      24000       2100     691416        107
        118 Himuro                     30       2600 6461.83178      24000       2100     691416        107
        203 Mavris                     40       6500 6461.83178      24000       2100     691416        107
        198 OConnell                   50       2600 6461.83178      24000       2100     691416        107
        ......
  • 查询人员信息以及各部门平均薪水,最小薪水,最大薪水,薪水总计以及人数
select employee_id,last_name,department_id,salary,
avg(salary) over (partition by department_id) avg_sal,
max(salary) over (partition by department_id) max_sal,
min(salary) over (partition by department_id) min_sal,
sum(salary) over (partition by department_id) sum_sal,
count(salary) over (partition by department_id) count_sal
from employees order by department_id;

EMPLOYEE_ID LAST_NAME       DEPARTMENT_ID     SALARY    AVG_SAL    MAX_SAL    MIN_SAL    SUM_SAL  COUNT_SAL
----------- --------------- ------------- ---------- ---------- ---------- ---------- ---------- ----------
        200 Whalen                     10       4400       4400       4400       4400       4400          1
        201 Hartstein                  20      13000       9500      13000       6000      19000          2
        202 Fay                        20       6000       9500      13000       6000      19000          2
        114 Raphaely                   30      11000       4150      11000       2500      24900          6
        119 Colmenares                 30       2500       4150      11000       2500      24900          6
        115 Khoo                       30       3100       4150      11000       2500      24900          6
        116 Baida                      30       2900       4150      11000       2500      24900          6
        117 Tobias                     30       2800       4150      11000       2500      24900          6
        118 Himuro                     30       2600       4150      11000       2500      24900          6
        203 Mavris                     40       6500       6500       6500       6500       6500          1
        198 OConnell                   50       2600 3475.55556       8200       2100     156400         45
        ......
  • 查询部门最高薪水的员工信息(不使用分析函数)
select employee_id,last_name,e1.department_id,job_id,salary
from employees e1
where e1.salary=(select max(salary) from employees e2 where e1.department_id=e2.department_id)
order by department_id;

EMPLOYEE_ID LAST_NAME       DEPARTMENT_ID JOB_ID         SALARY
----------- --------------- ------------- ---------- ----------
        200 Whalen                     10 AD_ASST          4400
        201 Hartstein                  20 MK_MAN          13000
        114 Raphaely                   30 PU_MAN          11000
        203 Mavris                     40 HR_REP           6500
        121 Fripp                      50 ST_MAN           8200
        103 Hunold                     60 IT_PROG          9000
        204 Baer                       70 PR_REP          10000
        145 Russell                    80 SA_MAN          14000
        100 King                       90 AD_PRES         24000
        108 Greenberg                 100 FI_MGR          12008
        205 Higgins                   110 AC_MGR          12008

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 298340369

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |    44 |     5  (20)| 00:00:01 |
|   1 |  SORT ORDER BY                |                   |     1 |    44 |     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS                |                   |     1 |    44 |     5  (20)| 00:00:01 |
|   3 |    NESTED LOOPS               |                   |    10 |    44 |     5  (20)| 00:00:01 |
|   4 |     VIEW                      | VW_SQ_1           |     1 |    16 |     4  (25)| 00:00:01 |
|*  5 |      FILTER                   |                   |       |       |            |          |
|   6 |       HASH GROUP BY           |                   |     1 |     7 |     4  (25)| 00:00:01 |
|   7 |        TABLE ACCESS FULL      | EMPLOYEES         |   107 |   749 |     3   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|*  9 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     1 |    28 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(MAX("SALARY")>0)
   8 - access("E1"."DEPARTMENT_ID"="ITEM_1")
   9 - filter("E1"."SALARY"="MAX(SALARY)")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
       1178  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         11  rows processed
  • 查询部门最高薪水的员工信息(使用分析函数)
select emp.*
from (select employee_id,last_name,department_id,job_id,salary,
max(salary) over (partition by department_id) max_sal
from employees
order by department_id) emp
where salary=max_sal
order by department_id;

EMPLOYEE_ID LAST_NAME       DEPARTMENT_ID JOB_ID         SALARY    MAX_SAL
----------- --------------- ------------- ---------- ---------- ----------
        200 Whalen                     10 AD_ASST          4400       4400
        201 Hartstein                  20 MK_MAN          13000      13000
        114 Raphaely                   30 PU_MAN          11000      11000
        203 Mavris                     40 HR_REP           6500       6500
        121 Fripp                      50 ST_MAN           8200       8200
        103 Hunold                     60 IT_PROG          9000       9000
        204 Baer                       70 PR_REP          10000      10000
        145 Russell                    80 SA_MAN          14000      14000
        100 King                       90 AD_PRES         24000      24000
        108 Greenberg                 100 FI_MGR          12008      12008
        205 Higgins                   110 AC_MGR          12008      12008

EMPLOYEE_ID LAST_NAME       DEPARTMENT_ID JOB_ID         SALARY    MAX_SAL
----------- --------------- ------------- ---------- ---------- ----------
        178 Grant                         SA_REP           7000       7000

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 720055818

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |   107 |  6848 |     3   (0)| 00:00:01 |
|*  1 |  VIEW               |           |   107 |  6848 |     3   (0)| 00:00:01 |
|   2 |   WINDOW SORT       |           |   107 |  2996 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES |   107 |  2996 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SALARY"="MAX_SAL")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       1312  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         12  rows processed

可以看到使用分析函数的SQL性能有一定提升。

  • 查询人员信息以及各部门各职位薪水总计和各部门薪水总计
select employee_id,last_name,department_id,job_id,salary,
sum(salary) over (partition by department_id,job_id) job_sal1,
sum(salary) over (partition by department_id) dept_sal2
from employees
order by department_id;

EMPLOYEE_ID LAST_NAME       DEPARTMENT_ID JOB_ID         SALARY   JOB_SAL1  DEPT_SAL2
----------- --------------- ------------- ---------- ---------- ---------- ----------
        200 Whalen                     10 AD_ASST          4400       4400       4400
        201 Hartstein                  20 MK_MAN          13000      13000      19000
        202 Fay                        20 MK_REP           6000       6000      19000
        118 Himuro                     30 PU_CLERK         2600      13900      24900
        119 Colmenares                 30 PU_CLERK         2500      13900      24900
        115 Khoo                       30 PU_CLERK         3100      13900      24900
        116 Baida                      30 PU_CLERK         2900      13900      24900
        117 Tobias                     30 PU_CLERK         2800      13900      24900
        114 Raphaely                   30 PU_MAN          11000      11000      24900
        203 Mavris                     40 HR_REP           6500       6500       6500
        198 OConnell                   50 SH_CLERK         2600      64300     156400
        ......
  • 查询各部门各职位薪水总计以及各部门薪水总计
select department_id,job_id,
sum(salary) job_sal1,
sum(sum(salary)) over (partition by department_id) dept_sal2
from employees
group by department_id,job_id
order by department_id;

DEPARTMENT_ID JOB_ID       JOB_SAL1  DEPT_SAL2
------------- ---------- ---------- ----------
           10 AD_ASST          4400       4400
           20 MK_MAN          13000      19000
           20 MK_REP           6000      19000
           30 PU_CLERK        13900      24900
           30 PU_MAN          11000      24900
           40 HR_REP           6500       6500
           50 SH_CLERK        64300     156400
           50 ST_CLERK        55700     156400
           50 ST_MAN          36400     156400
           60 IT_PROG         28800      28800
           70 PR_REP          10000      10000

DEPARTMENT_ID JOB_ID       JOB_SAL1  DEPT_SAL2
------------- ---------- ---------- ----------
           80 SA_MAN          61000     304500
           80 SA_REP         243500     304500
           90 AD_PRES         24000      58000
           90 AD_VP           34000      58000
          100 FI_ACCOUNT      39600      51608
          100 FI_MGR          12008      51608
          110 AC_ACCOUNT       8300      20308
          110 AC_MGR          12008      20308
              SA_REP           7000       7000

20 rows selected.
  • 查询各职位薪水总计占所在部门薪水总计超过50%的职位
select emp.*,100 * round(job_sal1/dept_sal2, 2)||'%' Percent
from (select department_id,job_id,
sum(salary) job_sal1,
sum(sum(salary)) over (partition by department_id) dept_sal2
from employees
group by department_id,job_id) emp 
where job_sal1>dept_sal2*0.5;

DEPARTMENT_ID JOB_ID       JOB_SAL1  DEPT_SAL2 PERCENT
------------- ---------- ---------- ---------- -----------------------------------------
           10 AD_ASST          4400       4400 100%
           20 MK_MAN          13000      19000 68%
           30 PU_CLERK        13900      24900 56%
           40 HR_REP           6500       6500 100%
           60 IT_PROG         28800      28800 100%
           70 PR_REP          10000      10000 100%
           80 SA_REP         243500     304500 80%
           90 AD_VP           34000      58000 59%
          100 FI_ACCOUNT      39600      51608 77%
          110 AC_MGR          12008      20308 59%
              SA_REP           7000       7000 100%

11 rows selected.
  • 查询各职位薪水总计占所在部门薪水总计超过50%的职位(使用ratio_to_report函数)
select emp.*
from (select department_id,job_id,
sum(salary) job_sal1,
sum(sum(salary)) over (partition by department_id) dept_sal2,
ratio_to_report(sum(salary)) over (partition by department_id) job_to_dept_sal3
from employees
group by department_id,job_id) emp
where job_to_dept_sal3>0.5;

DEPARTMENT_ID JOB_ID       JOB_SAL1  DEPT_SAL2 JOB_TO_DEPT_SAL3
------------- ---------- ---------- ---------- ----------------
           10 AD_ASST          4400       4400                1
           20 MK_MAN          13000      19000       .684210526
           30 PU_CLERK        13900      24900       .558232932
           40 HR_REP           6500       6500                1
           60 IT_PROG         28800      28800                1
           70 PR_REP          10000      10000                1
           80 SA_REP         243500     304500       .799671593
           90 AD_VP           34000      58000       .586206897
          100 FI_ACCOUNT      39600      51608       .767322896
          110 AC_MGR          12008      20308       .591294071
              SA_REP           7000       7000                1

11 rows selected.
  • 查询每个人的薪水占部门薪水合计及公司薪水总计的百分比(使用ratio_to_report函数)
select employee_id,last_name,department_id,hire_date,salary,
ratio_to_report(salary) over(partition by department_id) as pct1,
ratio_to_report(salary) over() as pct2
from employees;

EMPLOYEE_ID LAST_NAME       DEPARTMENT_ID HIRE_DATE              SALARY       PCT1       PCT2
----------- --------------- ------------- ------------------ ---------- ---------- ----------
        200 Whalen                     10 17-SEP-03                4400          1 .006363752
        201 Hartstein                  20 17-FEB-04               13000 .684210526 .018801995
        202 Fay                        20 17-AUG-05                6000 .315789474 .008677844
        114 Raphaely                   30 07-DEC-02               11000 .441767068  .01590938
        119 Colmenares                 30 10-AUG-07                2500 .100401606 .003615768
        115 Khoo                       30 18-MAY-03                3100 .124497992 .004483553
        116 Baida                      30 24-DEC-05                2900 .116465863 .004194291
        117 Tobias                     30 24-JUL-05                2800 .112449799  .00404966
        118 Himuro                     30 15-NOV-06                2600 .104417671 .003760399
        203 Mavris                     40 07-JUN-02                6500          1 .009400997
        198 OConnell                   50 21-JUN-07                2600 .016624041 .003760399
        ......

Windowing

  • Cumulative Aggregate Function

    • 查询按部门的薪水合计及公司薪水总计
    select employee_id,last_name,department_id,salary,
    sum(salary) over (partition by department_id order by department_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) dept_sal_cum1,
    sum(salary) over (order by department_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) dept_sal_cum2
    from employees;
    
    EMPLOYEE_ID LAST_NAME       DEPARTMENT_ID     SALARY DEPT_SAL_CUM1 DEPT_SAL_CUM2
    ----------- --------------- ------------- ---------- ------------- -------------
            200 Whalen                     10       4400          4400        691416
            201 Hartstein                  20      13000         19000        691416
            202 Fay                        20       6000         19000        691416
            114 Raphaely                   30      11000         24900        691416
            119 Colmenares                 30       2500         24900        691416
            115 Khoo                       30       3100         24900        691416
            116 Baida                      30       2900         24900        691416
            117 Tobias                     30       2800         24900        691416
            118 Himuro                     30       2600         24900        691416
            203 Mavris                     40       6500          6500        691416
            198 OConnell                   50       2600        156400        691416
            ......
    

    和以下SQL等价:

    select employee_id,last_name,department_id,salary,
    sum(salary) over (partition by department_id) dept_sal_cum1,
    sum(salary) over () dept_sal_cum2
    from employees;
    
    • 查询按部门的薪水累计及不按部门的薪水累计
    select employee_id,last_name,department_id,salary,
    sum(salary) over (partition by department_id order by department_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) dept_sal_cum1,
    sum(salary) over (order by department_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) dept_sal_cum2
    from employees;
    
    EMPLOYEE_ID LAST_NAME       DEPARTMENT_ID     SALARY DEPT_SAL_CUM1 DEPT_SAL_CUM2
    ----------- --------------- ------------- ---------- ------------- -------------
            200 Whalen                     10       4400          4400          4400
            201 Hartstein                  20      13000         13000         17400
            202 Fay                        20       6000         19000         23400
            114 Raphaely                   30      11000         11000         34400
            119 Colmenares                 30       2500         13500         36900
            115 Khoo                       30       3100         16600         40000
            116 Baida                      30       2900         19500         42900
            117 Tobias                     30       2800         22300         45700
            118 Himuro                     30       2600         24900         48300
            203 Mavris                     40       6500          6500         54800
            198 OConnell                   50       2600          2600         57400
            ......
    

    和以下SQL等价:

    select employee_id,last_name,department_id,salary,
    sum(salary) over (partition by department_id order by department_id ROWS UNBOUNDED PRECEDING) dept_sal_cum1,
    sum(salary) over (order by department_id ROWS UNBOUNDED PRECEDING) dept_sal_cum2
    from employees;
    
    • 查询按部门分区从分区第一行到本行前一行的累计和到本行后一行的累计
    select employee_id,last_name,department_id,salary,
    sum(salary) over (partition by department_id order by department_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) dept_sal_cum1,
    sum(salary) over (partition by department_id order by department_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) dept_sal_cum2
    from employees;
    
    EMPLOYEE_ID LAST_NAME       DEPARTMENT_ID     SALARY DEPT_SAL_CUM1 DEPT_SAL_CUM2
    ----------- --------------- ------------- ---------- ------------- -------------
            200 Whalen                     10       4400                        4400
            201 Hartstein                  20      13000                       19000
            202 Fay                        20       6000         13000         19000
            114 Raphaely                   30      11000                       13500
            119 Colmenares                 30       2500         11000         16600
            115 Khoo                       30       3100         13500         19500
            116 Baida                      30       2900         16600         22300
            117 Tobias                     30       2800         19500         24900
            118 Himuro                     30       2600         22300         24900
            203 Mavris                     40       6500                        6500
            198 OConnell                   50       2600                        5200
            ......
    
  • Moving Aggregate Function

    • 查询按部门分区从分区前一行到本行的累计以及到本行到后一行的累计
    select employee_id,last_name,department_id,salary,
    sum(salary) over (partition by department_id order by department_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) dept_sal_cum1,
    sum(salary) over (partition by department_id order by department_id ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) dept_sal_cum2
    from employees;
    
    EMPLOYEE_ID LAST_NAME       DEPARTMENT_ID     SALARY DEPT_SAL_CUM1 DEPT_SAL_CUM2
    ----------- --------------- ------------- ---------- ------------- -------------
            200 Whalen                     10       4400          4400          4400
            201 Hartstein                  20      13000         13000         19000
            202 Fay                        20       6000         19000          6000
            114 Raphaely                   30      11000         11000         13500
            119 Colmenares                 30       2500         13500          5600
            115 Khoo                       30       3100          5600          6000
            116 Baida                      30       2900          6000          5700
            117 Tobias                     30       2800          5700          5400
            118 Himuro                     30       2600          5400          2600
            203 Mavris                     40       6500          6500          6500
            198 OConnell                   50       2600          2600          5200
            ......
    
  • Centered Aggregate

    • 查询按照入职日期分组的薪水合计,以及入职日期相邻1天的人员的平均薪水
    SELECT hire_date, SUM(salary) AS sum_sal1, 
    AVG(SUM(salary)) OVER (ORDER BY hire_date RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING) AS CENTERED_1_DAY_AVG
    FROM employees
    GROUP BY hire_date;
    
    HIRE_DATE            SUM_SAL1 CENTERED_1_DAY_AVG
    ------------------ ---------- ------------------
    13-JAN-01               17000              17000
    07-JUN-02               36808              36808
    16-AUG-02                9000              10504
    17-AUG-02               12008              10504
    07-DEC-02               11000              11000
    01-MAY-03                7900               7900
    18-MAY-03                3100               3100
    17-JUN-03               24000              24000
    14-JUL-03                3600               3600
    17-SEP-03                4400               4400
    17-OCT-03                3500               3500
    ......
    

Ranking

  • RANK:返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名

  • DENSE_RANK:返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。

  • ROW_NUMBER:返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。

  • 查询按部门的薪水从低到高排名人员信息

select employee_id,last_name,department_id,salary,
RANK() over (partition by department_id order by salary) rank,
DENSE_RANK() over (partition by department_id order by salary) dense_rank,
ROW_NUMBER() over (partition by department_id order by salary) row_number
from employees where department_id=50;


EMPLOYEE_ID LAST_NAME       DEPARTMENT_ID     SALARY       RANK DENSE_RANK ROW_NUMBER
----------- --------------- ------------- ---------- ---------- ---------- ----------
        132 Olson                      50       2100          1          1          1
        128 Markle                     50       2200          2          2          2
        136 Philtanker                 50       2200          2          2          3
        135 Gee                        50       2400          4          3          4
        127 Landry                     50       2400          4          3          5
        131 Marlow                     50       2500          6          4          6
        144 Vargas                     50       2500          6          4          7
        182 Sullivan                   50       2500          6          4          8
        191 Perkins                    50       2500          6          4          9
        140 Patel                      50       2500          6          4         10
        198 OConnell                   50       2600         11          5         11
        ......
  • 查询每个部门的薪水排名前三名人员信息
select e.*
from (select employee_id,last_name,department_id,salary,
DENSE_RANK() over (partition by department_id order by salary desc) dense_rank
from employees) e
where dense_rank<=3;


EMPLOYEE_ID LAST_NAME       DEPARTMENT_ID     SALARY DENSE_RANK
----------- --------------- ------------- ---------- ----------
        200 Whalen                     10       4400          1
        201 Hartstein                  20      13000          1
        202 Fay                        20       6000          2
        114 Raphaely                   30      11000          1
        115 Khoo                       30       3100          2
        116 Baida                      30       2900          3
        203 Mavris                     40       6500          1
        121 Fripp                      50       8200          1
        120 Weiss                      50       8000          2
        122 Kaufling                   50       7900          3
        103 Hunold                     60       9000          1
        ......

LAG/LEAD

  • 语法

{LAG | LEAD} ( value_expr [, offset] [, default] ) [RESPECT NULLS|IGNORE NULLS] OVER ( [query_partition_clause] order_by_clause )

lag 和lead函数可以获取结果集中,按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联);lag,lead分别是向前,向后;lag 和lead 有三个参数,第一个参数是列名,第二个参数是偏移的offset,第三个参数是超出记录窗口时的默认值)。lag(expression<,offset><,default>)函数可以访问组内当前行之前的行,而lead(expression<,offset><,default>)函数则正相反,可以访问组内当前行之后的行。其中,offset是正整数,默认为1.因组内第一个条记录没有之前的行,最后一行没有之后的行,default就是用于处理这样的信息,默认为空.注意:这2个函数必须指定 order By 字句。

  • 查询人员薪水及其前面入职人员的薪水和后面入职人员的薪水
SELECT hire_date, last_name, salary,
LAG(salary, 1, 0 ) OVER (ORDER BY hire_date) AS prev_sal,
LEAD(salary, 1, 0 ) OVER (ORDER BY hire_date) AS next_sal
FROM employees
WHERE job_id = 'PU_CLERK'
ORDER BY hire_date;

HIRE_DATE          LAST_NAME           SALARY   PREV_SAL   NEXT_SAL
------------------ --------------- ---------- ---------- ----------
18-MAY-03          Khoo                  3100          0       2800
24-JUL-05          Tobias                2800       3100       2900
24-DEC-05          Baida                 2900       2800       2600
15-NOV-06          Himuro                2600       2900       2500
10-AUG-07          Colmenares            2500       2600          0

FIRST/LAST

  • 语法

aggregate_function KEEP ( DENSE_RANK LAST ORDER BY expr [ DESC | ASC ] [NULLS { FIRST | LAST }] [, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...) [OVER query_partitioning_clause]

first/last函数允许我们对某数据集进行排序,并对排序结果的第一条记录和最后一条记录进行处理。在查询出第一条或者最后一条记录后,我们需要应用一个聚合函数来处理特定列,这是为了保证返回结果的唯一性,因为排名第一的记录和排名最后的记录可能会存在多个。使用first/last函数可以避免自连接或者子查询,因此可以提高处理效率。

  • 使用说明

    • first和last函数有over子句就是分析函数,没有就是聚合函数。
    • 函数的参数必须是数字类型(或者其他类型可转为数字类型),返回相同类型
    • aggregate_function可以是MIN,MAX,SUM,AVG,COUNT,VARIANCE,STDDEV
  • 查询人员信息及其所在部门的最低和最高薪水

SELECT employee_id, last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY salary) OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY salary) OVER (PARTITION BY department_id) "Best"
FROM employees
ORDER BY department_id, salary, last_name;

EMPLOYEE_ID LAST_NAME       DEPARTMENT_ID     SALARY      Worst       Best
----------- --------------- ------------- ---------- ---------- ----------
        200 Whalen                     10       4400       4400       4400
        202 Fay                        20       6000       6000      13000
        201 Hartstein                  20      13000       6000      13000
        119 Colmenares                 30       2500       2500      11000
        118 Himuro                     30       2600       2500      11000
        117 Tobias                     30       2800       2500      11000
        116 Baida                      30       2900       2500      11000
        115 Khoo                       30       3100       2500      11000
        114 Raphaely                   30      11000       2500      11000
        203 Mavris                     40       6500       6500       6500
        132 Olson                      50       2100       2100       8200

NTILE

  • 语法

NTILE (expr) OVER ([query_partition_clause] order_by_clause)

  • 查询人员信息及其对应的薪水等级,将薪水分为5个等级
SELECT employee_id,last_name,salary,
NTILE(5) OVER (ORDER BY salary DESC) AS quartile
FROM employees
WHERE department_id=30;

EMPLOYEE_ID LAST_NAME                     SALARY   QUARTILE
----------- ------------------------- ---------- ----------
        114 Raphaely                       11000          1
        115 Khoo                            3100          1
        116 Baida                           2900          2
        117 Tobias                          2800          3
        118 Himuro                          2600          4
        119 Colmenares                      2500          5

FIRST_VALUE/LAST_VALUE

  • 语法

FIRST_VALUE|LAST_VALUE ( ) [RESPECT NULLS|IGNORE NULLS] OVER (analytic clause );

  • 查询人员信息及其所在部门最低薪水和最高薪水人员姓名
SELECT employee_id,last_name,department_id,salary,
FIRST_VALUE(last_name) OVER (PARTITION BY department_id ORDER BY salary) AS worst,
LAST_VALUE(last_name) OVER (PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS best
FROM employees order by department_id,salary;

EMPLOYEE_ID LAST_NAME            DEPARTMENT_ID     SALARY WORST                BEST
----------- -------------------- ------------- ---------- -------------------- --------------------
        200 Whalen                          10       4400 Whalen               Whalen
        202 Fay                             20       6000 Fay                  Hartstein
        201 Hartstein                       20      13000 Fay                  Hartstein
        119 Colmenares                      30       2500 Colmenares           Raphaely
        118 Himuro                          30       2600 Colmenares           Raphaely
        117 Tobias                          30       2800 Colmenares           Raphaely
        116 Baida                           30       2900 Colmenares           Raphaely
        115 Khoo                            30       3100 Colmenares           Raphaely
        114 Raphaely                        30      11000 Colmenares           Raphaely
        203 Mavris                          40       6500 Mavris               Mavris
        132 Olson                           50       2100 Olson                Fripp
        ......

LISTAGG

  • 语法

LISTAGG ( [, ) WITHIN GROUP (ORDER BY <oby_expression_list>)

  • 查询每个部门所有人员姓名并按照薪水从低到高排序
select department_id,
listagg(last_name,',') within group (order by salary) name 
from employees where department_id in (10,20,30) group by department_id;

DEPARTMENT_ID NAME
------------- --------------------------------------------------
           10 Whalen
           20 Fay,Hartstein
           30 Colmenares,Himuro,Tobias,Baida,Khoo,Raphaely
select department_id,last_name,salary,
listagg(last_name,',') within group (order by salary) over (partition by department_id) name 
from employees where department_id in (10,20,30);


DEPARTMENT_ID LAST_NAME                SALARY NAME
------------- -------------------- ---------- --------------------------------------------------
           10 Whalen                     4400 Whalen
           20 Fay                        6000 Fay,Hartstein
           20 Hartstein                 13000 Fay,Hartstein
           30 Colmenares                 2500 Colmenares,Himuro,Tobias,Baida,Khoo,Raphaely
           30 Himuro                     2600 Colmenares,Himuro,Tobias,Baida,Khoo,Raphaely
           30 Tobias                     2800 Colmenares,Himuro,Tobias,Baida,Khoo,Raphaely
           30 Baida                      2900 Colmenares,Himuro,Tobias,Baida,Khoo,Raphaely
           30 Khoo                       3100 Colmenares,Himuro,Tobias,Baida,Khoo,Raphaely
           30 Raphaely                  11000 Colmenares,Himuro,Tobias,Baida,Khoo,Raphaely

CUME_DIST

  • 语法

CUME_DIST ( ) OVER ( [query_partition_clause] order_by_clause )

  • 计算每个人在本部门按照薪水排列中的相对位置
SELECT employee_id,last_name,department_id,salary,
CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) AS cume_dist 
FROM employees
WHERE department_id=30;


EMPLOYEE_ID LAST_NAME            DEPARTMENT_ID     SALARY  CUME_DIST
----------- -------------------- ------------- ---------- ----------
        119 Colmenares                      30       2500 .166666667
        118 Himuro                          30       2600 .333333333
        117 Tobias                          30       2800         .5
        116 Baida                           30       2900 .666666667
        115 Khoo                            30       3100 .833333333
        114 Raphaely                        30      11000          1

PERCENT_RANK

  • 语法

PERCENT_RANK () OVER ([query_partition_clause] order_by_clause)

  • 计算每个人在本部门按照薪水排列中的相对位置
SELECT department_id,last_name,salary,
PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS pr 
FROM employees
WHERE department_id=30;

DEPARTMENT_ID LAST_NAME                SALARY         PR
------------- -------------------- ---------- ----------
           30 Colmenares                 2500          0
           30 Himuro                     2600         .2
           30 Tobias                     2800         .4
           30 Baida                      2900         .6
           30 Khoo                       3100         .8
           30 Raphaely                  11000          1

Hypothetical Rank

  • 语法

[RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST]( constant expression [, ...] ) WITHIN GROUP ( ORDER BY order by expression [ASC|DESC] [NULLS FIRST|NULLS LAST][, ...] )

  • 假如50部门新来一位工资4000的员工,计算该员工在50部门薪水的位置
select 
RANK(50,4000) within group (order by department_id, salary) rank,
DENSE_RANK(50,4000) within group (order by department_id, salary) dense_rank,
PERCENT_RANK(50,4000) within group (order by department_id, salary) percent_rank,
cume_dist(50,4000) within group (order by department_id, salary) cume_dist 
from employees where department_id=50;

      RANK DENSE_RANK PERCENT_RANK  CUME_DIST
---------- ---------- ------------ ----------
        38         18   .822222222 .847826087

欢迎关注我的公众号,一起学习。

  • 分享:
评论
还没有评论
    发表评论 说点什么