教为学: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以后解释)。
统计信息:一个递归调用、七个一致性读、一个内存排序。
关于执行计划不多说,以后有时间看能不能专门写个系列的文章。