概念
官网定义：
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_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 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 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 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 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 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 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 (<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 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 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 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 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欢送关注我的公众号，一起学习。