熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle分析函數詳述

2013-11-13 22:08:13  來源: Oracle 

  一分析函數(OVER)

  目錄

  ===============================================

  Oracle分析函數簡介

   Oracle分析函數簡單實例

  分析函數OVER解析

  一Oracle分析函數簡介

  在日常的生產環境中我們接觸得比較多的是OLTP系統(即Online Transaction Process)這些系統的特點是具備實時要求或者至少說對響應的時間多長有一定的要求其次這些系統的業務邏輯一般比較復雜可能需要經過多次的運算比如我們經常接觸到的電子商城

  在這些系統之外還有一種稱之為OLAP的系統(即Online Aanalyse Process)這些系統一般用於系統決策使用通常和數據倉庫數據分析數據挖掘等概念聯系在一起這些系統的特點是數據量大對實時響應的要求不高或者根本不關注這方面的要求以查詢統計操作為主

  我們來看看下面的幾個典型例子

  ①查找上一年度各個銷售區域排名前的員工

  ②按區域查找上一年度訂單總額占區域訂單總額%以上的客戶

  ③查找上一年度銷售最差的部門所在的區域

  ④查找上一年度銷售最好和最差的產品

  我們看看上面的幾個例子就可以感覺到這幾個查詢和我們日常遇到的查詢有些不同具體有

  ①需要對同樣的數據進行不同級別的聚合操作

  ②需要在表內將多條數據和同一條數據進行多次的比較

  ③需要在排序完的結果集上進行額外的過濾操作

  二Oracle分析函數簡單實例

  下面我們通過一個實際的例子按區域查找上一年度訂單總額占區域訂單總額%以上的客戶來看看分析函數的應用

  【】測試環境

  SQL> desc orders_tmp;

  Name                           Null?    Type

  

  CUST_NBR                    NOT NULL NUMBER()

  REGION_ID                   NOT NULL NUMBER()

  SALESPERSON_ID      NOT NULL NUMBER()

  YEAR                              NOT NULL NUMBER()

  MONTH                         NOT NULL NUMBER()

  TOT_ORDERS              NOT NULL NUMBER()

  TOT_SALES                 NOT NULL NUMBER()

  【】測試數據

  SQL> select * from orders_tmp;

  CUST_NBR  REGION_ID SALESPERSON_ID       YEAR      MONTH TOT_ORDERS  TOT_SALES

  

                                                                         

                                                                          

                                                                             

                                                                           

                                                                           

                                                                            

                                                                           

                                                                            

                                                                            

                                                                            

                                                                             

                                                                           

                                                                           

   rows selected

  【】測試語句

  SQL> select ocust_nbr customer

           oregion_id region

           sum(otot_sales) cust_sales

           sum(sum(otot_sales)) over(partition by oregion_id) region_sales

      from orders_tmp o

     where oyear =

     group by oregion_id ocust_nbr;

  CUSTOMER     REGION CUST_SALES REGION_SALES

  

                             

                               

                            

                            

  三分析函數OVER解析

  請注意上面的綠色高亮部分group by的意圖很明顯將數據按區域ID客戶進行分組那麼Over這一部分有什麼用呢?假如我們只需要統計每個區域每個客戶的訂單總額那麼我們只需要 group by oregion_idocust_nbr就夠了但我們還想在每一行顯示該客戶所在區域的訂單總額這一點和前面的不同需要在前面分組的基礎上按區域累加很顯然group by和sum是無法做到這一點的(因為聚集操作的級別不一樣前者是對一個客戶後者是對一批客戶)

  這就是over函數的作用了!它的作用是告訴SQL引擎按區域對數據進行分區然後累積每個區域每個客戶的訂單總額(sum(sum(otot_sales)))

  現在我們已經知道年度每個客戶及其對應區域的訂單總額那麼下面就是篩選那些個人訂單總額占到區域訂單總額%以上的大客戶了

  SQL> select *

      from (select ocust_nbr customer

                   oregion_id region

                   sum(otot_sales) cust_sales

                   sum(sum(otot_sales)) over(partition by oregion_id) region_sales

              from orders_tmp o

             where oyear =

             group by oregion_id ocust_nbr) all_sales

     where all_salescust_sales > all_salesregion_sales * ;

  CUSTOMER     REGION CUST_SALES REGION_SALES

  

                         

                         

                         

  SQL>

  現在我們已經知道這些大客戶是誰了!哦不過這還不夠如果我們想要知道每個大客戶所占的訂單比例呢?看看下面的SQL語句只需要一個簡單的Round函數就搞定了 SQL> select all_sales*

           * round(cust_sales / region_sales ) || % Percent

      from (select ocust_nbr customer

                   oregion_id region

                   sum(otot_sales) cust_sales

                   sum(sum(otot_sales)) over(partition by oregion_id) region_sales

              from orders_tmp o

             where oyear =

             group by oregion_id ocust_nbr) all_sales

     where all_salescust_sales > all_salesregion_sales * ;

  CUSTOMER     REGION CUST_SALES REGION_SALES PERCENT

  

                                            %

                                             %

                                           %

  SQL>

  總結

  ①Over函數指明在那些字段上做分析其內跟Partition by表示對數據進行分組注意Partition by可以有多個字段

  ②Over函數可以和其它聚集函數分析函數搭配起到不同的作用例如這裡的SUM還有諸如RankDense_rank等

  一分析函數(rank\dense_rank\row_number)

  目錄

  ===============================================

  使用rownum為記錄排名

  使用分析函數來為記錄排名

  使用分析函數為記錄進行分組排名

  一使用rownum為記錄排名

  在前面一篇《Oracle開發專題之分析函數》我們認識了分析函數的基本應用現在我們再來考慮下面幾個問題

  ①對所有客戶按訂單總額進行排名

  ②按區域和客戶訂單總額進行排名

  ③找出訂單總額排名前位的客戶

  ④找出訂單總額最高最低的客戶

  ⑤找出訂單總額排名前%的客戶

  按照前面第一篇文章的思路我們只能做到對各個分組的數據進行統計如果需要排名的話那麼只需要簡單地加上rownum不就行了嗎?事實情況是否如此想象般簡單我們來實踐一下

  【】測試環境

  SQL> desc user_order;

  Name                                      Null?    Type

  

  REGION_ID                                          NUMBER()

  CUSTOMER_ID                                  NUMBER()

  CUSTOMER_SALES                          NUMBER

  【】測試數據

  SQL> select * from user_order order by customer_sales;

  REGION_ID CUSTOMER_ID CUSTOMER_SALES

  

                          

                        

                          

                       

                      

                      

                      

                        

                        

                        

                      

                      

                      

                      

                        

                     

                              

                            

                            

                      

                      

                      

                     

                      

                        

                     

                        

                      

                      

                      

   rows selected

  注意這裡有條記錄的訂單總額是一樣的假如我們現在需要篩選排名前位的客戶如果使用rownum會有什麼樣的後果呢? SQL> select rownum t*

      from (select *

              from user_order

             order by customer_sales desc) t

     where rownum <=

     order by customer_sales desc;

  ROWNUM  REGION_ID CUSTOMER_ID CUSTOMER_SALES

  

                                    

                                    

                                    

                                      

                                  

                                      

                                    

                                  

                                   

                                  

                                  

                                             

   rows selected

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

  二使用分析函數來為記錄排名

  針對上面的情況Oracle從i開始就提供了個分析函數randdense_rankrow_number來解決諸如此類的問題下面我們來看看這個分析函數的作用以及彼此之間的區別

  RankDense_rankRow_number函數為每條記錄產生一個從開始至N的自然數N的值可能小於等於記錄的總數個函數的唯一區別在於當碰到相同數據時的排名策略

  ①ROW_NUMBER

  Row_number函數返回一個唯一的值當碰到相同數據時排名按照記錄集中記錄的順序依次遞增

  ②DENSE_RANK

  Dense_rank函數返回一個唯一的值除非當碰到相同數據時此時所有相同數據的排名都是一樣的

  ③RANK

  Rank函數返回一個唯一的值除非遇到相同的數據時此時所有相同數據的排名是一樣的同時會在最後一條相同記錄和下一條不同記錄的排名之間空出排名

  這樣的介紹有點難懂我們還是通過實例來說明吧下面的例子演示了個不同函數在遇到相同數據時不同排名策略

  SQL> select region_id customer_id sum(customer_sales) total

           rank() over(order by sum(customer_sales) desc) rank

           dense_rank() over(order by sum(customer_sales) desc) dense_rank

           row_number() over(order by sum(customer_sales) desc) row_number

      from user_order

     group by region_id customer_id;

  REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER

  

                                                      

                                                        

                                                      

                                                      

                                                          

   rows selected

  請注意上面的綠色高亮部分這裡生動的演示了種不同的排名策略

  ①對於第一條相同的記錄種函數的排名都是一樣的

  ②當出現第二條相同的記錄時Rank和Dense_rank依然給出同樣的排名而row_number則順延遞增為依次類推至第三條相同的記錄

  ③當排名進行到下一條不同的記錄時可以看到Rank函數在之間空出了的排名因為這個排名實際上已經被第二三條相同的記錄占了而Dense_rank則順序遞增row_number函數也是順序遞增

  比較上面種不同的策略我們在選擇的時候就要根據客戶的需求來定奪了

  ①假如客戶就只需要指定數目的記錄那麼采用row_number是最簡單的但有漏掉的記錄的危險

  ②假如客戶需要所有達到排名水平的記錄那麼采用rank或dense_rank是不錯的選擇至於選擇哪一種則看客戶的需要選擇dense_rank或得到最大的記錄

  三使用分析函數為記錄進行分組排名

  上面的排名是按訂單總額來進行排列的現在跟進一步假如是為各個地區的訂單總額進行排名呢?這意味著又多了一次分組操作對記錄按地區分組然後進行排名幸虧Oracle也提供了這樣的支持我們所要做的僅僅是在over函數中order by的前面增加一個分組子句partition by region_id

  SQL> select region_id customer_id

  sum(customer_sales) total

           rank() over(partition by region_id

  order by sum(customer_sales) desc) rank

           dense_rank() over(partition by region_id

  order by sum(customer_sales) desc) dense_rank

           row_number() over(partition by region_id

  order by sum(customer_sales) desc) row_number

      from user_order

     group by region_id customer_id;

  REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER

  

                                                          

                                                          

                                                          

                                                          

                                                          

                                                        

   rows selected

  現在我們看到的排名將是基於各個地區的而非所有區域的了!Partition by 子句在排列函數中的作用是將一個結果集劃分成幾個部分這樣排列函數就能夠應用於這各個子集

  三分析函數(top\bottom nfirst\lastntile)

  目錄

  ===============================================

  帶空值的排列

  Top/Bottom N查詢

  First/Last排名查詢

  按層次查詢

  一帶空值的排列

  在前面《Oracle開發專題之分析函數(RankDense_rankrow_number)》一文中我們已經知道了如何為一批記錄進行全排列分組排列假如被排列的數據中含有空值呢?

  SQL> select region_id customer_id

           sum(customer_sales) cust_sales

           sum(sum(customer_sales)) over(partition by region_id) ran_total

           rank() over(partition by region_id

                    order by sum(customer_sales) desc) rank

      from user_order

     group by region_id customer_id;

  REGION_ID CUSTOMER_ID CUST_SALES  RAN_TOTAL       RANK

  

                                         

                             

                             

                             

                              

                              

  我們看到這裡有一條記錄的CUST_TOTAL字段值為NULL但居然排在第一名了!顯然這不符合情理所以我們重新調整完善一下我們的排名策略看看下面的語句

  SQL> select region_id customer_id

           sum(customer_sales) cust_total

           sum(sum(customer_sales)) over(partition by region_id) reg_total

           rank() over(partition by region_id

  order by sum(customer_sales) desc NULLS LAST) rank

          from user_order

         group by region_id customer_id;

  REGION_ID CUSTOMER_ID CUST_TOTAL  REG_TOTAL       RANK

  

                               

                              

                              

                                

                                

                                           

  綠色高亮處NULLS LAST/FIRST告訴Oracle讓空值排名最後後第一

  注意是NULLS不是NULL

  二Top/Bottom N查詢

  在日常的工作生產中我們經常碰到這樣的查詢找出排名前位的訂單客戶找出排名前位的銷售人員等等現在這個對我們來說已經是很簡單的問題了下面我們用一個實際的例子來演示

  【】找出所有訂單總額排名前的大客戶

  SQL> select *

  SQL>   from (select region_id

  SQL>                customer_id

  SQL>                sum(customer_sales) cust_total

  SQL>                rank() over(order by sum(customer_sales) desc NULLS LAST) rank

  SQL>           from user_order

  SQL>          group by region_id customer_id)

  SQL>  where rank <= ;

  REGION_ID CUSTOMER_ID CUST_TOTAL       RANK

  

                         

                         

                         

  SQL>

  【】找出每個區域訂單總額排名前的大客戶

  SQL> select *

      from (select region_id

                   customer_id

                   sum(customer_sales) cust_total

                   sum(sum(customer_sales)) over(partition by region_id) reg_total

                   rank() over(partition by region_id

  order by sum(customer_sales) desc NULLS LAST) rank

              from user_order

             group by region_id customer_id)

     where rank <= ;

  REGION_ID CUSTOMER_ID CUST_TOTAL  REG_TOTAL       RANK

  

                              

                              

                              

                              

                              

                             

                             

                             

                             

                             

                             

                             

                             

                             

                             

                             

                             

                             

   rows selected

  三First/Last排名查詢

  想象一下下面的情形找出訂單總額最多最少的客戶按照前面我們學到的知識這個至少需要個查詢第一個查詢按照訂單總額降序排列以期拿到第一名第二個查詢按照訂單總額升序排列以期拿到最後一名是不是很煩?因為Rank函數只告訴我們排名的結果卻無法自動替我們從中篩選結果

  幸好Oracle為我們在排列函數之外提供了兩個額外的函數firstlast函數專門用來解決這種問題還是用實例說話 SQL> select min(customer_id)

           keep (dense_rank first order by sum(customer_sales) desc) first

           min(customer_id)

           keep (dense_rank last order by sum(customer_sales) desc) last

      from user_order

     group by customer_id;

  FIRST       LAST

  

           

  這裡有幾個看起來比較疑惑的地方

  ①為什麼這裡要用min函數

  ②Keep這個東西是干什麼的

  ③fist/last是干什麼的

  ④dense_rank和dense_rank()有什麼不同能換成rank嗎?

  首先解答一下第一個問題min函數的作用是用於當存在多個First/Last情況下保證返回唯一的記錄假如我們去掉會有什麼樣的後果呢? SQL> select keep (dense_rank first order by sum(customer_sales) desc) first

               keep (dense_rank last order by sum(customer_sales) desc) last

      from user_order

     group by customer_id;

  select keep (dense_rank first order by sum(customer_sales) desc) first

  *

  ERROR at line :

  ORA: missing right parenthesis

  接下來看看第個問題keep是干什麼用的?從上面的結果我們已經知道Oracle對排名的結果只保留條數據這就是keep的作用告訴Oracle只保留符合keep條件的記錄

  那麼什麼才是符合條件的記錄呢?這就是第個問題了dense_rank是告訴Oracle排列的策略first/last則告訴最終篩選的條件

  第個問題如果我們把dense_rank換成rank呢? SQL> select min(region_id)

            keep(rank first order by sum(customer_sales) desc) first

           min(region_id)

            keep(rank last order by sum(customer_sales) desc) last

      from user_order

     group by region_id;

  select min(region_id)

  *

  ERROR at line :

  ORA: missing DENSE_RANK

  四按層次查詢

  現在我們已經見識了如何通過Oracle的分析函數來獲取Top/Bottom N第一個最後一個記錄有時我們會收到類似下面這樣的需求找出訂單總額排名前/的客戶

  很熟悉是不?我們馬上會想到第二點中提到的方法可是rank函數只為我們做好了排名並不知道每個排名在總排名中的相對位置這時候就引入了另外一個分析函數NTile下面我們就以上面的需求為例來講解一下

  SQL> select region_id

           customer_id

           ntile() over(order by sum(customer_sales) desc) til

      from user_order

     group by region_id customer_id;

  REGION_ID  CUSTOMER_ID       TILE

  

                     

                      

                     

                        

                      

                        

                      

                        

                      

                        

                        

                      

                        

                     

                        

  Ntil函數為各個記錄在記錄集中的排名計算比例我們看到所有的記錄被分成個等級那麼假如我們只需要前/的記錄則只需要截取TILE的值為的記錄就可以了假如我們需要排名前%的記錄(也就是/)那麼我們只需要設置ntile()就可以了


From:http://tw.wingwit.com/Article/program/Oracle/201311/18316.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.