Oracle 分析函数使用介绍（转）

今天我主要给大家介绍一下以下几个函数的使用方法
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12801008/viewspace-1025221/