oracle分析函数:oracle 分析函数的使用

="t18">分析是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> autot _disibledevent=> 　　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 s sent via SQL*Net to client

　　503 s 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 s sent via SQL*Net to client

　　503 s 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,如果基表很大话,结果就可想而知了.

　　1. 使用cube介绍

　　Quote:

　　为了介绍cube我们再来看看另外个使用rollup例子

　　06:53:00 SQL> select area_code,bill_month,sum(local_fare) local_fare

　　06:53:37 2 from t

　　06:53:38 3 group by rollup(area_code,bill_month)

　　06:53:49 4 /

　　AREA_CODE BILL_MONTH LOCAL_FARE

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

　　5761 200405 13060433.89

　　5761 200406 13318931.01

　　5761 200407 13710265.93

　　5761 200408 14135782.21

　　5761 54225413.04

　　5762 200405 12643792.11

　　5762 200406 12795060.65

　　5762 200407 13224298.12

　　5762 200408 13376468.72

　　5762 52039619.60

　　5763 200405 16649778.91

　　5763 200406 17120515.71

　　5763 200407 17487493.32

　　5763 200408 17928757.08

　　5763 69186545.02

　　5764 200405 12487791.94

　　5764 200406 13295187.67

　　5764 200407 13444093.76

　　5764 200408 13929695.09

　　5764 53156768.46

　　5765 200405 25057737.47

　　5765 200406 26058461.31

　　5765 200407 26301881.40

　　5765 200408 27130639.01

　　5765 104548719.19

　　333157065.31

　　26 rows selected.

　　Elapsed: 00:00:00.00

　　系统只是根据rollup第个参数area_code对结果集数据做了汇总处理,而没有对bill_month做汇总分析处理,cube就是为了这个而设计.

　　下面,让我们看看使用cube结果

　　06:58:02 SQL> select area_code,bill_month,sum(local_fare) local_fare

　　06:58:30 2 from t

　　06:58:32 3 group by cube(area_code,bill_month)

　　06:58:42 4 order by area_code,bill_month nulls last

　　06:58:57 5 /

　　AREA_CODE BILL_MONTH LOCAL_FARE

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

　　5761 200405 13060.43

　　5761 200406 13318.93

　　5761 200407 13710.27

　　5761 200408 14135.78

　　5761 54225.41

　　5762 200405 12643.79

　　5762 200406 12795.06

　　5762 200407 13224.30

　　5762 200408 13376.47

　　5762 52039.62

　　5763 200405 16649.78

　　5763 200406 17120.52

　　5763 200407 17487.49

　　5763 200408 17928.76

　　5763 69186.54

　　5764 200405 12487.79

　　5764 200406 13295.19

　　5764 200407 13444.09

　　5764 200408 13929.69

　　5764 53156.77

　　5765 200405 25057.74

　　5765 200406 26058.46

　　5765 200407 26301.88

　　5765 200408 27130.64

　　5765 104548.72

　　200405 79899.53

　　200406 82588.15

　　200407 84168.03

　　200408 86501.34

　　333157.05

　　30 rows selected.

　　Elapsed: 00:00:00.01

　　可以看到,在cube输出结果比使用rollup多出了几行统计数据.这就是cube根据bill_month做汇总统计结果]

　　1 rollup 和 cube再深入

　　Quote:

　　从上面结果中我们很容易发现,每个统计数据所对应行都会出现null,我们如何来区分到底是根据那个字段做汇总呢,这时候,oraclegrouping就粉墨登场了.

　　如果当前汇总记录是利用该字段得出,grouping就会返回1,否则返回0

　　1 select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code,

　　2 decode(grouping(bill_month),1,'all month',bill_month) bill_month,

　　3 sum(local_fare) local_fare

　　4 from t

　　5 group by cube(area_code,bill_month)

　　6* order by area_code,bill_month nulls last

　　07:07:29 SQL> /

　　AREA_CODE BILL_MONTH LOCAL_FARE

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

　　5761 200405 13060.43

　　5761 200406 13318.93

　　5761 200407 13710.27

　　5761 200408 14135.78

　　5761 all month 54225.41

　　5762 200405 12643.79

　　5762 200406 12795.06

　　5762 200407 13224.30

　　5762 200408 13376.47

　　5762 all month 52039.62

　　5763 200405 16649.78

　　5763 200406 17120.52

　　5763 200407 17487.49

　　5763 200408 17928.76

　　5763 all month 69186.54

　　5764 200405 12487.79

　　5764 200406 13295.19

　　5764 200407 13444.09

　　5764 200408 13929.69

　　5764 all month 53156.77

　　5765 200405 25057.74

　　5765 200406 26058.46

　　5765 200407 26301.88

　　5765 200408 27130.64

　　5765 all month 104548.72

　　all area 200405 79899.53

　　all area 200406 82588.15

　　all area 200407 84168.03

　　all area 200408 86501.34

　　all area all month 333157.05

　　30 rows selected.

　　Elapsed: 00:00:00.01

　　07:07:31 SQL>

　　可以看到,所有空值现在都根据grouping做出了很好区分,这样利用rollup,cube和grouping,我们做数据统计时候就可以轻松很多了.

延伸阅读

• 2009-9-5-- oracle安装失败:分析oracle 登录失败的原因
• 2008-12-24-- oracle执行函数:Oracle 下医嘱执行函数
• 2009-10-3-- oracleover函数:[转]Oracle中over函数的使用举例
• 2009-10-11-- oracle自定义函数:他山的石 可以攻玉--Oracle自定义的聚集函数的强大功能
• 2009-2-21-- windowsapi函数:在Oracle/2000中调用Windows API函数
• 2009-9-27-- oracle常用函数:110个Oracle常用函数整理总结( 2)
• 2009-8-16-- oracle自定义函数:Oracle自定义函数——f_henry_GetStringLeng
• 2009-8-16-- oracle函数:Oracle 函数大全
• 2009-8-16-- oracle日期函数:Oracle 日期的一些简单使用
• 2008-12-24-- oracle日期时间:Oracle有关时间/日期的操作