oracle 分析函式之(rank()/dense_rank()/row_number())

在實際的資料庫開發過程中，經常需要用到不同需求的排序，有時候需要區別並列的排名，有時候需要區分並列排名之後的排名，Oracle為排序提供rank()、dense_rank()、row_number()函式，為每條記錄產生一個從1開始至N的自然數，N的值可能小於等於記錄的總數。這3個函式的唯一區別在於當碰到相同資料時的排名策略。

1、Rank()函式返回一個唯一的值，除非遇到相同的資料時，此時所有相同資料的排名是一樣的，同時會在最後一條相同記錄和下一條不同記錄的排名之間空出排名。
2、Dense_rank()函式返回一個唯一的值，除非當碰到相同資料時，此時所有相同資料的排名都是一樣的，而且不為後面的資料留出排名空間。
3、Row_number()函式返回一個唯一的值，當碰到相同資料時，排名按照記錄集中記錄的順序依次遞增。
下面看例子吧：
建立測試需要的表：

SQL> create table tangyunoracle
2 (
3 cname VARCHAR2(20),
4 months Number,
5 sales Number
6 );

表已建立。
為測試表中插入一些資料：

SQL> INSERT INTO tangyunoracle VALUES('tangyun',1,100);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('tangyun',2,800);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('tangyun',3,800);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('huananzixun',1,100);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('huananzixun',2,810);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('huananzixun',3,790);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('ty',1,100);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('ty',2,880);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('ty',3,790);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('oracle',1,100);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('oracle',2,810);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('oracle',3,890);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('tgyun',1,120);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('tgyun',2,830);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('tgyun',3,850);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('yun',1,110);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('yun',2,840);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('yun',3,850);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('tang',1,120);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('tang',2,830);

已建立 1 行。

SQL> INSERT INTO tangyunoracle VALUES('tang',3,850);

已建立 1 行。

SQL> COMMIT;

提交完成。
對比三個函式對相同順序的排名：

SQL> select cname,rank() over(order by sum(sales) desc) rank,dense_rank() over(order by sum(sales) desc) dense_rank,row_number() over(order by sum(sales) desc)row_number from tangyunoracle group by cname;

CNAME RANK DENSE_RANK ROW_NUMBER

-------------------- ---------- ---------- ----------

oracle 1 1 1

ty 2 2 2

tangyun 3 3 3

huananzixun 3 3 4

yun 5 4 5

tang 5 4 6

tgyun 5 4 7

已選擇7行。

透過這個例子就可以清楚三者之間的區別了。

－－－－－－－－－－－－－－－－End－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－