oracle之分析函数Word文件下载.docx

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函数,我们做数据统计的时候就可以轻松很