Oracle分析函数

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取基数的分析函数 基础数据

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