教为学:Oracle SQL学习之路(一):分析函数之排名
前言
本来想对分析函数来个概述的,可是觉得这概念让人头疼,不如先来几个实例直截了当。
三个分析排名函数的对比。
先上SQL:
再上结果:
CNT |
RN(无并列) |
RK(并列不连续) |
DRK(并列连续) |
|
10 |
1 |
1 |
1 |
|
20 |
2 |
2 |
2 |
|
30 |
3 |
3 |
3 |
|
30 |
4 |
3 |
3 |
|
40 |
5 |
5 |
4 |
|
70 |
6 |
6 |
5 |
从结果上可以很清晰的看出row_number,rank,dense_rank这几个函数在结果上的区别。
接下来,我们一个个的讲解这些函数。
Syntax
RANK函数:
DENSE_RANK函数:
ROW_NUMBER函数:
除了函数名,你看出它们之间有区别吗?没有,确实,在用法上,他们没有任何区别。区别在处理的结果上。
区别:
RANK() 函数:
返回唯一值,碰到相同的值的时候,排名一致,不过接下的值会把相同的值所占的位置空缺出来。
DENSE_RANK函数:
返回唯一值,碰到相同的值的时候,排名一致,接下来的数值不会空缺,接上面的。
ROW_NUMBER函数:
返回唯一值,碰到相同的值按照一定的排序,不采用相同的值,一直排列下去。
用法详解:
代码如下:
-
department_id,
-
last_name,
-
employee_id,
-
from employees
-
;
执行结果如下(省略一部分):
DEPARTMENT_ID |
LAST_NAME |
EMPLOYEE_ID |
EMP_ID |
Grant |
178 |
1 |
|
10 |
Whalen |
200 |
1 |
20 |
Hartstein |
201 |
1 |
20 |
Fay |
202 |
2 |
30 |
Raphaely |
114 |
1 |
30 |
Khoo |
115 |
2 |
30 |
Baida |
116 |
3 |
30 |
Tobias |
117 |
4 |
30 |
Himuro |
118 |
5 |
30 |
Colmenares |
119 |
6 |
40 |
Mavris |
203 |
1 |
50 |
Weiss |
120 |
1 |
50 |
Fripp |
121 |
2 |
50 |
Kaufling |
122 |
3 |
50 |
Vollman |
123 |
4 |
50 |
Mourgos |
124 |
5 |
50 |
Nayer |
125 |
6 |
50 |
Mikkilineni |
126 |
7 |
执行计划:
-
Execution Plan
-
----------------------------------------------------------
-
Plan hash value: 1919783947
-
-
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 107 | 1605 | 4 (25)| 00:00:01 |
-
| 1 | WINDOW SORT | | 107 | 1605 | 4 (25)| 00:00:01 |
-
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
-
--------------------------------------------------------------------------------
-
-
-
Statistics
-
----------------------------------------------------------
-
1 recursive calls
-
0 db block gets
-
7 consistent gets
-
0 physical reads
-
0 redo size
-
3534 bytes sent via SQL*Net to client
-
496 bytes received via SQL*Net from client
-
9 SQL*Net roundtrips to/from client
-
1 sorts (memory)
-
0 sorts (disk)
-
107 rows processed
row_number() over(partition by department_id order by employee_id)
和普通函数不同的是,分析函数后面有一个over 关键字。或者说是条件函数?
Partition by 表示依据什么分类,order by地球人都知道(在这几个函数中是必须的)。
从执行计划来看,一个全表扫描,然后一个WINDOW SORT(至于为什么叫WINDOW SORT以后解释)。
统计信息:一个递归调用、七个一致性读、一个内存排序。
关于执行计划不多说,以后有时间看能不能专门写个系列的文章。