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