ITPub博客

首页 > 数据库 > Oracle > Oracle 分析函数使用介绍(转)

Oracle 分析函数使用介绍(转)

原创 Oracle 作者:wangkxxe 时间:2009-08-12 21:51:09 0 删除 编辑

今天我主要给大家介绍一下以下几个函数的使用方法
1. 自动汇总函数rollup,cube,
2. rank 函数, rank,dense_rank,row_number
3. lag,lead函数
4. sum,avg,的移动增加,移动平均数
5. ratio_to_report报表处理函数
6. first,last取基数的分析函数

Code:
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,如果基表很大的话,结果就可想而知了
[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12801008/viewspace-1025221/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-25

  • 博文量
    68
  • 访问量
    94238