Oracle分析函數的使用一

　　分析函數是oracle引入的一個全新的概念為我們分析數據提供了一種簡單高效的處理方式在分析函數出現以前我們必須使用自聯查詢子查詢或者內聯視圖甚至復雜的存儲過程實現的語句現在只要一條簡單的sql語句就可以實現了而且在執行效率方面也有相當大的提高下面我將針對分析函數做一些具體的說明

　　 

　　今天我主要給大家介紹一下以下幾個函數的使用方法

　　 

　　  自動匯總函數rollupcube

　　 

　　  rank 函數 rankdense_rankrow_number

　　 

　　        laglead函數

　　 

　　        sumavg的移動增加移動平均數

　　 

　　        ratio_to_report報表處理函數

　　 

　　        firstlast取基數的分析函數

　　 

　　基礎數據

　　 

　　Code:        [Copy to clipboard]

　　:: SQL> select * from t;

　　 

　　BILL_MONTH      AREA_CODE  NET_TYPE       LOCAL_FARE

　　

　　                 G             

　　                 J             

　　                 G             

　　                 J             

　　                 G             

　　                 J             

　　                 G             

　　                 J             

　　                 G            

　　                 J            

　　                 G             

　　                 J             

　　                 G             

　　                 J             

　　                 G             

　　                 J             

　　                 G             

　　                 J             

　　                 G            

　　                 J            

　　                 G             

　　                 J             

　　                 G             

　　                 J             

　　                 G             

　　                 J              

　　                 G             

　　                 J             

　　                 G            

　　                 J            

　　                 G             

　　                 J             

　　                 G             

　　                 J             

　　                 G             

　　                 J             

　　                 G             

　　 

　　BILL_MONTH      AREA_CODE  NET_TYPE       LOCAL_FARE

　　

　　                 J             

　　                 G            

　　                 J            

　　 

　　 rows selected

　　 

　　Elapsed: ::

　　 

　　 使用rollup函數的介紹

　　 

　　Quote:

　　

　　下面是直接使用普通sql語句求出各地區的匯總數據的例子

　　:: SQL> set autot on

　　:: SQL> select area_codesum(local_fare) local_fare

　　::     from t

　　::     group by area_code

　　::     union all

　　::     select 合計 area_codesum(local_fare) local_fare

　　::     from t

　　::     /

　　 

　　AREA_CODE      LOCAL_FARE

　　

　　         

　　         

　　         

　　         

　　        

　　合計        

　　 

　　 rows selected

　　 

　　Elapsed: ::

　　 

　　Execution Plan

　　

　　      SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=

　　)

　　 

　　       UNIONALL

　　         SORT (GROUP BY) (Cost= Card= Bytes=)

　　           TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=

　　)

　　 

　　         SORT (AGGREGATE)

　　           TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=

　　)

　　 

　　Statistics

　　

　　  recursive calls

　　  db block gets

　　  consistent gets

　　  physical reads

　　  redo size

　　  bytes sent via SQL*Net to client

　　  bytes received via SQL*Net from client

　　  SQL*Net roundtrips to/from client

　　  sorts (memory)

　　  sorts (disk)

　　  rows processed

　　 

　　下面是使用分析函數rollup得出的匯總數據的例子

　　 

　　:: SQL> select nvl(area_code合計) area_codesum(local_fare) local_fare

　　::     from t

　　::     group by rollup(nvl(area_code合計))

　　::     /

　　 

　　AREA_CODE      LOCAL_FARE

　　

　　         

　　         

　　         

　　         

　　        

　　

　　 

　　 rows selected

　　 

　　Elapsed: ::

　　 

　　Execution Plan

　　

　　      SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=

　　)

　　 

　　       SORT (GROUP BY ROLLUP) (Cost= Card= Bytes=)

　　         TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=

　　)

　　 

　　Statistics

　　

　　  recursive calls

　　  db block gets

　　  consistent gets

　　  physical reads

　　  redo size

　　  bytes sent via SQL*Net to client

　　  bytes received via SQL*Net from client

　　  SQL*Net roundtrips to/from client

　　  sorts (memory)

　　  sorts (disk)

　　  rows processed

　　 

　　從上面的例子我們不難看出使用rollup函數系統的sql語句更加簡單耗用的資源更少從個consistent gets降到個consistent gets如果基表很大的話結果就可想而知了

　　 

　　 使用cube函數的介紹

　　 

　　Quote:

　　 

　　為了介紹cube函數我們再來看看另外一個使用rollup的例子

　　 

　　:: SQL> select area_codebill_monthsum(local_fare) local_fare

　　::     from t

　　::     group by rollup(area_codebill_month)

　　::     /

　　 

　　AREA_CODE  BILL_MONTH          LOCAL_FARE

　　

　　                   

　　                   

　　                   

　　                   

　　                         

　　                   

　　                   

　　                   

　　                   

　　                         

　　                   

　　                   

　　                   

　　                   

　　                         

　　                   

　　                   

　　                   

　　                   

　　                         

　　                   

　　                   

　　                   

　　                   

　　                         

　　

　　 

　　 rows selected

　　 

　　Elapsed: ::

　　 

　　系統只是根據rollup的第一個參數area_code對結果集的數據做了匯總處理而沒有對bill_month做匯總分析處理cube函數就是為了這個而設計的

　　 

　　下面讓我們看看使用cube函數的結果

　　 

　　:: SQL> select area_codebill_monthsum(local_fare) local_fare

　　::     from t

　　::     group by cube(area_codebill_month)

　　::     order by area_codebill_month nulls last

　　::     /

　　 

　　AREA_CODE  BILL_MONTH          LOCAL_FARE

　　

　　                      

　　                      

　　                      

　　                      

　　                            

　　                      

　　                      

　　                      

　　                      

　　                            

　　                      

　　                      

　　                      

　　                      

　　                            

　　                      

　　                      

　　                      

　　                      

　　                            

　　                      

　　                      

　　                      

　　                      

　　                           

　　               

　　               

　　               

　　               

　　

　　 

　　 rows selected

　　 

　　Elapsed: ::

　　 

　　可以看到在cube函數的輸出結果比使用rollup多出了幾行統計數據這就是cube函數根據bill_month做的匯總統計結果]

　　 rollup 和 cube函數的再深入

　　 

　　Quote:

　　 

　　從上面的結果中我們很容易發現每個統計數據所對應的行都會出現null我們如何來區分到底是根據那個字段做的匯總呢這時候oracle的grouping函數就粉墨登場了

　　 

　　如果當前的匯總記錄是利用該字段得出的grouping函數就會返回否則返回

　　 

　　  select decode(grouping(area_code)all areato_char(area_code)) area_code

　　         decode(grouping(bill_month)all monthbill_month) bill_month

　　         sum(local_fare) local_fare

　　  from t

　　  group by cube(area_codebill_month)

　　* order by area_codebill_month nulls last

　　:: SQL> /

　　 

　　AREA_CODE  BILL_MONTH          LOCAL_FARE

　　

　　                      

　　                      

　　                      

　　                      

　　       all month            

　　                       

　　                      

　　                      

　　                      

　　       all month            

　　                      

　　                      

　　                      

　　                      

　　       all month            

　　                      

　　                      

　　                      

　　                      

　　       all month            

　　                      

　　                      

　　                      

　　                      

　　       all month           

　　all area                  

　　all area                  

　　all area                  

　　all area                  

　　all area   all month           

　　 

　　 rows selected

　　 

　　Elapsed: ::

　　:: SQL>

　　 

　　可以看到所有的空值現在都根據grouping函數做出了很好的區分這樣利用rollupcube和grouping函數我們做數據統計的時候就可以輕松很多了

From:http://tw.wingwit.com/Article/program/Oracle/201311/18065.html