Oracle 分析函數的使用（轉載）

分析函數是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取基數的分析函數

基礎資料：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

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函數的介紹

下面是直接使用普通sql語句求出各地區的彙總資料的例子set timing on

set autotrace on

col LOCAL_FARE for 9999999999.99

select area_code,sum(local_fare) local_fare

from t

group by area_code

union all

select '合計' area_code,sum(local_fare) local_fare

/

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=24871)

4 1 SORT (AGGREGATE)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=17017)

Statistics

0 recursive calls

0 db block gets6 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得出的彙總資料的例子select nvl(area_code,'合計') area_code,sum(local_fare) local_fare

group by rollup(nvl(area_code,'合計'))

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

333157065.31

Elapsed: 00:00:00.00

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)

0 db block gets4 consistent gets

557 bytes sent via SQL*Net to client