Oracle 分析函数的使用.docx

1、Oracle 分析函数的使用Oracle 分析函数的使用Oracle 分析函数的使用Oracle分析函数使用介绍 分析函数是oracle816引入的一个全新的概念,为我们分析数据提供了一种简单高效的处理方式.在分析函数出现以前,我们必须使用自联查询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在只要一条简单的sql语句就可以实现了,而且在执行效率方面也有相当大的提高.下面我将针对分析函数做一些具体的说明.今天我主要给大家介绍一下以下几个函数的使用方法1.自动汇总函数rollup,cube,2.rank 函数, rank,dense_rank,row_number3. lag,lead函

2、数4. sum,avg,的移动增加,移动平均数5. ratio_to_report报表处理函数6. first,last取基数的分析函数基础数据Code:Copy to clipboard06:34:23 SQL select * from t;BILL_MONTH AREA_CODENET_TYPE LOCAL_FARE- - - -200405 5761 G 7393344.04200405 5761 J 5667089.85200405 5762 G 6315075.96200405 5762 J 6328716.15200405 5763 G 8861742.59200405 5763

3、 J 7788036.32200405 5764 G 6028670.45200405 5764 J 6459121.49200405 5765 G 13156065.77200405 5765 J 11901671.70200406 5761 G 7614587.96200406 5761 J 5704343.05200406 5762 G 6556992.60200406 5762 J 6238068.05200406 5763 G 9130055.46200406 5763 J 7990460.25200406 5764 G 6387706.01200406 5764 J 6907481

4、.66200406 5765 G 13562968.81200406 5765 J 12495492.50200407 5761 G 7987050.65200407 5761 J 5723215.28200407 5762 G 6833096.68200407 5762 J 6391201.44200407 5763 G 9410815.91200407 5763 J 8076677.41200407 5764 G 6456433.23200407 5764 J 6987660.53200407 5765 G 14000101.20200407 5765 J 12301780.2020040

5、8 5761 G 8085170.84200408 5761 J 6050611.37200408 5762 G 6854584.22200408 5762 J 6521884.50200408 5763 G 9468707.65200408 5763 J 8460049.43200408 5764 G 6587559.23BILL_MONTH AREA_CODENET_TYPE LOCAL_FARE- - - -200408 5764 J 7342135.86200408 5765 G 14450586.63200408 5765 J 12680052.3840 rows selected.

6、Elapsed: 00:00:00.001. 使用rollup函数的介绍Quote:下面是直接使用普通sql语句求出各地区的汇总数据的例子06:41:36 SQL set autot on06:43:36 SQL select area_code,sum(local_fare) local_fare06:43:50 2from t06:43:51 3group by area_code06:43:57 4union all06:44:00 5select 合计 area_code,sum(local_fare) local_fare06:44:06 6from t06:44:08 7/AREA

7、_CODE LOCAL_FARE- -5761 54225413.045762 52039619.605763 69186545.025764 53156768.465765 104548719.19合计 333157065.316 rows selected.Elapsed: 00:00:00.03Execution 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

8、) 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- 0recursive calls 0db block gets 6consistent gets 0physical reads 0redo size 561bytes sent via SQL*Net to client 503bytes received via SQL*Net fr

9、om client 2SQL*Net roundtrips to/from client 1sorts (memory) 0sorts (disk) 6rows processed下面是使用分析函数rollup得出的汇总数据的例子06:44:09 SQL select nvl(area_code,合计) area_code,sum(local_fare) local_fare06:45:26 2from t06:45:30 3group by rollup(nvl(area_code,合计)06:45:50 4/AREA_CODE LOCAL_FARE- -5761 54225413.0457

10、62 52039619.605763 69186545.025764 53156768.465765 104548719.19 333157065.316 rows selected.Elapsed: 00:00:00.00Execution 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=13

11、09 Bytes=24871 )Statistics- 0recursive calls 0db block gets 4consistent gets 0physical reads 0redo size 557bytes sent via SQL*Net to client 503bytes received via SQL*Net from client 2SQL*Net roundtrips to/from client 1sorts (memory) 0sorts (disk) 6rows processed从上面的例子我们不难看出使用rollup函数,系统的sql语句更加简单,耗用

12、的资源更少,从6个consistent gets降到4个consistent gets,如果基表很大的话,结果就可想而知了.1. 使用cube函数的介绍Quote:为了介绍cube函数我们再来看看另外一个使用rollup的例子06:53:00 SQL select area_code,bill_month,sum(local_fare) local_fare06:53:37 2from t06:53:38 3group by rollup(area_code,bill_month)06:53:49 4/AREA_CODEBILL_MONTH LOCAL_FARE- - -5761 200405

13、 13060433.895761 200406 13318931.015761 200407 13710265.935761 200408 14135782.215761 54225413.045762 200405 12643792.115762 200406 12795060.655762 200407 13224298.125762 200408 13376468.725762 52039619.605763 200405 16649778.915763 200406 17120515.715763 200407 17487493.325763 200408 17928757.08576

14、3 69186545.025764 200405 12487791.945764 200406 13295187.675764 200407 13444093.765764 200408 13929695.095764 53156768.465765 200405 25057737.475765 200406 26058461.315765 200407 26301881.405765 200408 27130639.015765 104548719.19 333157065.3126 rows selected.Elapsed: 00:00:00.00系统只是根据rollup的第一个参数ar

15、ea_code对结果集的数据做了汇总处理,而没有对bill_month做汇总分析处理,cube函数就是为了这个而设计的.下面,让我们看看使用cube函数的结果06:58:02 SQL select area_code,bill_month,sum(local_fare) local_fare06:58:30 2from t06:58:32 3group by cube(area_code,bill_month)06:58:42 4order by area_code,bill_month nulls last06:58:57 5/AREA_CODEBILL_MONTH LOCAL_FARE-

16、- -5761 200405 13060.435761 200406 13318.935761 200407 13710.275761 200408 14135.785761 54225.415762 200405 12643.795762 200406 12795.065762 200407 13224.305762 200408 13376.475762 52039.625763 200405 16649.785763 200406 17120.525763 200407 17487.495763 200408 17928.765763 69186.545764 200405 12487.

17、795764 200406 13295.195764 200407 13444.095764 200408 13929.695764 53156.775765 200405 25057.745765 200406 26058.465765 200407 26301.885765 200408 27130.645765 104548.72 200405 79899.53 200406 82588.15 200407 84168.03 200408 86501.34 333157.0530 rows selected.Elapsed: 00:00:00.01可以看到,在cube函数的输出结果比使用

18、rollup多出了几行统计数据.这就是cube函数根据bill_month做的汇总统计结果1 rollup 和 cube函数的再深入Quote:从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,我们如何来区分到底是根据那个字段做的汇总呢,这时候,oracle的grouping函数就粉墨登场了.如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回01select decode(grouping(area_code),1,all area,to_char(area_code) area_code,2 decode(grouping(bill_month

19、),1,all month,bill_month) bill_month,3 sum(local_fare) local_fare4from t5group by cube(area_code,bill_month)6* order by area_code,bill_month nulls last07:07:29 SQL /AREA_CODEBILL_MONTH LOCAL_FARE- - -5761 200405 13060.435761 200406 13318.935761 200407 13710.275761 200408 14135.785761 all month 54225.415762 200405 12643.795762 200406 12795.065762