分析函數是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.04

　　200405 5761 J 5667089.85

　　200405 5762 G 6315075.96

　　200405 5762 J 6328716.15

　　200405 5763 G 8861742.59

　　200405 5763 J 7788036.32

　　200405 5764 G 6028670.45

　　200405 5764 J 6459121.49

　　200405 5765 G 13156065.77

　　200405 5765 J 11901671.70

　　200406 5761 G 7614587.96

　　200406 5761 J 5704343.05

　　200406 5762 G 6556992.60

　　200406 5762 J 6238068.05

　　200406 5763 G 9130055.46

　　200406 5763 J 7990460.25

　　200406 5764 G 6387706.01

　　200406 5764 J 6907481.66

　　200406 5765 G 13562968.81

　　200406 5765 J 12495492.50

　　200407 5761 G 7987050.65

　　200407 5761 J 5723215.28

　　200407 5762 G 6833096.68

　　200407 5762 J 6391201.44

　　200407 5763 G 9410815.91

　　200407 5763 J 8076677.41

　　200407 5764 G 6456433.23

　　200407 5764 J 6987660.53

　　200407 5765 G 14000101.20

　　200407 5765 J 12301780.20

　　200408 5761 G 8085170.84

　　200408 5761 J 6050611.37

　　200408 5762 G 6854584.22

　　200408 5762 J 6521884.50

　　200408 5763 G 9468707.65

　　200408 5763 J 8460049.43

　　200408 5764 G 6587559.23

　　BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE

　　--------------- ---------- ---------- --------------

　　200408 5764 J 7342135.86

　　200408 5765 G 14450586.63

　　200408 5765 J 12680052.38

　　40 rows selected.

　　Elapsed: 00:00:00.00

　　1. 使用rollup函數的介紹

　　Quote:

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

　　06:41:36 SQL> set autot on

　　06:43:36 SQL> select area_code,sum(local_fare) local_fare

　　06:43:50 2 from t

　　06:43:51 3 group by area_code

　　06:43:57 4 union all

　　06:44:00 5 select '合計' area_code,sum(local_fare) local_fare

　　06:44:06 6 from t

　　06:44:08 7 /

　　AREA_CODE LOCAL_FARE

　　---------- --------------

　　5761 54225413.04

　　5762 52039619.60

　　5763 69186545.02

　　5764 53156768.46

　　5765 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_fare

　　06:45:26 2 from t

　　06:45:30 3 group by rollup(nvl(area_code,'合計'))

　　06:45:50 4 /

　　AREA_CODE LOCAL_FARE

　　---------- --------------

　　5761 54225413.04

　　5762 52039619.60

　　5763 69186545.02

　　5764 53156768.46

　　5765 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_fare

　　06:53:37 2 from t

　　06:53:38 3 group by rollup(area_code,bill_month)

　　06:53:49 4 /

　　AREA_CODE BILL_MONTH LOCAL_FARE

　　---------- --------------- --------------

　　5761 200405 13060433.89

　　5761 200406 13318931.01

　　5761 200407 13710265.93

　　5761 200408 14135782.21

　　5761 54225413.04

　　5762 200405 12643792.11

　　5762 200406 12795060.65

　　5762 200407 13224298.12

　　5762 200408 13376468.72

　　5762 52039619.60

　　5763 200405 16649778.91

　　5763 200406 17120515.71

　　5763 200407 17487493.32

　　5763 200408 17928757.08

　　5763 69186545.02

　　5764 200405 12487791.94

　　5764 200406 13295187.67

　　5764 200407 13444093.76

　　5764 200408 13929695.09

　　5764 53156768.46

　　5765 200405 25057737.47

　　5765 200406 26058461.31

　　5765 200407 26301881.40

　　5765 200408 27130639.01

　　5765 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_fare

　　06:58:30 2 from t

　　06:58:32 3 group by cube(area_code,bill_month)

　　06:58:42 4 order by area_code,bill_month nulls last

　　06:58:57 5 /

　　AREA_CODE BILL_MONTH LOCAL_FARE

　　---------- --------------- --------------

　　5761 200405 13060.43

　　5761 200406 13318.93

　　5761 200407 13710.27

　　5761 200408 14135.78

　　5761 54225.41

　　5762 200405 12643.79

　　5762 200406 12795.06

　　5762 200407 13224.30

　　5762 200408 13376.47

　　5762 52039.62

　　5763 200405 16649.78

　　5763 200406 17120.52

　　5763 200407 17487.49

　　5763 200408 17928.76

　　5763 69186.54

　　5764 200405 12487.79

　　5764 200406 13295.19

　　5764 200407 13444.09

　　5764 200408 13929.69

　　5764 53156.77

　　5765 200405 25057.74

　　5765 200406 26058.46

　　5765 200407 26301.88

　　5765 200408 27130.64

　　5765 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 last

　　07:07:29 SQL> /

　　AREA_CODE BILL_MONTH LOCAL_FARE

　　---------- --------------- --------------

　　5761 200405 13060.43

　　5761 200406 13318.93

　　5761 200407 13710.27

　　5761 200408 14135.78

　　5761 all month 54225.41

　　5762 200405 12643.79

　　5762 200406 12795.06

　　5762 200407 13224.30

　　5762 200408 13376.47

　　5762 all month 52039.62

　　5763 200405 16649.78

　　5763 200406 17120.52

　　5763 200407 17487.49

　　5763 200408 17928.76

　　5763 all month 69186.54

　　5764 200405 12487.79

　　5764 200406 13295.19

　　5764 200407 13444.09

　　5764 200408 13929.69

　　5764 all month 53156.77

　　5765 200405 25057.74

　　5765 200406 26058.46

　　5765 200407 26301.88

　　5765 200408 27130.64

　　5765 all month 104548.72

　　all area 200405 79899.53

　　all area 200406 82588.15

　　all area 200407 84168.03

　　all area 200408 86501.34

　　all area all month 333157.05

　　30 rows selected.

　　Elapsed: 00:00:00.01

　　07:07:31 SQL>

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