分析函數(shù)是Oracle816引入的一個(gè)全新的概念,為我們分析數(shù)據(jù)提供了一種簡(jiǎn)單高效的處理方式.在分析函數(shù)出現(xiàn)以前,我們必須使用自聯(lián)查詢,子查詢或者內(nèi)聯(lián)視圖,甚至復(fù)雜的存儲(chǔ)過(guò)程實(shí)現(xiàn)的語(yǔ)句,現(xiàn)在只要一條簡(jiǎn)單的sql語(yǔ)句就可以實(shí)現(xiàn)了,而且在執(zhí)行效率方面也有相當(dāng)大的提高.下面我將針對(duì)分析函數(shù)做一些具體的說(shuō)明. 今天我主要給大家介紹一下以下幾個(gè)函數(shù)的使用方法 1. 自動(dòng)匯總函數(shù)rollup,cube, 2. rank 函數(shù), rank,dense_rank,row_number 3. lag,lead函數(shù) 4. sum,avg,的移動(dòng)增加,移動(dòng)平均數(shù) 5. ratio_to_report報(bào)表處理函數(shù) 6. first,last取基數(shù)的分析函數(shù) 基礎(chǔ)數(shù)據(jù) 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函數(shù)的介紹 Quote: 下面是直接使用普通sql語(yǔ)句求出各地區(qū)的匯總數(shù)據(jù)的例子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 '合計(jì)' 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合計(jì) 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 下面是使用分析函數(shù)rollup得出的匯總數(shù)據(jù)的例子 06:44:09 SQL> select nvl(area_code,'合計(jì)') area_code,sum(local_fare) local_fare06:45:26 2 from t06:45:30 3 group by rollup(nvl(area_code,'合計(jì)'))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函數(shù),系統(tǒng)的sql語(yǔ)句更加簡(jiǎn)單,耗用的資源更少,從6個(gè)consistent gets降到4個(gè)consistent gets,假如基表很大的話,結(jié)果就可想而知了. 1. 使用cube函數(shù)的介紹 Quote: 為了介紹cube函數(shù)我們?cè)賮?lái)看看另外一個(gè)使用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 系統(tǒng)只是根據(jù)rollup的第一個(gè)參數(shù)area_code對(duì)結(jié)果集的數(shù)據(jù)做了匯總處理,而沒(méi)有對(duì)bill_month做匯總分析處理,cube函數(shù)就是為了這個(gè)而設(shè)計(jì)的. 下面,讓我們看看使用cube函數(shù)的結(jié)果 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函數(shù)的輸出結(jié)果比使用rollup多出了幾行統(tǒng)計(jì)數(shù)據(jù).這就是cube函數(shù)根據(jù)bill_month做的匯總統(tǒng)計(jì)結(jié)果]
1 rollup 和 cube函數(shù)的再深入 Quote: 從上面的結(jié)果中我們很輕易發(fā)現(xiàn),每個(gè)統(tǒng)計(jì)數(shù)據(jù)所對(duì)應(yīng)的行都會(huì)出現(xiàn)null,我們?nèi)绾蝸?lái)區(qū)分到底是根據(jù)那個(gè)字段做的匯總呢,這時(shí)候,oracle的grouping函數(shù)就粉墨登場(chǎng)了. 假如當(dāng)前的匯總記錄是利用該字段得出的,grouping函數(shù)就會(huì)返回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> 可以看到,所有的空值現(xiàn)在都根據(jù)grouping函數(shù)做出了很好的區(qū)分,這樣利用rollup,cube和grouping函數(shù),我們做數(shù)據(jù)統(tǒng)計(jì)的時(shí)候就可以輕松很多了.
