分析函數是Oracle816引入的一個全新的概念,為我們分析數據提供了一種簡單高效的處理方式.在分析函數出現以前,我們必須使用自聯查詢,子查詢或者內聯視圖,甚至復雜的存儲過程實現的語句,現在只要一條簡單的sql語句就可以實現了,而且在執行效率方面也有相當大的提高.下面我將針對分析函數做一些具體的說明. 今天我主要給大家介紹一下以下幾個函數的使用方法 1. 自動匯總函數rollup,cube, 2. rank 函數, rank,dense_rank,row_number 3. lag,lead函數 4. sum,avg,的移動增加,移動平均數 5. ratio_to_report報表處理函數 6. first,last取基數的分析函數 基礎數據 Code: [Copy to clipboard]06:34:23 SQL> select * from t; BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE--------------- ---------- ---------- --------------200405 5761 G 7393344.04200405 5761 J 5667089.85200405 5762 G 6315075.96200405 5762 J 6328716.15200405 5763 G 8861742.59200405 5763 J 7788036.32200405 5764 G 6028670.45200405 5764 J 6459121.49200405 5765 G 13156065.77200405 5765 J 11901671.70200406 5761 G 7614587.96200406 5761 J 5704343.05200406 5762 G 6556992.60200406 5762 J 6238068.05200406 5763 G 9130055.46200406 5763 J 7990460.25200406 5764 G 6387706.01200406 5764 J 6907481.66200406 5765 G 13562968.81200406 5765 J 12495492.50200407 5761 G 7987050.65200407 5761 J 5723215.28200407 5762 G 6833096.68200407 5762 J 6391201.44200407 5763 G 9410815.91200407 5763 J 8076677.41200407 5764 G 6456433.23200407 5764 J 6987660.53200407 5765 G 14000101.20200407 5765 J 12301780.20200408 5761 G 8085170.84200408 5761 J 6050611.37200408 5762 G 6854584.22200408 5762 J 6521884.50200408 5763 G 9468707.65200408 5763 J 8460049.43200408 5764 G 6587559.23 BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE--------------- ---------- ---------- --------------200408 5764 J 7342135.86200408 5765 G 14450586.63200408 5765 J 12680052.38 40 rows selected. Elapsed: 00:00:00.00 1. 使用rollup函數的介紹 Quote: 下面是直接使用普通sql語句求出各地區的匯總數據的例子06:41:36 SQL> set autot on06:43:36 SQL> select area_code,sum(local_fare) local_fare06:43:50 2 from t06:43:51 3 group by area_code06:43:57 4 union all06:44:00 5 select '合計' area_code,sum(local_fare) local_fare06:44:06 6 from t06:44:08 7 / AREA_CODE LOCAL_FARE---------- --------------5761 54225413.045762 52039619.605763 69186545.025764 53156768.465765 104548719.19合計 333157065.31 6 rows selected. Elapsed: 00:00:00.03 Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1310 Bytes= 24884) 1 0 UNION-ALL 2 1 SORT (GROUP BY) (Cost=5 Card=1309 Bytes=24871) 3 2 TABLE access (FULL) OF 'T' (Cost=2 Card=1309 Bytes=248 71) 4 1 SORT (AGGREGATE) 5 4 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=170 17) Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 561 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 6 rows PRocessed 下面是使用分析函數rollup得出的匯總數據的例子 06:44:09 SQL> select nvl(area_code,'合計') area_code,sum(local_fare) local_fare06:45:26 2 from t06:45:30 3 group by rollup(nvl(area_code,'合計'))06:45:50 4 / AREA_CODE LOCAL_FARE---------- --------------5761 54225413.045762 52039619.605763 69186545.025764 53156768.465765 104548719.19 333157065.31 6 rows selected. Elapsed: 00:00:00.00 Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1309 Bytes= 24871) 1 0 SORT (GROUP BY ROLLUP) (Cost=5 Card=1309 Bytes=24871) 2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=24871 ) Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 557 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 6 rows processed 從上面的例子我們不難看出使用rollup函數,系統的sql語句更加簡單,耗用的資源更少,從6個consistent gets降到4個consistent gets,假如基表很大的話,結果就可想而知了. 1. 使用cube函數的介紹 Quote: 為了介紹cube函數我們再來看看另外一個使用rollup的例子 06:53:00 SQL> select area_code,bill_month,sum(local_fare) local_fare06:53:37 2 from t06:53:38 3 group by rollup(area_code,bill_month)06:53:49 4 / AREA_CODE BILL_MONTH LOCAL_FARE---------- --------------- --------------5761 200405 13060433.895761 200406 13318931.015761 200407 13710265.935761 200408 14135782.215761 54225413.045762 200405 12643792.115762 200406 12795060.655762 200407 13224298.125762 200408 13376468.725762 52039619.605763 200405 16649778.915763 200406 17120515.715763 200407 17487493.325763 200408 17928757.085763 69186545.025764 200405 12487791.945764 200406 13295187.675764 200407 13444093.765764 200408 13929695.095764 53156768.465765 200405 25057737.475765 200406 26058461.315765 200407 26301881.405765 200408 27130639.015765 104548719.19 333157065.31 26 rows selected. Elapsed: 00:00:00.00 系統只是根據rollup的第一個參數area_code對結果集的數據做了匯總處理,而沒有對bill_month做匯總分析處理,cube函數就是為了這個而設計的. 下面,讓我們看看使用cube函數的結果 06:58:02 SQL> select area_code,bill_month,sum(local_fare) local_fare06:58:30 2 from t06:58:32 3 group by cube(area_code,bill_month)06:58:42 4 order by area_code,bill_month nulls last06:58:57 5 / AREA_CODE BILL_MONTH LOCAL_FARE---------- --------------- --------------5761 200405 13060.435761 200406 13318.935761 200407 13710.275761 200408 14135.785761 54225.415762 200405 12643.795762 200406 12795.065762 200407 13224.305762 200408 13376.475762 52039.625763 200405 16649.785763 200406 17120.525763 200407 17487.495763 200408 17928.765763 69186.545764 200405 12487.795764 200406 13295.195764 200407 13444.095764 200408 13929.695764 53156.775765 200405 25057.745765 200406 26058.465765 200407 26301.885765 200408 27130.645765 104548.72 200405 79899.53 200406 82588.15 200407 84168.03 200408 86501.34 333157.05 30 rows selected. Elapsed: 00:00:00.01 可以看到,在cube函數的輸出結果比使用rollup多出了幾行統計數據.這就是cube函數根據bill_month做的匯總統計結果]
1 rollup 和 cube函數的再深入 Quote: 從上面的結果中我們很輕易發現,每個統計數據所對應的行都會出現null,我們如何來區分到底是根據那個字段做的匯總呢,這時候,oracle的grouping函數就粉墨登場了. 假如當前的匯總記錄是利用該字段得出的,grouping函數就會返回1,否則返回0 1 select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code, 2 decode(grouping(bill_month),1,'all month',bill_month) bill_month, 3 sum(local_fare) local_fare 4 from t 5 group by cube(area_code,bill_month) 6* order by area_code,bill_month nulls last07:07:29 SQL> / AREA_CODE BILL_MONTH LOCAL_FARE---------- --------------- --------------5761 200405 13060.43
5761 200406 13318.935761 200407 13710.275761 200408 14135.785761 all month 54225.415762 200405 12643.795762 200406 12795.065762 200407 13224.305762 200408 13376.475762 all month 52039.625763 200405 16649.785763 200406 17120.525763 200407 17487.49
5763 200408 17928.765763 all month 69186.545764 200405 12487.795764 200406 13295.195764 200407 13444.095764 200408 13929.695764 all month 53156.775765 200405 25057.745765 200406 26058.465765 200407 26301.885765 200408 27130.645765 all month 104548.72
all area 200405 79899.53all area 200406 82588.15all area 200407 84168.03all area 200408 86501.34all area all month 333157.05 30 rows selected. Elapsed: 00:00:00.0107:07:31 SQL> 可以看到,所有的空值現在都根據grouping函數做出了很好的區分,這樣利用rollup,cube和grouping函數,我們做數據統計的時候就可以輕松很多了.
