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

Oracle分析函數的使用一

2013-11-13 16:16:12  來源: 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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.