Oracle 分析函数的使用

2016-02-19 18:47 3 1 收藏

只要你有一台电脑或者手机，都能关注图老师为大家精心推荐的Oracle 分析函数的使用，手机电脑控们准备好了吗？一起看过来吧！

【 tulaoshi.com - 编程语言】

　　分析函数是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 set autot on

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

　　06:43:50 2 from t

　　06:43:51 3 group by area_code

　　06:43:57 4 union all

　　06:44:00 5 select '合计' area_code,sum(local_fare) local_fare

　　06:44:06 6 from t

　　06:44:08 7 /

　　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.03

　　Execution Plan

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

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

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

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

　　503 bytes 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

来源:http://www.tulaoshi.com/n/20160219/1618965.html

上一篇：在Delphi中开发使用多显示器的应用程序
下一篇：吊在一棵树上的女生

看过《Oracle 分析函数的使用》的人还看了以下文章更多>>

Oracle 下医嘱执行函数

标签：编程语言网络编程

//*********************************************************************** //函数名称：of_zy_yzfy for n_medicare_minihis //功能说明：医嘱录入后执行时提交医嘱 //函数参数： // 1.S_yb_zy_yzfy astr_jyxx 医嘱费用信息 //函数返回： (BOOLEAN) //作者：annicybc 时...

SQLServer和Oracle的常用函数对比

标签： SQLServer

1.绝对值 S:select abs(-1) value O:select abs(-1) value from dual 2.取整(大) S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual 3.取整（小） S:select floor(-1.001) value O:select floor(-1.001) value from dual 4.取整（截取） S:select cast(-1.002 as int) value O:select trunc(-1.002) value from du...

Oracle 数据对象分析

标签：编程语言网络编程

过程和函数过程和函数都以编译后的形式存放在数据库中，函数可以没有参数也可以有多个参数并有一个返回值。过程有零个或多个参数，没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值，函数和过程的主要区别不在于返回值，而在于他们的调用方式。过程是作为一个独立执行语句调用的： pay_involume(invoic...

Java调用Oracle的过程和函数

标签：编程语言网络编程

内容或简介： /** 调用数据库里的一个函数一个函数本质上一个返回一个结果的存储过程，这个例子示范了怎么调用有in、out和in/out参数的函数 ***********************************/ CallableStatement cs; try { // 调用一个没有参数的函数; 函数返回 a VARCHAR // 预处理callable语句 cs = connection.prepareCall("{？ = ca...

理解和使用Oracle 8i分析工具－LogMiner

标签：编程语言网络编程

Oracle LogMiner 是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具，使用该工具可以轻松获得Oracle 重作日志文件（归档日志文件）中的具体内容，特别是，该工具可以分析出所有对于数据库操作的DML（insert、update、delete等）语句，另外还可分析得到一些必要的回滚SQL语句。该工具特别适用于调试、审计或者回退某个特定的事务。...

查看更多精彩>>