Oracle开发专题之：分析函数资源-CSDN文库

需积分: 2 183 浏览量 2013-03-22 14:22:33 上传评论收藏 974KB DOC 举报

资源推荐

资源详情

资源评论

Oracle 开发专题之：分析函数(OVER)...........................................................................................................................1

Oracle 开发专题之：分析函数 2(Rank, Dense_rank, row_number)...............................................................................6

Oracle 开发专题之：分析函数 3(Top/Bottom N、First/Last、NTile).........................................................................10

Oracle 开发专题之：窗口函数.......................................................................................................................................15

Oracle 开发专题之：报表函数.......................................................................................................................................20

Oracle 开发专题之：分析函数总结..............................................................................................................................22

Oracle 开发专题之：26 个分析函数..............................................................................................................................25

PLSQL 开发笔记和小结.................................................................................................................................................29

分析函数简述..................................................................................................................................................................61

说明: 1)Oracle 开发专题 99%收集自: http://www.blogjava.net/pengpenglin/(偶补充了一点点 1%);

2) PLSQL 开发笔记和小结收集自 http://www.blogjava.net/cheneyfree/

3)分析函数简述收集自 http://space.itpub.net/7607759/

昆明小虫 http://ynlxc.cnblogs.com/ 收集,并补充了一点点 1%

Oracle 开发专题之：分析函数(OVER)



 



分析函数简介 

 



分析函数简单实例 

 分析函数 



 



解析 

一、 分析函数简介：

在日常的生产环境中，我们接触得比较多的是  系统即 ，这些系统的特点是具备实时

要求，或者至少说对响应的时间多长有一定的要求；其次这些系统的业务逻辑一般比较复杂，可能需要经过多次的运算。

比如我们经常接触到的电子商城。

在这些系统之外，还有一种称之为  的系统即 ，这些系统一般用于系统决策使用。通常

和数据仓库、数据分析、数据挖掘等概念联系在一起。这些系统的特点是数据量大，对实时响应的要求不高或者根本不关

注这方面的要求，以查询、统计操作为主。

我们来看看下面的几个典型例子：

 查找上一年度各个销售区域排名前  的员工

 按区域查找上一年度订单总额占区域订单总额 以上的客户

 查找上一年度销售最差的部门所在的区域

 查找上一年度销售最好和最差的产品

我们看看上面的几个例子就可以感觉到这几个查询和我们日常遇到的查询有些不同，具体有：

 需要对同样的数据进行不同级别的聚合操作

 需要在表内将多条数据和同一条数据进行多次的比较

 需要在排序完的结果集上进行额外的过滤操作

分析函数语法

 !"#$%&'()$%&'(***

+#,

$-&($.-/-&($0.1%&(

例2

&'34/.4./'1!

&' 就是函数名

是分析函数的参数)每个函数有 56 个参数)参数可以是表达式)例如2&'7''

3是一个关键字)用于标识分析函数)否则查询分析器不能区别 &'聚集函数和 &'分析函数

4/.4是可选的分区子句)如果不存在任何分区子句)则全部的结果集可看作一个单一的大区

./'是可选的 ./子句)有些函数需要它)有些则不需要*依靠已排序数据的那些函数)如2用于访问结果集

中前一行和后一行的 8 和 #9)必须使用)其它函数)如 +8)则不需要*在使用了任何排序的开窗函数时)该子句是强制性

的)它指定了在计算分析函数时一组内的数据是如何排序的*

 子句

,# 提供了 : 个分析函数)按功能分 ; 类

分析函数分类

等级<%函数2用于寻找前  种查询

开窗1.1%函数2用于计算不同的累计)如 ="))+8)" )"> 等)作用于数据的一个窗口上

例2

&'*3./*.4)*'&%!)

&'*34/*.4./*'.4'!

制表4%函数2与开窗函数同名)作用于一个分区或一组上的所有列

例2

&'*3&%!)

&'*34/*.4.4'!

制表函数与开窗函数的关键不同之处在于  语句上缺少一个  子句

8)#9 函数2这类函数允许在结果集中向前或向后检索值)为了避免数据的自连接)它们是非常用用的*

+,!)+,!=")=9#+!# 及线性的衰减函数2计算任何未排序分区的统计值

  子句

按照表达式分区就是分组)如果省略了分区子句)则全部的结果集被看作是一个单一的组

 子句

分析函数中 ,9#,?@A 的存在将添加一个默认的开窗子句)这意味着计算中所使用的行的集合是当前分区中当前行和前面

所有行)没有 ,9#,@A 时)默认的窗口是全部的分区在 ./子句后可以添加 !"##$)如2./''.

&???表示排序时忽略 '' 列为空的行*???

%&&' 子句

用于定义分析函数将在其上操作的行的集合

0.1% 子句给出了一个定义变化或固定的数据窗口的方法)分析函数将对这些数据进行操作

默认的窗口是一个固定的窗口)仅仅在一组的第一行开始)一直继续到当前行)要使用窗口)必须使用 ,9#,@A 子句

根据  个标准可以建立窗口2数据值的范围,8#=或与当前行的行偏移量*

(!) 窗口

,%;4.%2将产生一个滑动窗口)他在组中拥有当前行以前 ; 行的集合

8# 窗口仅对 "@#,= 和 9#= 起作用)因为不可能从 +,B, 中增加或减去  个单元

另外的限制是 ,9#,@A 中只能有一列)因而范围实际上是一维的)不能在  维空间中

例2

3%*3./*C.%4.%统计前  天平均工资

*+, 窗口

利用 ,0 分区)就没有 ,8# 分区那样的限制了)数据可以是任何类型)且 ,9#,@A可以包括很多列

-./0120!) 窗口

@9#9,##9 82这个窗口从当前分区的每一行开始)并结束于正在处理的当前行

,,#,02该窗口从当前行开始并结束

&'#D4,##9 82对该窗口从当前行之前的数字表达式&'#D4的行开始)对 ,8# 来

说)从从行序值小于数字表达式的当前行的值开始*

&'#D40 82该窗口在当前行 &'#D4 行之后的行终止或开始)且从行序值大于当

前行 &'#D4 行的范围开始或终止

%/14.%.E1%2当前行  前)当前后  后

注意分析函数允许你对一个数据集进排序和筛选3这是 .45 从来不能实现的除了最后的 672 子句之外3分析函数是

在查询中执行的最后的操作集3这样的话3就不能直接在谓词中使用分析函数3即不能在上面使用 ,8 或 890!) 子句

二、 分析函数简单实例：

下面我们通过一个实际的例子：按区域查找上一年度订单总额占区域订单总额 以上的客户，来看看分析函数的应用。

【】测试环境：

=F(?.?.!'4G

?'??????????????????????????&H????4

?-----------------------?--------?----------------

?=!@,?????????????????????:(

?,#8 ! 9????????????????????:(

?=#=#,=! 9????????:(

?A#,???????????????????????????????:%

?"B??????????????????????????:

?!,9#,=????????????????:-

?!=#=??????????????????:)

【】测试数据：

=F(??I?E'?.!'4G

??=!@,??,#8 ! 9?=#=#,=! 9???????A#,??????"B?!,9#,=??!=#=

----------?----------?--------------?----------?----------?----------?----------

??????????????????-???????????????????????????????????;;??????????-????????????????;%

?????????%??????????(??????????????%????????????????????????;;?????????;???????????????-<;

?????????-??????????*??????????????-????????????????????????;;???????????????????????????-(;

????????;??????????*??????????????<????????????????????????;;??????????????????????????*=

????????;??????????*??????????????-????????????????????????;;??????????????????????????%*%

????????(??????????-????????????????????????????????????;;;??????????(??????????*?????????%

??????????????????-??????????????=???????????????????????;;;??????????*????????????????(;*(<

???????????????????(??????????????????????????????????????;;;??????????????????????????%%%=%

???????????????????(??????????????????????????????????????;;;??????????=????????????????-%<*%

???????????????????(??????????????%?????????????????????????;;;??????????????????????????(;*;

???????????????????(??????????????%????????????????????????;;;??????????%??????????%???????*%(%

???????????????????(??????????????????????????????????????;;;?????????;??????????%??????((<;

?????????%??????????(??????????????%????????????????????????;;;?????????????????????????==;

?1?.*

【6】测试语句：

=F(??*&!/?&')

???????????*%!.?%)

???????????&'*!?&!)

??%?????????&'&'*!?34?/?*%!.?%!

??(????E'?.!'4?

??*???1C?*?J?;;

??-???%&4?/?*%!.)?*&!/G

??="#,?????,#8 ?=!=#=?,#8 !=#=

----------?----------?----------?------------

?????????%?????????????(??????-<;????????-<;

?????????-?????????????*???????-(;????????*<;*(

????????;????????????*??????*%(????????*<;*(

????????????????????-??????;%????????;%

三、分析函数  解析：

请注意上面的绿色高亮部分，%&4/ 的意图很明显：将数据按区域 9，客户进行分组，那么 3 这一部分有什么用呢？

假如我们只需要统计每个区域每个客户的订单总额，那么我们只需要 %&4/*%!.)*&!/ 就够了。但我们

还想在每一行显示该客户所在区域的订单总额，这一点和前面的不同：需要在前面分组的基础上按区域累加。很显然

%&4/ 和 &' 是无法做到这一点的因为聚集操作的级别不一样，前者是对一个客户，后者是对一批客户。

这就是 +9 函数的作用了！它的作用是告诉 .45 引擎：按区域对数据进行分区，然后累积每个区域每个客户的订单总额

>#"?>#"?>+$+$@##。

现在我们已经知道  年度每个客户及其对应区域的订单总额，那么下面就是筛选那些个人订单总额占到区域订单总额

以上的大客户了

=F(??I

??????E'??*&!/?&')

???????????????????*%!.?%)

??%?????????????????&'*!?&!)

??(?????????????????&'&'*!?34?/?*%!.?%!

??*????????????E'?.!'4?

??-???????????1C?*?J?;;

??<???????????%&4?/?*%!.)?*&!/?!

??=???1C?!*&!?(?!*%!?I?;G

??="#,?????,#8 ?=!=#=?,#8 !=#=

----------?----------?----------?------------

?????????%??????????(??????-<;????????-<;

????????;??????????*??????*%(????????*<;*(

??????????????????-??????;%????????;%

=F(?

现在我们已经知道这些大客户是谁了！哦，不过这还不够，如果我们想要知道每个大客户所占的订单比例呢？看看下面的

=F 语句，只需要一个简单的 ,&. 函数就搞定了。

=F(??!*I)

???????????;;ABA+"!6>"#$@##ACA)0+!@##3AADDAEFEA!$

??????E'??*&!/?&')

??%?????????????????*%!.?%)

??(?????????????????&'*!?&!)

??*?????????????????&'&'*!?34?/?*%!.?%!

??-????????????E'?.!'4?

??<???????????1C?*?J?;;

??=???????????%&4?/?*%!.)?*&!/?!

?;???1C?!*&!?(?!*%!?I?;G

??="#,?????,#8 ?=!=#=?,#8 !=#=?#,#

----------?----------?----------?------------?----------------------------------------

?????????%????????????(?????????????????-<;????????-<;???;;

????????;??????????*?????????????????*%(????????*<;*(?????=%

??????????????????-?????????????????;%????????;%???;;

=F(?

总结：

剩余63页未读，继续阅读

评论收藏

内容反馈

战歌IT

粉丝: 122
资源: 2394

Oracle开发专题之：分析函数

ORACLE分析函数

oracle分析函数

oracle 中的分析函数

oracle 分析函数详解（有例子）

Oracle专题培训.docx

D52163GC10 Oracle Database 11g: SQL Tuning Workshop

最有价值的27个Oracle数据库学习专题

ORACLE分析函数大全

深入浅出Oracle分析函数

ORACLE分析函数详解

深入浅出oracle分析函数(全)

oracle分析函数及开窗函数

oracle数据库应用与开发.docx

Oracle数据库应用技巧专题

ORACLE 常用sql专题讲解

Oracle电子教案、Oracle课堂笔记.rar

Oracle数据库11g之SQL.zip

Oracle 分析函数.doc

oracle的分析函数over 及开窗函数

常用Oracle分析函数大全

oracle分析函数，窗口函数，报表函数

Oracle开发之分析函数简介Over用法

mapx与oraclespatial

oracle sql_tuning

Oracle数据库应用技巧专题.rar

Oracle11g性能优化

ORACLE数据库培训质料

最新资源