学步园

教为学：Oracle SQL学习之路（一）：分析函数之排名

前言

本来想对分析函数来个概述的，可是觉得这概念让人头疼，不如先来几个实例直截了当。

三个分析排名函数的对比。

先上SQL：

1. with t as
2. (
3. select rownum*10 cnt from dual connect by rownum<5
4. union all
5. select rownum*40-10 from dual connect by rownum<3
6. )
7. select cnt,
8. row_number() over(order by cnt) rn,
9. rank() over(order by cnt) rk,
10. dense_rank() over(order by cnt) drk
11. from t;

再上结果：

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函数：

返回唯一值，碰到相同的值按照一定的排序，不采用相同的值，一直排列下去。

用法详解：

代码如下：

1. select
2. department_id,
3. last_name,
4. employee_id,
5. row_number() over(partition by department_id order by employee_id) as emp_id
6. from employees
7. ;

执行结果如下（省略一部分）：

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

执行计划：

1. Execution Plan
2. ----------------------------------------------------------
3. Plan hash value: 1919783947
4.  
5. --------------------------------------------------------------------------------
6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
7. --------------------------------------------------------------------------------
8. | 0 | SELECT STATEMENT | | 107 | 1605 | 4 (25)| 00:00:01 |
9. | 1 | WINDOW SORT | | 107 | 1605 | 4 (25)| 00:00:01 |
10. | 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
11. --------------------------------------------------------------------------------
12.  
13.  
14. Statistics
15. ----------------------------------------------------------
16.      1 recursive calls
17.      0 db block gets
18.      7 consistent gets
19.      0 physical reads
20.      0 redo size
21.        3534 bytes sent via SQL*Net to client
22.    496 bytes received via SQL*Net from client
23.      9 SQL*Net roundtrips to/from client
24.      1 sorts (memory)
25.      0 sorts (disk)
26.    107 rows processed

row_number() over(partition by department_id order by employee_id)

和普通函数不同的是，分析函数后面有一个over 关键字。或者说是条件函数？

Partition by 表示依据什么分类，order by地球人都知道（在这几个函数中是必须的）。

从执行计划来看，一个全表扫描，然后一个WINDOW SORT（至于为什么叫WINDOW SORT以后解释）。

统计信息：一个递归调用、七个一致性读、一个内存排序。

关于执行计划不多说，以后有时间看能不能专门写个系列的文章。