Oracle分析函數(shù)原理及應(yīng)用

1、分析函數(shù)(OVER)1分析函數(shù)2(Rank, Dense_rank, row_number)6分析函數(shù)3(Top/Bottom N、First/Last、NTile)10窗口函數(shù)14報(bào)表函數(shù)20分析函數(shù)總結(jié)2226個(gè)分析函數(shù)24PLSQL開發(fā)筆記和小結(jié)28分析函數(shù)簡述60分析函數(shù)(OVER) 目錄：=1.Oracle分析函數(shù)簡介2. Oracle分析函數(shù)簡單實(shí)例3.分析函數(shù)OVER解析一、Oracle分析函數(shù)簡介：在日常的生產(chǎn)環(huán)境中，我們接觸得比較多的是OLTP系統(tǒng)(即Online Transaction Process)，這些系統(tǒng)的特點(diǎn)是具備實(shí)時(shí)要求，或者至少說對(duì)響應(yīng)的時(shí)間多長有一定的要求；

2、其次這些系統(tǒng)的業(yè)務(wù)邏輯一般比較復(fù)雜，可能需要經(jīng)過多次的運(yùn)算。比如我們經(jīng)常接觸到的電子商城。在這些系統(tǒng)之外，還有一種稱之為OLAP的系統(tǒng)(即Online Aanalyse Process)，這些系統(tǒng)一般用于系統(tǒng)決策使用。通常和數(shù)據(jù)倉庫、數(shù)據(jù)分析、數(shù)據(jù)挖掘等概念聯(lián)系在一起。這些系統(tǒng)的特點(diǎn)是數(shù)據(jù)量大，對(duì)實(shí)時(shí)響應(yīng)的要求不高或者根本不關(guān)注這方面的要求，以查詢、統(tǒng)計(jì)操作為主。我們來看看下面的幾個(gè)典型例子：查找上一年度各個(gè)銷售區(qū)域排名前10的員工按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶查找上一年度銷售最差的部門所在的區(qū)域查找上一年度銷售最好和最差的產(chǎn)品我們看看上面的幾個(gè)例子就可以感覺到這幾個(gè)

3、查詢和我們?nèi)粘Ｓ龅降牟樵冇行┎煌唧w有：需要對(duì)同樣的數(shù)據(jù)進(jìn)行不同級(jí)別的聚合操作需要在表內(nèi)將多條數(shù)據(jù)和同一條數(shù)據(jù)進(jìn)行多次的比較需要在排序完的結(jié)果集上進(jìn)行額外的過濾操作分析函數(shù)語法:FUNCTION_NAME(<argument>,<argument>.)OVER(<Partition-Clause><Order-by-Clause><Windowing Clause>)例:sum(sal) over (partition by deptno order by ename) new_aliassum就是函數(shù)名(sal)是分析函數(shù)的參數(shù),

4、每個(gè)函數(shù)有03個(gè)參數(shù),參數(shù)可以是表達(dá)式,例如:sum(sal+comm)over 是一個(gè)關(guān)鍵字,用于標(biāo)識(shí)分析函數(shù),否則查詢分析器不能區(qū)別sum()聚集函數(shù)和sum()分析函數(shù)partition by deptno 是可選的分區(qū)子句,如果不存在任何分區(qū)子句,則全部的結(jié)果集可看作一個(gè)單一的大區(qū)order by ename 是可選的order by 子句,有些函數(shù)需要它,有些則不需要.依靠已排序數(shù)據(jù)的那些函數(shù),如:用于訪問結(jié)果集中前一行和后一行的LAG和LEAD,必須使用,其它函數(shù),如AVG,則不需要.在使用了任何排序的開窗函數(shù)時(shí),該子句是強(qiáng)制性的,它指定了在計(jì)算分析函數(shù)時(shí)一組內(nèi)的數(shù)據(jù)是如何排序的.

5、1)FUNCTION子句ORACLE提供了26個(gè)分析函數(shù),按功能分5類分析函數(shù)分類等級(jí)(ranking)函數(shù):用于尋找前N種查詢開窗(windowing)函數(shù):用于計(jì)算不同的累計(jì),如SUM,COUNT,AVG,MIN,MAX等,作用于數(shù)據(jù)的一個(gè)窗口上例:sum(t.sal) over (order by t.deptno,t.ename) running_total,sum(t.sal) over (partition by t.deptno order by t.ename) department_total制表(reporting)函數(shù):與開窗函數(shù)同名,作用于一個(gè)分區(qū)或一組上的所有列例:s

6、um(t.sal) over () running_total2,sum(t.sal) over (partition by t.deptno ) department_total2制表函數(shù)與開窗函數(shù)的關(guān)鍵不同之處在于OVER語句上缺少一個(gè)ORDER BY子句!LAG,LEAD函數(shù):這類函數(shù)允許在結(jié)果集中向前或向后檢索值,為了避免數(shù)據(jù)的自連接,它們是非常用用的.VAR_POP,VAR_SAMP,STDEV_POPE及線性的衰減函數(shù):計(jì)算任何未排序分區(qū)的統(tǒng)計(jì)值2)PARTITION子句按照表達(dá)式分區(qū)(就是分組),如果省略了分區(qū)子句,則全部的結(jié)果集被看作是一個(gè)單一的組3)ORDER BY子句分析函

7、數(shù)中ORDER BY的存在將添加一個(gè)默認(rèn)的開窗子句,這意味著計(jì)算中所使用的行的集合是當(dāng)前分區(qū)中當(dāng)前行和前面所有行,沒有ORDER BY時(shí),默認(rèn)的窗口是全部的分區(qū) 在Order by 子句后可以添加nulls last,如:order by comm desc nulls last   表示排序時(shí)忽略comm列為空的行.    4)WINDOWING子句用于定義分析函數(shù)將在其上操作的行的集合Windowing子句給出了一個(gè)定義變化或固定的數(shù)據(jù)窗口的方法,分析函數(shù)將對(duì)這些數(shù)據(jù)進(jìn)行操作默認(rèn)的窗口是一個(gè)固定的窗口,僅僅在一

8、組的第一行開始,一直繼續(xù)到當(dāng)前行,要使用窗口,必須使用ORDER BY子句根據(jù)2個(gè)標(biāo)準(zhǔn)可以建立窗口:數(shù)據(jù)值的范圍(RANGES)或與當(dāng)前行的行偏移量.5)Rang窗口Range 5 preceding:將產(chǎn)生一個(gè)滑動(dòng)窗口,他在組中擁有當(dāng)前行以前5行的集合ANGE窗口僅對(duì)NUMBERS和DATES起作用,因?yàn)椴豢赡軓腣ARCHAR2中增加或減去N個(gè)單元另外的限制是ORDER BY中只能有一列,因而范圍實(shí)際上是一維的,不能在N維空間中例:avg(t.sal) over(order by t.hiredate asc range 100 preceding) 統(tǒng)計(jì)前100天平均工資6)Row窗口利用

9、ROW分區(qū),就沒有RANGE分區(qū)那樣的限制了,數(shù)據(jù)可以是任何類型,且ORDER BY 可以包括很多列7)Specifying窗口UNBOUNDED PRECEDING:這個(gè)窗口從當(dāng)前分區(qū)的每一行開始,并結(jié)束于正在處理的當(dāng)前行CURRENT ROW:該窗口從當(dāng)前行開始(并結(jié)束)Numeric Expression PRECEDING:對(duì)該窗口從當(dāng)前行之前的數(shù)字表達(dá)式(Numeric Expression)的行開始,對(duì)RANGE來說,從從行序值小于數(shù)字表達(dá)式的當(dāng)前行的值開始.Numeric Expression FOLLOWING:該窗口在當(dāng)前行Numeric Expression行之后的行終止(

10、或開始),且從行序值大于當(dāng)前行Numeric Expression行的范圍開始(或終止)range between 100 preceding and 100 following:當(dāng)前行100前,當(dāng)前后100后注意:分析函數(shù)允許你對(duì)一個(gè)數(shù)據(jù)集進(jìn)排序和篩選,這是SQL從來不能實(shí)現(xiàn)的.除了最后的Order by子句之外,分析函數(shù)是在查詢中執(zhí)行的最后的操作集,這樣的話,就不能直接在謂詞中使用分析函數(shù),即不能在上面使用where或having子句!二、Oracle分析函數(shù)簡單實(shí)例：下面我們通過一個(gè)實(shí)際的例子：按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶，來看看分析函數(shù)的應(yīng)用?！?】測試環(huán)境

11、：SQL> desc orders_tmp; Name                           Null?    Type - - - CUST_NBR     

12、               NOT NULL NUMBER(5) REGION_ID                   NOT NULL NUMBER(5) SALESPERSON_ID  

13、60;   NOT NULL NUMBER(5) YEAR                              NOT NULL NUMBER(4) MONTH    

14、60;                    NOT NULL NUMBER(2) TOT_ORDERS              NOT NULL NUMBER(7) TOT_SALES  

15、;               NOT NULL NUMBER(11,2)【2】測試數(shù)據(jù)：SQL> select * from orders_tmp;  CUST_NBR  REGION_ID SALESPERSON_ID       YEAR  

16、60;   MONTH TOT_ORDERS  TOT_SALES- - - - - - -        11          7             11

17、60;                      2001          7          2      12204

18、60;        4          5              4                 

19、        2001         10         2      37802         7       

20、0;  6              7                         2001        

21、60; 2          3       3750        10          6            

22、  8                        2001          1          2   

23、;   21691        10          6              7             &#

24、160;          2001          2          3      42624        15   &

25、#160;      7             12                       2000      

26、    5          6         24        12          7        

27、;      9                        2000          6         

28、0;2      50658         1          5              2         &

29、#160;               2000          3          2      44494       &

30、#160; 1          5              1                       

31、0; 2000          9          2      74864         2          5  &#

32、160;           4                         2000          3  &#

33、160;       2      35060         2          5              4  

34、;                       2000          4          4      

35、0;6454         2          5              1               

36、60;         2000         10          4      35580         4     &

37、#160;    5              4                         2000      

38、   12          2      3919013 rows selected.【3】測試語句： SQL> select o.cust_nbr customer,  2         o.region_id region,&

39、#160; 3         sum(o.tot_sales) cust_sales,  4         sum(sum(o.tot_sales) over(partition by o.region_id) region_sales  5    from ord

40、ers_tmp o  6   where o.year = 2001  7   group by o.region_id, o.cust_nbr;  CUSTOMER     REGION CUST_SALES REGION_SALES- - - -     

41、    4              5      37802        37802         7       

42、0;      6       3750         68065        10             6      64315&

43、#160;       68065        11             7      12204        12204三、分析函數(shù)OVER解析：請(qǐng)注意上面的綠色高亮部分，grou

44、p by的意圖很明顯：將數(shù)據(jù)按區(qū)域ID，客戶進(jìn)行分組，那么Over這一部分有什么用呢？假如我們只需要統(tǒng)計(jì)每個(gè)區(qū)域每個(gè)客戶的訂單總額，那么我們只需要group by o.region_id,o.cust_nbr就夠了。但我們還想在每一行顯示該客戶所在區(qū)域的訂單總額，這一點(diǎn)和前面的不同：需要在前面分組的基礎(chǔ)上按區(qū)域累加。很顯然group by和sum是無法做到這一點(diǎn)的(因?yàn)榫奂僮鞯募?jí)別不一樣，前者是對(duì)一個(gè)客戶，后者是對(duì)一批客戶)。這就是over函數(shù)的作用了！它的作用是告訴SQL引擎：按區(qū)域?qū)?shù)據(jù)進(jìn)行分區(qū)，然后累積每個(gè)區(qū)域每個(gè)客戶的訂單總額(sum(sum(o.tot_sales)?，F(xiàn)在我們已經(jīng)知

45、道2001年度每個(gè)客戶及其對(duì)應(yīng)區(qū)域的訂單總額，那么下面就是篩選那些個(gè)人訂單總額占到區(qū)域訂單總額20%以上的大客戶了SQL> select *  2    from (select o.cust_nbr customer,  3                 o.region_id 

46、region,  4                 sum(o.tot_sales) cust_sales,  5                 sum(sum(o.tot_sales) over

47、(partition by o.region_id) region_sales  6            from orders_tmp o  7           where o.year = 2001  8 &#

48、160;         group by o.region_id, o.cust_nbr) all_sales  9   where all_sales.cust_sales > all_sales.region_sales * 0.2;  CUSTOMER     REGION 

49、CUST_SALES REGION_SALES- - - -         4          5      37802        37802      

50、0; 10          6      64315        68065        11          7    

51、  12204        12204SQL> 現(xiàn)在我們已經(jīng)知道這些大客戶是誰了！哦，不過這還不夠，如果我們想要知道每個(gè)大客戶所占的訂單比例呢？看看下面的SQL語句，只需要一個(gè)簡單的Round函數(shù)就搞定了。 SQL> select all_sales.*,  2         100 * round(cust_sales&

52、#160;/ region_sales, 2) | '%' Percent  3    from (select o.cust_nbr customer,  4                 o.region_id region, 

53、 5                 sum(o.tot_sales) cust_sales,  6                 sum(sum(o.tot_sales) over(partition

54、60;by o.region_id) region_sales  7            from orders_tmp o  8           where o.year = 2001  9   

55、60;       group by o.region_id, o.cust_nbr) all_sales 10   where all_sales.cust_sales > all_sales.region_sales * 0.2;  CUSTOMER     REGION CUST_SALES RE

56、GION_SALES PERCENT- - - - -         4            5                  37802  

57、      37802    100%        10           6                  64315  

58、0;     68065      94%        11           7                  12204 

59、60;      12204    100%SQL> 總結(jié)：Over函數(shù)指明在那些字段上做分析，其內(nèi)跟Partition by表示對(duì)數(shù)據(jù)進(jìn)行分組。注意Partition by可以有多個(gè)字段。Over函數(shù)可以和其它聚集函數(shù)、分析函數(shù)搭配，起到不同的作用。例如這里的SUM，還有諸如Rank，Dense_rank等。分析函數(shù)2(Rank, Dense_rank, row_number) 目錄=1.使用rownum為記錄排名2.使用分析函數(shù)來為記錄排名3.使用分析函數(shù)為記錄進(jìn)行分組排名一、使用ro

60、wnum為記錄排名：在前面一篇Oracle開發(fā)專題之：分析函數(shù)，我們認(rèn)識(shí)了分析函數(shù)的基本應(yīng)用，現(xiàn)在我們?cè)賮砜紤]下面幾個(gè)問題：對(duì)所有客戶按訂單總額進(jìn)行排名按區(qū)域和客戶訂單總額進(jìn)行排名找出訂單總額排名前13位的客戶找出訂單總額最高、最低的客戶找出訂單總額排名前25%的客戶按照前面第一篇文章的思路，我們只能做到對(duì)各個(gè)分組的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，如果需要排名的話那么只需要簡單地加上rownum不就行了嗎？事實(shí)情況是否如此想象般簡單，我們來實(shí)踐一下?！?】測試環(huán)境：SQL> desc user_order; Name     

61、                                 Null?    Type - - - REGION_ID     

62、60;                                    NUMBER(2) CUSTOMER_ID         &

63、#160;                        NUMBER(2) CUSTOMER_SALES                    &#

64、160;     NUMBER【2】測試數(shù)據(jù)：SQL> select * from user_order order by customer_sales; REGION_ID CUSTOMER_ID CUSTOMER_SALES- - -         5      &#

65、160;    1              151162        10          29             903383

66、         6           7              971585        10       &

67、#160;  28            986964         9          21           1020541   

68、      9          22           1036146         8          16  

69、0;        1068467         6           8            1141638        

70、0;5           3            1161286         5           5     

71、0;      1169926         8          19           1174421         7   &#

72、160;      12           1182275         7          11           1190421 

73、;        6          10           1196748         6           

74、;9            1208959        10          30          1216858       

75、60; 5             2                1224992           9        

76、60;    24              1224992           9             23        

77、      1224992           8          18           1253840         7  

78、60;       15           1255591         7          13           1310434

79、        10          27          1322747         8          20 

80、60;         1413722         6           6            1788836       

81、60;10          26          1808949         5           4        &

82、#160;   1878275         7          14           1929774         8      

83、;    17           1944281         9          25           223270330 rows se

84、lected.注意這里有3條記錄的訂單總額是一樣的。假如我們現(xiàn)在需要篩選排名前12位的客戶，如果使用rownum會(huì)有什么樣的后果呢？ SQL> select rownum, t.*  2    from (select *   3            from user_order  4 &

85、#160;         order by customer_sales desc) t  5   where rownum <= 12  6   order by customer_sales desc;    ROWNUM  REGION

86、_ID CUSTOMER_ID CUSTOMER_SALES- - - -         1          9                 25    

87、;    2232703         2          8                 17        19442

88、81         3          7                 14        1929774    

89、;     4          5                   4        1878275      &

90、#160;  5         10                26        1808949         6   

91、       6                   6        1788836         7      &

92、#160;   8                 20        1413722         8         10  

93、;              27        1322747         9          7        

94、;        13        1310434        10          7               15&

95、#160;       1255591        11          8               18        

96、1253840          12             5                     2      

97、0;   122499212 rows selected.很明顯假如只是簡單地按rownum進(jìn)行排序的話，我們漏掉了另外兩條記錄(參考上面的結(jié)果)。二、使用分析函數(shù)來為記錄排名：針對(duì)上面的情況，Oracle從8i開始就提供了3個(gè)分析函數(shù)：rand，dense_rank，row_number來解決諸如此類的問題，下面我們來看看這3個(gè)分析函數(shù)的作用以及彼此之間的區(qū)別：Rank，Dense_rank，Row_number函數(shù)為每條記錄產(chǎn)生一個(gè)從1開始至N的自然數(shù)，N的值可能小于等于記錄的總數(shù)。這3個(gè)函數(shù)的唯一區(qū)別在于當(dāng)碰到相同數(shù)據(jù)時(shí)的排名策略。ROW_NUMB

98、ER： 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)難懂，我們還是通過實(shí)例來說明吧，下面的例子演示了3個(gè)不同函數(shù)在遇到相同數(shù)據(jù)時(shí)不同排名策略：SQL> select region_id, custom

99、er_id, sum(customer_sales) total,  2         rank() over(order by sum(customer_sales) desc) rank,  3         dense_rank() over(order by sum(

100、customer_sales) desc) dense_rank,  4         row_number() over(order by sum(customer_sales) desc) row_number  5    from user_order  6   group by&

101、#160;region_id, customer_id; REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER- - - - - -             &#

102、160;     8          18                1253840         11       &#

103、160; 11         11         5           2                 1224992&

104、#160;        12         12         12         9          23  

105、0;             1224992         12         12         13       

106、60; 9          24                1224992         12         12  &

107、#160;      14        10          30               1216858        

108、 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，依次類推

109、至第三條相同的記錄當(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ù)客戶的需求來定奪了：假如客戶就只需要指定數(shù)目的記錄，那么采用row_number是最簡單的，但有漏掉的記錄的危險(xiǎn)假如客戶需要所有達(dá)到排名水平的記錄，那么采用rank或dense_rank是不錯(cuò)的選擇。至于選擇哪一種則看客戶的需要，選擇dense_rank或得到最大的記錄三、使用分析函數(shù)為記錄進(jìn)行分組排名：上面的排名是按

110、訂單總額來進(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(cus

111、tomer_sales) total,  2         rank() over(partition by region_id                        order by

112、60;sum(customer_sales) desc) rank,  3         dense_rank() over(partition by region_id                      &#

113、160; order by sum(customer_sales) desc) dense_rank,  4         row_number() over(partition by region_id                 

114、       order by sum(customer_sales) desc) row_number  5    from user_order  6   group by region_id, customer_id; REGION_ID CUSTOMER_ID    &#

115、160; TOTAL       RANK DENSE_RANK ROW_NUMBER- - - - - -         5           4        &

116、#160;       1878275          1          1          1         5 &#

117、160;         2                1224992          2          2   

118、60;      2         5           5                1169926      

119、0;   3          3          3         6           6      

120、;          1788836          1          1          1         

121、6           9                1208959          2          2  

122、        2         6          10               1196748       

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

124、。前面我們提到的5個(gè)問題已經(jīng)解決了2個(gè)了(第1,2)，剩下的3個(gè)問題(Top/Bottom N，F(xiàn)irst/Last, NTile)會(huì)在下一篇講解。分析函數(shù)3(Top/Bottom N、First/Last、NTile) 目錄=1.帶空值的排列2.Top/Bottom N查詢3.First/Last排名查詢4.按層次查詢一、帶空值的排列：在前面Oracle開發(fā)專題之：分析函數(shù)2(Rank、Dense_rank、row_number)一文中，我們已經(jīng)知道了如何為一批記錄進(jìn)行全排列、分組排列。假如被排列的數(shù)據(jù)中含有空值呢？SQL> select region_id,

125、60;customer_id,  2         sum(customer_sales) cust_sales,  3         sum(sum(customer_sales) over(partition by region_id) ran_total,  4   &

126、#160;     rank() over(partition by region_id  5                  order by sum(customer_sales) desc) rank  6    

127、;from user_order  7   group by region_id, customer_id; REGION_ID CUSTOMER_ID CUST_SALES  RAN_TOTAL       RANK- - - - -         10&

128、#160;         31                    6238901          1        10 

129、         26    1808949    6238901          2        10          27 

130、60;  1322747    6238901          3        10          30    1216858    6238901  

131、        4        10          28     986964    6238901          5  

132、;      10          29     903383    6238901          6我們看到這里有一條記錄的CUST_TOTAL字段值為NULL，但居然排在第一名了！顯然這不符合情理。所以我們重新調(diào)整完善一下我們的排名策略，看

133、看下面的語句：SQL> select region_id, customer_id,  2         sum(customer_sales) cust_total,  3         sum(sum(customer_sales) over(partition by region_id)

134、60;reg_total,  4         rank() over(partition by region_id                         order by su

135、m(customer_sales) desc NULLS LAST) rank  5        from user_order  6       group by region_id, customer_id; REGION_ID CUSTOMER_ID CUST_TOTAL 

136、0;REG_TOTAL       RANK- - - - -        10          26    1808949     6238901      

137、60;    1        10          27    1322747    6238901           2       

138、; 10          30    1216858    6238901           3        10         &

139、#160;28     986964     6238901           4        10          29     903383  

140、60;  6238901           5        10          31     6238901            

141、               6綠色高亮處，NULLS LAST/FIRST告訴Oracle讓空值排名最后后第一。注意是NULLS，不是NULL。二、Top/Bottom N查詢：在日常的工作生產(chǎn)中，我們經(jīng)常碰到這樣的查詢：找出排名前5位的訂單客戶、找出排名前10位的銷售人員等等?，F(xiàn)在這個(gè)對(duì)我們來說已經(jīng)是很簡單的問題了。下面我們用一個(gè)實(shí)際的例子來演示：【1】找出所有訂單總額排名前3的大客戶：SQL> select *SQL>

142、   from (select region_id,SQL>                customer_id,SQL>                sum(customer_sales) cust_t

143、otal,SQL>                rank() over(order by sum(customer_sales) desc NULLS LAST) rankSQL>           from user_orderSQ

144、L>          group by region_id, customer_id)SQL>  where rank <= 3; REGION_ID CUSTOMER_ID CUST_TOTAL       RANK- - - -   &

145、#160;     9          25    2232703          1         8         &

146、#160;17    1944281          2         7          14    1929774         

147、; 3SQL> 【2】找出每個(gè)區(qū)域訂單總額排名前3的大客戶：SQL> select *  2    from (select region_id,  3                 customer_id,  4   

148、60;             sum(customer_sales) cust_total,  5                 sum(sum(customer_sales) over(partition by region_id)&

149、#160;reg_total,  6                 rank() over(partition by region_id                    &

150、#160;           order by sum(customer_sales) desc NULLS LAST) rank  7            from user_order  8      

151、     group by region_id, customer_id)  9   where rank <= 3; REGION_ID CUSTOMER_ID CUST_TOTAL  REG_TOTAL       RANK- - - - -  &#

152、160;      5           4    1878275    5585641          1         5  &#

153、160;        2    1224992    5585641          2         5           5    1169926    5585641          3         6           6    1788836