oracle之分析函数.docx
《oracle之分析函数.docx》由会员分享，可在线阅读，更多相关《oracle之分析函数.docx（19页珍藏版）》请在冰豆网上搜索。
oracle之分析函数
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取基数的分析函数
基础数据
createtablett（BILL_MONTHnumber（6）,AREA_CODEvarchar2（20）,NET_TYPEchar,LOCAL_FAREnumber（10,2））;
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
200408
5764
J
7342135.86
200408
5765
G
14450586.63
200408
5765
J
12680052.38
1.使用rollup函数的介绍
selectarea_code,sum（local_fare）local_fare
fromtt
groupbyarea_code
unionall
select'合计'area_code,sum（local_fare）local_fare
fromtt;
AREA_CODE LOCAL_FARE
5764
53156768.46
5765
104548719.19
5762
52039619.6
5761
54225413.04
5763
69186545.02
合计
333157065.31
Quote:
下面是直接使用普通sql语句求出各地区的汇总数据的例子
SQL>setautoton
SQL>selectarea_code,sum（local_fare）local_fare
2 fromt
3 groupbyarea_code
4 unionall
5 select'合计'area_code,sum（local_fare）local_fare
6 fromt
7 /
AREA_CODE LOCAL_FARE
------------------------
5761 54225413.04
5762 52039619.60
5763 69186545.02
5764 53156768.46
5765 104548719.19
合计 333157065.31
6rowsselected.
Elapsed:
00:
00:
00.03
ExecutionPlan
----------------------------------------------------------
0 SELECTSTATEMENTOptimizer=ALL_ROWS（Cost=7Card=1310Bytes=
24884）
1 0 UNION-ALL
2 1 SORT（GROUPBY）（Cost=5Card=1309Bytes=24871）
3 2 TABLEACCESS（FULL）OF'T'（Cost=2Card=1309Bytes=248
71）
4 1 SORT（AGGREGATE）
5 4 TABLEACCESS（FULL）OF'T'（Cost=2Card=1309Bytes=170
17）
Statistics
----------------------------------------------------------
0 recursivecalls
0 dbblockgets
6 consistentgets
0 physicalreads
0 redosize
561 bytessentviaSQL*Nettoclient
503 bytesreceivedviaSQL*Netfromclient
2 SQL*Netroundtripsto/fromclient
1 sorts（memory）
0 sorts（disk）
6 rowsprocessed
下面是使用分析函数rollup得出的汇总数据的例子
SQL>selectnvl（area_code,'合计'）area_code,sum（local_fare）local_fare
2 fromtt
3 groupbyrollup（area_code）;4 /
AREA_CODE LOCAL_FARE
------------------------
5761 54225413.04
5762 52039619.60
5763 69186545.02
5764 53156768.46
5765 104548719.19
合计 333157065.31
6rowsselected.
Elapsed:
00:
00:
00.00
ExecutionPlan
----------------------------------------------------------
0 SELECTSTATEMENTOptimizer=ALL_ROWS（Cost=5Card=1309Bytes=
24871）
1 0 SORT（GROUPBYROLLUP）（Cost=5Card=1309Bytes=24871）
2 1 TABLEACCESS（FULL）OF'T'（Cost=2Card=1309Bytes=24871
）
Statistics
----------------------------------------------------------
0 recursivecalls
0 dbblockgets
4 consistentgets
0 physicalreads
0 redosize
557 bytessentviaSQL*Nettoclient
503 bytesreceivedviaSQL*Netfromclient
2 SQL*Netroundtripsto/fromclient
1 sorts（memory）
0 sorts（disk）
6 rowsprocessed
从上面的例子我们不难看出使用rollup函数,系统的sql语句更加简单,耗用的资源更少,从6个consistentgets降到4个consistentgets,如果基表很大的话,结果就可想而知了.
1.使用cube函数的介绍
Quote:
为了介绍cube函数我们再来看看另外一个使用rollup的例子
06:
53:
00SQL>selectarea_code,bill_month,sum（local_fare）local_fare
06:
53:
37 2 fromt
06:
53:
38 3 groupbyrollup（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
26rowsselected.
Elapsed:
00:
00:
00.00
系统只是根据rollup的第一个参数area_code对结果集的数据做了汇总处理,而没有对bill_month做汇总分析处理,cube函数就是为了这个而设计的.
下面,让我们看看使用cube函数的结果
06:
58:
02SQL>selectarea_code,bill_month,sum（local_fare）local_fare
06:
58:
30 2 fromt
06:
58:
32 3 groupbycube（area_code,bill_month）
06:
58:
42 4 orderbyarea_code,bill_monthnullslast
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
30rowsselected.
Elapsed:
00:
00:
00.01
可以看到,在cube函数的输出结果比使用rollup多出了几行统计数据.这就是cube函数根据bill_month做的汇总统计结果
1rollup和cube函数的再深入
Quote:
从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,
我们如何来区分到底是根据那个字段做的汇总呢,
这时候,oracle的grouping函数就粉墨登场了.
如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0
1 selectdecode（grouping（area_code）,1,'allarea',to_char（area_code））area_code,
2 decode（grouping（bill_month）,1,'allmonth',bill_month）bill_month,
3 sum（local_fare）local_fare
4 fromt
5 groupbycube（area_code,bill_month）
6*orderbyarea_code,bill_monthnullslast
07:
07:
29SQL>/
AREA_CODE BILL_MONTH LOCAL_FARE
---------------------------------------
5761 200405 13060.43
5761 200406 13318.93
5761 200407 13710.27
5761 200408 14135.78
5761 allmonth 54225.41
5762 200405 12643.79
5762 200406 12795.06
5762 200407 13224.30
5762 200408 13376.47
5762 allmonth 52039.62
5763 200405 16649.78
5763 200406 17120.52
5763 200407 17487.49
5763 200408 17928.76
5763 allmonth 69186.54
5764 200405 12487.79
5764 200406 13295.19
5764 200407 13444.09
5764 200408 13929.69
5764 allmonth 53156.77
5765 200405 25057.74
5765 200406 26058.46
5765 200407 26301.88
5765 200408 27130.64
5765 allmonth 104548.72
allarea 200405 79899.53
allarea 200406 82588.15
allarea 200407 84168.03
allarea 200408 86501.34
allarea allmonth 333157.05
30rowsselected.
Elapsed:
00:
00:
00.01
07:
07:
31SQL>
可以看到,所有的空值现在都根据grouping函数做出了很好的区分,这样利用rollup,cube和grouping函数,我们做数据统计的时候就可以轻松很
升级会员 