概念
官网定义：
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 返回不同值，依据解决行的程序排序
限度
- 在剖析函数中只能应用expr，position和c_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必须为日期类型
分类
| Type | Used For |
|---|---|
| Reporting | Calculating 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. |
| Windowing | Calculating 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. |
| Ranking | Calculating ranks, percentiles, and n-tiles of the values in a result set. |
| LAG/LEAD | Finding a value in a row a specified number of rows from a current row. |
| FIRST/LAST | First or last value in an ordered group. |
| Hypothetical Rank and Distribution | The 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_salfrom 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_salfrom 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,salaryfrom employees e1where 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 1200811 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_salfrom employeesorder by department_id) empwhere salary=max_salorder 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 12008EMPLOYEE_ID LAST_NAME DEPARTMENT_ID JOB_ID SALARY MAX_SAL----------- --------------- ------------- ---------- ---------- ---------- 178 Grant SA_REP 7000 700012 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_sal2from employeesorder 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_sal2from employeesgroup by department_id,job_idorder 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 10000DEPARTMENT_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 700020 rows selected.- 查问各职位薪水总计占所在部门薪水总计超过50%的职位
select emp.*,100 * round(job_sal1/dept_sal2, 2)||'%' Percentfrom (select department_id,job_id,sum(salary) job_sal1,sum(sum(salary)) over (partition by department_id) dept_sal2from employeesgroup 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_sal3from employeesgroup by department_id,job_id) empwhere 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 111 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 pct2from 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_cum2from 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_cum2from 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_cum2from 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_cum2from 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_cum2from 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_cum2from 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_AVGFROM employeesGROUP BY hire_date;HIRE_DATE SUM_SAL1 CENTERED_1_DAY_AVG------------------ ---------- ------------------13-JAN-01 17000 1700007-JUN-02 36808 3680816-AUG-02 9000 1050417-AUG-02 12008 1050407-DEC-02 11000 1100001-MAY-03 7900 790018-MAY-03 3100 310017-JUN-03 24000 2400014-JUL-03 3600 360017-SEP-03 4400 440017-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_numberfrom 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_rankfrom employees) ewhere 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_salFROM employeesWHERE job_id = 'PU_CLERK'ORDER BY hire_date;HIRE_DATE LAST_NAME SALARY PREV_SAL NEXT_SAL------------------ --------------- ---------- ---------- ----------18-MAY-03 Khoo 3100 0 280024-JUL-05 Tobias 2800 3100 290024-DEC-05 Baida 2900 2800 260015-NOV-06 Himuro 2600 2900 250010-AUG-07 Colmenares 2500 2600 0FIRST/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 employeesORDER 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 8200NTILE
- 语法
NTILE (expr) OVER ([query_partition_clause] order_by_clause)
- 查问人员信息及其对应的薪水等级，将薪水分为5个等级
SELECT employee_id,last_name,salary,NTILE(5) OVER (ORDER BY salary DESC) AS quartileFROM employeesWHERE 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 5FIRST_VALUE/LAST_VALUE
- 语法
FIRST_VALUE|LAST_VALUE ( <expr> ) [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 bestFROM 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 (<expr> [, <delimiter>) 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,Raphaelyselect 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,RaphaelyCUME_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 employeesWHERE 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 1PERCENT_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 employeesWHERE 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 1Hypothetical 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欢送关注我的公众号，一起学习。