Oracle 分析函數的使用一

分析函數是Oracle816引入的一個全新的概念,為我們分析數據提供了一種簡單高效的處理方式.在分析函數出現以前,我們必須使用自聯查詢,子查詢或者內聯視圖,甚至復雜的存儲過程實現的語句,現在只要一條簡單的sql語句就可以實現了,而且在執行效率方面也有相當大的提高.下面我將針對分析函數做一些具體的說明.今天我主要給大家介紹一下以下幾個函數的使用方法1.; 自動匯總函數rollup,cube,2.; rank 函數, rank,dense_rank,row_number3.;;;;;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.23BILL_MONTH;;;AREA_CODE; NET_TYPE;;;;LOCAL_FARE--------------- ---------- ---------- --------------200408; 5764;;;;J;;;7342135.86200408; 5765;;;;G;;14450586.63200408; 5765;;;;J;;12680052.3840 rows selected.Elapsed: 00:00:00.001. 使用rollup函數的介紹Quote:; 下面是直接使用普通sql語句求出各地區的匯總數據的例子06:41:36 SQL> set autot on06:43:36 SQL> select area_code,sum(local_fare) local_fare06:43:502; from t06:43:513; group by area_code06:43:574; union all06:44:005; select '合計' area_code,sum(local_fare) local_fare06:44:066; from t06:44:087; /AREA_CODE;;;LOCAL_FARE---------- --------------5761; 54225413.045762; 52039619.605763; 69186545.025764; 53156768.465765 104548719.19合計 333157065.316 rows selected.Elapsed: 00:00:00.03Execution Plan----------------------------------------------------------0;;;SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1310 Bytes=; 24884)1;0UNION-ALL2;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:262; from t06:45:303; group by rollup(nvl(area_code,'合計'))06:45:504; /AREA_CODE;;;LOCAL_FARE---------- --------------5761; 54225413.045762; 52039619.605763; 69186545.025764; 53156768.465765 104548719.19;;;;;;;;;;333157065.316 rows selected.Elapsed: 00:00:00.00Execution Plan----------------------------------------------------------0;;;SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1309 Bytes=; 24871)1;0SORT (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:372; from t06:53:383; group by rollup(area_code,bill_month)06:53:494; /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.3126 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:302; from t06:58:323; group by cube(area_code,bill_month)06:58:424; order by area_code,bill_month nulls last06:58:575; /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.72200405;;;;;79899.53200406;;;;;82588.15200407;;;;;84168.03200408;;;;;86501.34;;;;;333157.0530 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 area200405;;;;;79899.53all area200406;;;;;82588.15all area200407;;;;;84168.03all area200408;;;;;86501.34all areaall month;333157.0530 rows selected.Elapsed: 00:00:00.0107:07:31 SQL>可以看到,所有的空值現在都根據grouping函數做出了很好的區分,這樣利用rollup,cube和grouping函數,我們做數據統計的時候就可以輕松很多了.