oracle分析函數(shù)Rank, Dense_rank, row_number

分析函數(shù)2(Rank, Dense_rank, row_number)

?

目錄
===============================================
1.使用rownum為記錄排名
2.使用分析函數(shù)來(lái)為記錄排名
3.使用分析函數(shù)為記錄進(jìn)行分組排名

一、使用rownum為記錄排名：

在前面一篇《Oracle開(kāi)發(fā)專題之：分析函數(shù)》，我們認(rèn)識(shí)了分析函數(shù)的基本應(yīng)用，現(xiàn)在我們?cè)賮?lái)考慮下面幾個(gè)問(wèn)題：

①對(duì)所有客戶按訂單總額進(jìn)行排名
②按區(qū)域和客戶訂單總額進(jìn)行排名
③找出訂單總額排名前13位的客戶
④找出訂單總額最高、最低的客戶
⑤找出訂單總額排名前25%的客戶

按照前面第一篇文章的思路，我們只能做到對(duì)各個(gè)分組的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，如果需要排名的話那么只需要簡(jiǎn)單地加上rownum不就行了嗎？事實(shí)情況是否如此想象般簡(jiǎn)單，我們來(lái)實(shí)踐一下。

【1】測(cè)試環(huán)境：

SQL>?desc?user_order;
?Name??????????????????????????????????????Null?????Type
?-----------------------------------------?--------?----------------------------
?REGION_ID??????????????????????????????????????????NUMBER(2)
?CUSTOMER_ID??????????????????????????????????NUMBER(2)
?CUSTOMER_SALES??????????????????????????NUMBER

【2】測(cè)試數(shù)據(jù)：

SQL>?select?*?from?user_order?order?by?customer_sales;

?REGION_ID?CUSTOMER_ID?CUSTOMER_SALES
----------?-----------?--------------
?????????5???????????1????????????? 151162
????????10??????????29????????? ?? 903383
?????????6???????????7????????????? 971585
????????10??????????28??????????? 986964
?????????9??????????21???????????1020541
?????????9??????????22?????????? 1036146
?????????8??????????16?????????? 1068467
?????????6???????????8??????????? 1141638
?????????5???????????3??????????? 1161286
?????????5???????????5??????????? 1169926
?????????8??????????19?????????? 1174421
?????????7??????????12?????????? 1182275
?????????7??????????11?????????? 1190421
?????????6??????????10?????????? 1196748
?????????6???????????9??????????? 1208959
????????10??????????30??????????1216858
?????????5???????????? 2??????????????? 1224992
???????????9???????????? 24????????????? 1224992
???????????9???????????? 23????????????? 1224992
?????????? 8??????????18?????????? 1253840
?????????7??????????15?????????? 1255591
?????????7??????????13?????????? 1310434
????????10??????????27????????? 1322747
?????????8??????????20?????????? 1413722
?????????6???????????6??????????? 1788836
????????10??????????26????????? 1808949
?????????5???????????4??????????? 1878275
?????????7??????????14?????????? 1929774
?????????8??????????17?????????? 1944281
?????????9??????????25?????????? 2232703

30?rows?selected.

注意這里有3條記錄的訂單總額是一樣的。假如我們現(xiàn)在需要篩選排名前12位的客戶，如果使用rownum會(huì)有什么樣的后果呢？

SQL>?select?rownum,?t.*
??2????from?(select?*?
??3????????????from?user_order
??4???????????order?by?customer_sales?desc)?t
??5???where?rownum?<=?12
??6???order?by?customer_sales?desc;

????ROWNUM??REGION_ID?CUSTOMER_ID?CUSTOMER_SALES
----------?----------?-----------?--------------
?????????1??????????9???????????????? 25????????2232703
?????????2??????????8???????????????? 17????????1944281
?????????3??????????7?????????????????14????????1929774
?????????4??????????5???????????????????4????????1878275
?????????5?????????10????????????????26????????1808949
?????????6??????????6?????????????????? 6????????1788836
?????????7??????????8???????????????? 20????????1413722
?????????8?????????10??????????????? 27????????1322747
?????????9??????????7??????????????? 13????????1310434
????????10??????????7?????????????? 15????????1255591
????????11??????????8?????????????? 18????????1253840
????????? 12???????????? 5???????????????????? 2????????? 1224992

12?rows?selected.

很明顯假如只是簡(jiǎn)單地按rownum進(jìn)行排序的話，我們漏掉了另外兩條記錄(參考上面的結(jié)果)。

二、使用分析函數(shù)來(lái)為記錄排名：

針對(duì)上面的情況，Oracle從8i開(kāi)始就提供了3個(gè)分析函數(shù)：rand，dense_rank，row_number來(lái)解決諸如此類的問(wèn)題，下面我們來(lái)看看這3個(gè)分析函數(shù)的作用以及彼此之間的區(qū)別：

Rank，Dense_rank，Row_number函數(shù)為每條記錄產(chǎn)生一個(gè)從1開(kāi)始至N的自然數(shù)，N的值可能小于等于記錄的總數(shù)。這3個(gè)函數(shù)的唯一區(qū)別在于當(dāng)碰到相同數(shù)據(jù)時(shí)的排名策略。

①ROW_NUMBER：

Row_number函數(shù)返回一個(gè)唯一的值，當(dāng)碰到相同數(shù)據(jù)時(shí)，排名按照記錄集中記錄的順序依次遞增。?

②DENSE_RANK：
Dense_rank函數(shù)返回一個(gè)唯一的值，除非當(dāng)碰到相同數(shù)據(jù)時(shí)，此時(shí)所有相同數(shù)據(jù)的排名都是一樣的。?

③RANK：
Rank函數(shù)返回一個(gè)唯一的值，除非遇到相同的數(shù)據(jù)時(shí)，此時(shí)所有相同數(shù)據(jù)的排名是一樣的，同時(shí)會(huì)在最后一條相同記錄和下一條不同記錄的排名之間空出排名。

這樣的介紹有點(diǎn)難懂，我們還是通過(guò)實(shí)例來(lái)說(shuō)明吧，下面的例子演示了3個(gè)不同函數(shù)在遇到相同數(shù)據(jù)時(shí)不同排名策略：

SQL>?select?region_id,?customer_id,?sum(customer_sales)?total,
??2?????????rank()?over(order?by?sum(customer_sales)?desc)?rank,
??3?????????dense_rank()?over(order?by?sum(customer_sales)?desc)?dense_rank,
??4?????????row_number()?over(order?by?sum(customer_sales)?desc)?row_number
??5????from?user_order
??6???group?by?region_id,?customer_id;

?REGION_ID?CUSTOMER_ID??????TOTAL???????RANK?DENSE_RANK?ROW_NUMBER
----------?-----------?----------?----------?----------?----------
? ? ????????
?????????8??????????18????????????????1253840?????????11?????????11?????????11
?????????5???????????2?????????????????1224992?????????12?????????12?????????12
?????????9??????????23????????????????1224992?????????12?????????12?????????13
?????????9??????????24????????????????1224992?????????12?????????12?????????14
????????10??????????30???????????????1216858?????????15?????????? 13??????????? 15
? ?

30?rows?selected.

請(qǐng)注意上面的綠色高亮部分，這里生動(dòng)的演示了3種不同的排名策略：

①對(duì)于第一條相同的記錄，3種函數(shù)的排名都是一樣的：12

②當(dāng)出現(xiàn)第二條相同的記錄時(shí)，Rank和Dense_rank依然給出同樣的排名12；而row_number則順延遞增為13，依次類推至第三條相同的記錄

③當(dāng)排名進(jìn)行到下一條不同的記錄時(shí)，可以看到Rank函數(shù)在12和15之間空出了13,14的排名，因?yàn)檫@2個(gè)排名實(shí)際上已經(jīng)被第二、三條相同的記錄占了。而Dense_rank則順序遞增。row_number函數(shù)也是順序遞增

比較上面3種不同的策略，我們?cè)谶x擇的時(shí)候就要根據(jù)客戶的需求來(lái)定奪了：

①假如客戶就只需要指定數(shù)目的記錄，那么采用row_number是最簡(jiǎn)單的，但有漏掉的記錄的危險(xiǎn)

②假如客戶需要所有達(dá)到排名水平的記錄，那么采用rank或dense_rank是不錯(cuò)的選擇。至于選擇哪一種則看客戶的需要，選擇dense_rank或得到最大的記錄

三、使用分析函數(shù)為記錄進(jìn)行分組排名：

上面的排名是按訂單總額來(lái)進(jìn)行排列的，現(xiàn)在跟進(jìn)一步：假如是為各個(gè)地區(qū)的訂單總額進(jìn)行排名呢？這意味著又多了一次分組操作：對(duì)記錄按地區(qū)分組然后進(jìn)行排名。幸虧Oracle也提供了這樣的支持，我們所要做的僅僅是在over函數(shù)中order by的前面增加一個(gè)分組子句：partition by region_id。

SQL>?select?region_id,?customer_id,?
???????????????sum(customer_sales)?total,
??2?????????rank()?over(partition?by?region_id
????????????????????????order?by?sum(customer_sales)?desc)?rank,
??3?????????dense_rank()?over(partition?by?region_id
????????????????????????order?by?sum(customer_sales)?desc)?dense_rank,
??4?????????row_number()?over(partition?by?region_id
????????????????????????order?by?sum(customer_sales)?desc)?row_number

??5????from?user_order
??6???group?by?region_id,?customer_id;

?REGION_ID?CUSTOMER_ID??????TOTAL???????RANK?DENSE_RANK?ROW_NUMBER
----------?-----------?----------?----------?----------?----------
?????????5???????????4????????????????1878275??????????1??????????1??????????1
?????????5???????????2??????????????? 1224992??????????2??????????2??????????2
?????????5???????????5??????????????? 1169926??????????3??????????3??????????3
?????????6???????????6??????????????? 1788836??????????1??????????1??????????1
?????????6???????????9??????????????? 1208959??????????2??????????2??????????2
?????????6??????????10????????????? ?1196748??????????3??????????3??????????3???????
?

30?rows?selected.

現(xiàn)在我們看到的排名將是基于各個(gè)地區(qū)的，而非所有區(qū)域的了！Partition by 子句在排列函數(shù)中的作用是將一個(gè)結(jié)果集劃分成幾個(gè)部分，這樣排列函數(shù)就能夠應(yīng)用于這各個(gè)子集。

前面我們提到的5個(gè)問(wèn)題已經(jīng)解決了2個(gè)了(第1,2)，剩下的3個(gè)問(wèn)題(Top/Bottom N，First/Last, NTile)會(huì)在下一篇講解。

?原文地址：http://www.cnblogs.com/wuyisky/archive/2010/02/24/oracle_rank.html

轉(zhuǎn)載于:https://www.cnblogs.com/autumnlj/p/5815297.html

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎(jiǎng)勵(lì)來(lái)咯，堅(jiān)持創(chuàng)作打卡瓜分現(xiàn)金大獎(jiǎng)

總結(jié)

以上是生活随笔為你收集整理的[转]oracle分析函数Rank, Dense_rank, row_number的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。