0
分析函数
source link: https://segmentfault.com/a/1190000039192556
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
概念
官方定义：
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 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 ( <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,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
欢迎关注我的公众号，一起学习。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK