如何使用oracle中的分析函数

如何使用oracle中的分析函数？

it55.com

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

　　1. 自动汇总函数rollup,cube,

免费资源http://www.it55.com

　　2. rank 函数, rank,dense_rank,row_number

45398 http://www.it55.com it55学习IT知识，享受IT生活 4dfkjn

　　3. lag,lead函数 免费网页模版下载http://www.it55.com

　　4. sum,avg,的移动增加,移动平均数

　　5. ratio_to_report报表处理函数 http://www.it55.com/

　　6. first,last取基数的分析函数

IT资讯之家 http://www.it55.com

　　基础数据

　　Code: [Copy to clipboard] 免费网页模版下载http://www.it55.com

　　06:34:23 SQL> select * from t;

　　BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE 免费资源http://www.it55.com

　　--------------- ---------- ---------- -------------- http://www.it55.com

　　200405 5761 G 7393344.04

免费壁纸下载http://www.it55.com

　　200405 5761 J 5667089.85

http://www.it55.com在线教程

　　200405 5762 G 6315075.96

免费资源http://www.it55.com

　　200405 5762 J 6328716.15

IT资讯之家 http://www.it55.com

　　200405 5763 G 8861742.59 vd;k;l http://www.it55.com rdfg

　　200405 5763 J 7788036.32

　　200405 5764 G 6028670.45 精美商业网页模版下载http://www.it55.com

　　200405 5764 J 6459121.49 免费网页模版下载http://www.it55.com

　　200405 5765 G 13156065.77

　　200405 5765 J 11901671.70

http://www.it55.com在线教程

　　200406 5761 G 7614587.96

IT资讯之家 http://www.it55.com

　　200406 5761 J 5704343.05 精美韩国模版下载http://www.it55.com

　　200406 5762 G 6556992.60 http://www.it55.com在线教程

　　200406 5762 J 6238068.05 it55.com

　　200406 5763 G 9130055.46 免费设计素材下载http://www.it55.com

　　200406 5763 J 7990460.25

　　200406 5764 G 6387706.01

　　200406 5764 J 6907481.66

精美韩国模版下载http://www.it55.com

　　200406 5765 G 13562968.81 免费资源http://www.it55.com

　　200406 5765 J 12495492.50 IT资讯之家 http://www.it55.com

　　200407 5761 G 7987050.65 免费网页模版下载http://www.it55.com

　　200407 5761 J 5723215.28 免费资源http://www.it55.com

　　200407 5762 G 6833096.68 it55.com

　　200407 5762 J 6391201.44 免费设计素材下载http://www.it55.com

　　200407 5763 G 9410815.91 http://www.it55.com

　　200407 5763 J 8076677.41 http://www.it55.com在线教程

　　200407 5764 G 6456433.23 精美韩国模版下载http://www.it55.com

　　200407 5764 J 6987660.53

vd;k;l http://www.it55.com rdfg

　　200407 5765 G 14000101.20 免费网页模版下载http://www.it55.com

　　200407 5765 J 12301780.20

sflj http://www.it55.com kg^&fgd

　　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 精美商业网页模版下载http://www.it55.com

　　200408 5763 J 8460049.43 45398 http://www.it55.com it55学习IT知识，享受IT生活 4dfkjn

　　200408 5764 G 6587559.23

　　BILL_MONTH AREA_CODE NET_TYPE LOCAL_FARE

免费矢量图片素材下载http://www.it55.com

　　--------------- ---------- ---------- -------------- 精美韩国模版下载http://www.it55.com

　　200408 5764 J 7342135.86

精美韩国模版下载http://www.it55.com

　　200408 5765 G 14450586.63

　　200408 5765 J 12680052.38 http://www.it55.com/

　　40 rows selected. 45398 http://www.it55.com it55学习IT知识，享受IT生活 4dfkjn

　　Elapsed: 00:00:00.00

　　1. 使用rollup函数的介绍 it55.com

　　Quote: http://www.it55.com在线教程

　　下面是直接使用普通sql语句求出各地区的汇总数据的例子 it55.com

　　06:41:36 SQL> set autot on

IT资讯之家 http://www.it55.com

　　06:43:36 SQL> select area_code,sum(local_fare) local_fare

　　06:43:50 2 from t sflj http://www.it55.com kg^&fgd

　　06:43:51 3 group by area_code

　　06:43:57 4 union all 免费资源http://www.it55.com

　　06:44:00 5 select '合计' area_code,sum(local_fare) local_fare IT资讯之家 http://www.it55.com

　　06:44:06 6 from t

　　06:44:08 7 /

vd;k;l http://www.it55.com rdfg

　　AREA_CODE LOCAL_FARE

免费资源http://www.it55.com

　　---------- -------------- it55.com

　　5761 54225413.04

　　5762 52039619.60 vd;k;l http://www.it55.com rdfg

　　5763 69186545.02 vd;k;l http://www.it55.com rdfg

　　5764 53156768.46 免费壁纸下载http://www.it55.com

　　5765 104548719.19

http://www.it55.com在线教程

　　合计 333157065.31

　　6 rows selected. 45398 http://www.it55.com it55学习IT知识，享受IT生活 4dfkjn

　　Elapsed: 00:00:00.03

免费设计素材下载http://www.it55.com

　　Execution Plan 免费资源http://www.it55.com

　　---------------------------------------------------------- IT资讯之家 http://www.it55.com

　　0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1310 Bytes=

http://www.it55.com在线教程

　　24884) 免费网页模版下载http://www.it55.com

　　1 0 UNION-ALL

免费网页模版下载http://www.it55.com

　　2 1 SORT (GROUP BY) (Cost=5 Card=1309 Bytes=24871) 免费壁纸下载http://www.it55.com

　　3 2 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=248 免费设计素材下载http://www.it55.com

　　71) sflj http://www.it55.com kg^&fgd

　　4 1 SORT (AGGREGATE)

　　5 4 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=170

　　17)

vd;k;l http://www.it55.com rdfg

　　Statistics http://www.it55.com/

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

http://www.it55.com

　　0 recursive calls 45398 http://www.it55.com it55学习IT知识，享受IT生活 4dfkjn

　　0 db block gets

45398 http://www.it55.com it55学习IT知识，享受IT生活 4dfkjn

　　6 consistent gets

免费壁纸下载http://www.it55.com

　　0 physical reads 精美韩国模版下载http://www.it55.com

　　0 redo size 免费矢量图片素材下载http://www.it55.com

　　561 bytes sent via SQL*Net to client it55.com

　　503 bytes received via SQL*Net from client http://www.it55.com/

　　2 SQL*Net roundtrips to/from client

http://www.it55.com在线教程

　　1 sorts (memory)

免费设计素材下载http://www.it55.com

　　0 sorts (disk)

it55.com

　　6 rows processed 精美韩国模版下载http://www.it55.com

　　下面是使用分析函数rollup得出的汇总数据的例子

vd;k;l http://www.it55.com rdfg

　　06:44:09 SQL> select nvl(area_code,'合计') area_code,sum(local_fare) local_fare 免费网页模版下载http://www.it55.com

　　06:45:26 2 from t IT资讯之家 http://www.it55.com

　　06:45:30 3 group by rollup(nvl(area_code,'合计')) sflj http://www.it55.com kg^&fgd

　　06:45:50 4 /

http://www.it55.com在线教程

　　AREA_CODE LOCAL_FARE 免费网页模版下载http://www.it55.com

　　---------- -------------- http://www.it55.com

　　5761 54225413.04

免费资源http://www.it55.com

　　5762 52039619.60

　　5763 69186545.02 vd;k;l http://www.it55.com rdfg

　　5764 53156768.46 免费矢量图片素材下载http://www.it55.com

　　5765 104548719.19

　　333157065.31 免费资源http://www.it55.com

　　6 rows selected. 免费网页模版下载http://www.it55.com

　　Elapsed: 00:00:00.00 精美韩国模版下载http://www.it55.com

　　Execution Plan

免费设计素材下载http://www.it55.com

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

　　0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1309 Bytes= http://www.it55.com

　　24871) sflj http://www.it55.com kg^&fgd

　　1 0 SORT (GROUP BY ROLLUP) (Cost=5 Card=1309 Bytes=24871)

http://www.it55.com/

　　2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=24871

　　)

　　Statistics sflj http://www.it55.com kg^&fgd

　　---------------------------------------------------------- 精美商业网页模版下载http://www.it55.com

　　0 recursive calls

45398 http://www.it55.com it55学习IT知识，享受IT生活 4dfkjn

　　0 db block gets 免费壁纸下载http://www.it55.com

　　4 consistent gets

　　0 physical reads 免费网页模版下载http://www.it55.com

　　0 redo size

　　557 bytes sent via SQL*Net to client 精美韩国模版下载http://www.it55.com

　　503 bytes received via SQL*Net from client

　　2 SQL*Net roundtrips to/from client

　　1 sorts (memory)

http://www.it55.com在线教程

　　0 sorts (disk)

　　6 rows processed

　　从上面的例子我们不难看出使用rollup函数,系统的sql语句更加简单,耗用的资源更少,从6个consistent gets降到4个consistent gets,如果基表很大的话,结果就可想而知了. IT资讯之家 http://www.it55.com

　　1. 使用cube函数的介绍 免费资源http://www.it55.com

　　Quote:

免费矢量图片素材下载http://www.it55.com

　　为了介绍cube函数我们再来看看另外一个使用rollup的例子 精美商业网页模版下载http://www.it55.com

　　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)

免费壁纸下载http://www.it55.com

　　06:53:49 4 / http://www.it55.com在线教程

　　AREA_CODE BILL_MONTH LOCAL_FARE

精美商业网页模版下载http://www.it55.com

　　---------- --------------- -------------- IT资讯之家 http://www.it55.com

　　5761 200405 13060433.89 免费矢量图片素材下载http://www.it55.com

　　5761 200406 13318931.01 http://www.it55.com/

　　5761 200407 13710265.93 免费壁纸下载http://www.it55.com

　　5761 200408 14135782.21 免费资源http://www.it55.com

　　5761 54225413.04 it55.com

　　5762 200405 12643792.11 it55.com

　　5762 200406 12795060.65 免费壁纸下载http://www.it55.com

　　5762 200407 13224298.12

　　5762 200408 13376468.72 免费网页模版下载http://www.it55.com

　　5762 52039619.60

免费矢量图片素材下载http://www.it55.com

　　5763 200405 16649778.91

免费资源http://www.it55.com

　　5763 200406 17120515.71

　　5763 200407 17487493.32

　　5763 200408 17928757.08

免费设计素材下载http://www.it55.com

　　5763 69186545.02

精美商业网页模版下载http://www.it55.com

　　5764 200405 12487791.94 45398 http://www.it55.com it55学习IT知识，享受IT生活 4dfkjn

　　5764 200406 13295187.67

http://www.it55.com在线教程

　　5764 200407 13444093.76

vd;k;l http://www.it55.com rdfg

　　5764 200408 13929695.09

　　5764 53156768.46

免费资源http://www.it55.com

　　5765 200405 25057737.47 http://www.it55.com在线教程

　　5765 200406 26058461.31

http://www.it55.com在线教程

　　5765 200407 26301881.40

　　5765 200408 27130639.01 免费壁纸下载http://www.it55.com

　　5765 104548719.19

免费网页模版下载http://www.it55.com

　　333157065.31 it55.com

　　26 rows selected. http://www.it55.com/

　　Elapsed: 00:00:00.00 http://www.it55.com

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

　　下面,让我们看看使用cube函数的结果 http://www.it55.com/

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

　　06:58:30 2 from t

精美韩国模版下载http://www.it55.com

　　06:58:32 3 group by cube(area_code,bill_month) sflj http://www.it55.com kg^&fgd

　　06:58:42 4 order by area_code,bill_month nulls last 免费网页模版下载http://www.it55.com

　　06:58:57 5 /

　　AREA_CODE BILL_MONTH LOCAL_FARE 免费资源http://www.it55.com

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

it55.com

　　5761 200405 13060.43 免费设计素材下载http://www.it55.com

　　5761 200406 13318.93

免费壁纸下载http://www.it55.com

　　5761 200407 13710.27 免费网页模版下载http://www.it55.com

　　5761 200408 14135.78 sflj http://www.it55.com kg^&fgd

　　5761 54225.41

vd;k;l http://www.it55.com rdfg

　　5762 200405 12643.79 vd;k;l http://www.it55.com rdfg

　　5762 200406 12795.06 免费矢量图片素材下载http://www.it55.com

　　5762 200407 13224.30 45398 http://www.it55.com it55学习IT知识，享受IT生活 4dfkjn

　　5762 200408 13376.47

　　5762 52039.62 免费网页模版下载http://www.it55.com

　　5763 200405 16649.78 http://www.it55.com/

　　5763 200406 17120.52

免费矢量图片素材下载http://www.it55.com

　　5763 200407 17487.49

　　5763 200408 17928.76 免费矢量图片素材下载http://www.it55.com

　　5763 69186.54

免费壁纸下载http://www.it55.com

　　5764 200405 12487.79

sflj http://www.it55.com kg^&fgd

　　5764 200406 13295.19 vd;k;l http://www.it55.com rdfg

　　5764 200407 13444.09 http://www.it55.com/

　　5764 200408 13929.69 vd;k;l http://www.it55.com rdfg

　　5764 53156.77

　　5765 200405 25057.74 免费壁纸下载http://www.it55.com

　　5765 200406 26058.46 it55.com

　　5765 200407 26301.88 免费网页模版下载http://www.it55.com

　　5765 200408 27130.64 精美商业网页模版下载http://www.it55.com

　　5765 104548.72 http://www.it55.com/

　　200405 79899.53 http://www.it55.com

　　200406 82588.15

　　200407 84168.03

　　200408 86501.34 sflj http://www.it55.com kg^&fgd

　　333157.05

http://www.it55.com/

　　30 rows selected.

　　Elapsed: 00:00:00.01

http://www.it55.com在线教程

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

http://www.it55.com在线教程

　　1 rollup 和 cube函数的再深入

免费设计素材下载http://www.it55.com

　　Quote: http://www.it55.com

　　从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,我们如何来区分到底是根据那个字段做的汇总呢,这时候,oracle的grouping函数就粉墨登场了. 精美韩国模版下载http://www.it55.com

　　如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0 http://www.it55.com/

　　1 select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code, 免费设计素材下载http://www.it55.com

　　2 decode(grouping(bill_month),1,'all month',bill_month) bill_month, 精美韩国模版下载http://www.it55.com

　　3 sum(local_fare) local_fare

　　4 from t 精美商业网页模版下载http://www.it55.com

　　5 group by cube(area_code,bill_month)

IT资讯之家 http://www.it55.com

　　6* order by area_code,bill_month nulls last 免费矢量图片素材下载http://www.it55.com

　　07:07:29 SQL> /

it55.com

　　AREA_CODE BILL_MONTH LOCAL_FARE vd;k;l http://www.it55.com rdfg

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

　　5761 200405 13060.43

　　5761 200406 13318.93

精美韩国模版下载http://www.it55.com

　　5761 200407 13710.27 免费设计素材下载http://www.it55.com

　　5761 200408 14135.78 45398 http://www.it55.com it55学习IT知识，享受IT生活 4dfkjn

　　5761 all month 54225.41

免费网页模版下载http://www.it55.com

　　5762 200405 12643.79

45398 http://www.it55.com it55学习IT知识，享受IT生活 4dfkjn

　　5762 200406 12795.06 精美韩国模版下载http://www.it55.com

　　5762 200407 13224.30 http://www.it55.com

　　5762 200408 13376.47

　　5762 all month 52039.62 vd;k;l http://www.it55.com rdfg

　　5763 200405 16649.78 免费矢量图片素材下载http://www.it55.com

　　5763 200406 17120.52 it55.com

　　5763 200407 17487.49

　　5763 200408 17928.76

　　5763 all month 69186.54

精美韩国模版下载http://www.it55.com

　　5764 200405 12487.79 http://www.it55.com

　　5764 200406 13295.19

　　5764 200407 13444.09 免费矢量图片素材下载http://www.it55.com

　　5764 200408 13929.69

　　5764 all month 53156.77

　　5765 200405 25057.74 精美韩国模版下载http://www.it55.com

　　5765 200406 26058.46 vd;k;l http://www.it55.com rdfg

　　5765 200407 26301.88

　　5765 200408 27130.64

　　5765 all month 104548.72

http://www.it55.com/

　　all area 200405 79899.53 http://www.it55.com

　　all area 200406 82588.15

免费壁纸下载http://www.it55.com

　　all area 200407 84168.03 免费壁纸下载http://www.it55.com

　　all area 200408 86501.34 http://www.it55.com/

　　all area all month 333157.05

　　30 rows selected.

　　Elapsed: 00:00:00.01

sflj http://www.it55.com kg^&fgd

　　07:07:31 SQL> 免费矢量图片素材下载http://www.it55.com

　　可以看到,所有的空值现在都根据grouping函数做出了很好的区分,这样利用rollup,cube和grouping函数,我们做数据统计的时候就可以轻松很多了. http://www.it55.com在线教程

（编辑：IT资讯之家 www.it55.com）