分析函数dj.docx

分析函数dj.docx

《分析函数dj.docx》由会员分享，可在线阅读，更多相关《分析函数dj.docx（27页珍藏版）》请在冰豆网上搜索。

分析函数dj

11.分析函数（AnalyticalFunctions）

11.1背景知识

分析函数是oracle8.1.6加入的，oracle后续版本扩展了分析函数的数目。

分析函数可以分为四类：

排名函数（ranking），聚合函数（aggregate），行比较函数（rowcomparison），统计函数（statistical）。

本节内容主要围绕这四个方面讲解分析函数的原理，特点，使用场合，注意点等。

对常用分析函数重点分析，一般的分析函数作为了解简单介绍。

使用分析函数的好处很多,可以解决复杂问题的分析，代替复杂的查询操作，比如没有分析函数之前，我们要解决一个复杂的问题，可能需要用到自连接，子查询或内嵌视图，甚至可能要使用存储过程，但是使用了分析函数之后，一句简单的sql就可以解决这些问题，而且oracle对分析函数做了内部的优化处理，性能比使用复杂查询有很大的提升。

11.2分析函数基础

11.2.1分析函数基本原理

Oracle分析函数（AnalyticFunction）依赖于对行的分组，是对行进行分组之后，计算这些分组的值。

所以，分析函数是对查询的结果集按照分析子句和分析函数的规则来进行进一步的操作。

首先按照分析子句对行进行分组（大组），然后扫描各个分组，被扫描到的当前行会对应于一个分组内的滑动的窗口（小组）之内，当前行对应的这个滑动的窗口范围由分析子句决定，这个范围可以是物理度量的也可以是逻辑度量的，最后对当前行对应的窗口使用分析函数，计算这个组（小组）的值。

所以分析函数对于各个组可以产生多个分组的值，而聚合函数对于各个分组只能产生唯一值，这是两者之间的典型区别。

名词：

分析子句（analyticclause）,最多由三个部分组成，依次顺序是partition子句，orderby子句，window子句。

partition子句根据分区键确定如何对行进行分区，如果没有partition子句，那么所有行为一个分区。

orderby子句确定每个分区内的排序规则。

Window子句，确定当前行对应的窗口范围，从而用分析函数计算当前行对应的窗口的值，如果没有window子句，而有orderby那么默认窗口范围是当前分区内的首行到当前行，如果没有orderby，那么默认是当前分区内的首行到末行。

如：

selectid,last_name,salary,dept_id,row_number（）over（partitionbydept_idorderbyid）froms_emp;

这是一个计算排名的分析函数，这里使用了partition子句对行进行分区（大组,如果没有partition子句，那么整个行是一个大组）,partition子句按照部门编号分为多个大组。

Orderby子句确定组内的排序,然后对各个大组扫描，row_number没有window子句，那么当前行的窗口范围就是首先在一个分区之内，然后当前行的分析函数计算范围是该组第一行到当前行。

对这个范围使用分析函数，计算组的值。

结果如下：

可以看出，id为4的组只有一行，所以分析函数的结果为1，id为3的和id为11的同组。

Id=3的计算为1，id=11的窗口对应范围在id=3到当前行，所以计算结果为2。

分析函数用于计算排名，累积值，移动值，中间值，平均值，输出集合报表等。

常用于复杂的分析，比如在数据仓库系统中进行OLAP（On-LineAnalyticalProcessing）操作，使用分析函数可以更好地提供对决策的支持。

分析函数限制和注意点：

分析函数是在一个查询中最后计算的，除了orderby（在语句最后orderby之前），所以在同一个查询层次中,分析函数只可能出现在orderby，select中，其他where,groupby,having等处不能出现分析函数。

当然分析函数还有其他的限制，比如不能嵌套，以及其他的使用，比如可以用于子查询等，这将在后面详细分析。

另外要注意一点，分析函数是在一个查询中最后计算的，除了orderby。

所以分析函数是在orderby之前和select显示结果之前（from之后）计算好的，在同一个查询层次中只能出现在orderby和select中，不能出现在where，groupby，having等其他地方，但是如果一个查询含有嵌套查询，比如where中有子查询，那么这个子查询我们可以使用分析函数。

所以说上述限制只是在同一层次的查询中，对子查询不使用这个限制。

还要注意一点，分析函数既然是最后计算的，在orderby之前，如果一个select中有多个分析函数，最后没有orderby显示排序的话，可能前面的分析函数会出现乱序的情况，从左到右后面的那个分析函数会重新组织前面分析函数的结果顺序，但是不改变其查询的结果,所以对于多个分析函数的处理结果，一般还需要显示orderby一下，另外分析子句中的orderby只是组内排序，而不是最终结果排序，当然没有partition的时候是对最终结果排序，另外分析子句中的orderby如果出现键值相同的话不保证排序，详细见后面说明。

例如：

selectid,last_name,salary,sum（salary）over（orderbyid,last_name）,sum（salary）over（orderbylast_name,id）froms_emp;

这条语句使用了两个看似相同的分析函数，但是内部排序规则不一样，第2个分析函数的结果会对整个结果重新排序，先按照last_name，再按照id排序，所以第1个是先按id，再按last_name排序的结果不变，但是位置发生了变化，由第2个指定了排序规则，下面看这个结果：

由上面的图可以看出，最终结果按照第2个分析函数排序，这两个分析函数的最大值都是31377，第1个分析函数的最大值id为25，因为先按照id排序，id为25也是所有id最大的，第2个按last_name先排序，那么velasquez这个last_name也是最大的。

如果显示指定orderby，则最终按照orderby的排序情况重新组织结果。

下面先用一个实例来说明一下分析函数的基本工作情况：

SQL>descemployees

NameTypeNullableDefaultComments

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

MANAGE_IDNUMBER（10）Y上级经理ID

LAST_NAMEVARCHAR2（10）Ylast_name

HIRE_DATEDATEY雇佣日期

SALARYNUMBER（10,2）Y薪水值

问题：

将雇员employees按照对应的经理分组，并且按照hire_date从小到大排序，前面加上序号，每个对应经理的初始序号为1，然后递增？

解决：

使用分析函数row_number（），按照条件分组排序确定计算范围的window，然后对window使用分析函数计算组值。

SQL>selectrow_number（）over（partitionbymanage_idorderbyhire_date）rm,manage_id,last_name,hire_date,salaryfromemployees;

RMMANAGE_IDLAST_NAMEHIRE_DATESALARY

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

11dj42008/6/33000.00

21dj22008/6/44000.00

31dj12008/6/53000.00

12wj42008/6/33000.00

22wj22008/6/44000.00

32wj12008/6/53000.00

下面简单分析一下这句sql：

11.2.2分析函数语法结构

要使用分析函数，首先必须了解分析函数的语法结构，基本语法结构如下图所示：

分析函数语法结构图

从上图可以看出，分析函数是由函数名，参数，over关键字，over后面的括号内指定分析子句，分析子句可能由partition子句，orderby子句，window子句按顺序排列组成。

分析函数的语法含义和使用注意点：

参数：

1.分析函数的参数：

0-3个参数，是指分析函数接受数字类型或可以隐性转为数字类型的非数字类型。

自动转为数字类型的规则根据数字类型的优先级确定，oracle数字类型的优先级别依次是binarydoublebinaryfloatnumber。

所以最先转为binarydouble类型。

另外分析函数的返回值也是数字类型。

（除个别接受其他类型参数，如first_value和last_value）

分析子句：

2.通过over关键字来区分分析函数是操作一个查询结果集。

也就是说，分析函数是在from，where，groupby，having之后才开始工作的。

出现在最后orderby和select之前，分析函数也可以用于子查询，用于过滤父查询的查询结果。

分析函数只允许出现在orderby和select中，只是针对同一级查询。

3.分析函数不能嵌套。

意思是在同一个分析函数中，分析子句中不能再使用分析函数，但是可以将分析函数放到子查询中，然后父查询的分析函数操作这个子查询的分析函数的返回结果。

4.分析子句顺序是partition子句orderby子句window子句。

其中有的分析函数必须有orderby子句，另外有window子句必须要有orderby子句。

还有其他的一些限制，后面详细介绍。

当然有的分析函数也可以不需要分析子句，但是意义不大。

如：

selectid,last_name,salary,dept_id,sum（salary）over（）froms_emp;--正确的

相当于所有行为一组，没有顺序，窗口范围是首行到末行，所以分析函数对所有行的处理结果一致，等于于sum（salary）。

5.用户自定义分析函数user-definedaggregatefuntion，也可以通过over关键字确定。

实现更强大的自定义处理功能。

本章将举例分析。

6.通过partition子句来将查询结果集分组，可以通过多个value_exp来确定，如果不指定partition，则把查询结果集当成单个组，相当于partitionnull，分析函数中使用partitionby后面不能有括号，带括号的是在model查询和outerjoin中使用，多个partition表达式之间用逗号隔开。

Partition表达式可以是常量，表列，非分析函数，函数表达式或前面的任意组合。

7.若被查询的对象具有并行性，并且分析函数包含partition子句，那么分析函数的计算也是并行的。

8.orderby子句，有的分析函数必须要有orderby，如row_number函数，有的不需要orderby，有orderby如没有window子句，则表示对当前行计算的窗口范围是组的首行到当前行，相当于betweenunboundedprecedingandcurrentrow。

如果没有orderby，则对当前行的计算范围是组的首行到末行，相当于betweenunboundedprecedingandunbounedfollowing,同组的行计算结果一样，当然，如果有window子句，必须要有orderby。

Orderby子句确定了组内的排序情况，如果没有指定partition，则是所有行的排序情况，如果有partition，则是partition之后的每个组内部的排序情况，orderby可以接受多个排序键值，除了PERCENTILE_CONT和PERCENTILE_DISC（他们只能取唯一的键值排序）。

如果最后查询结果需要一定的顺序，则在最后显示指定orderby，因为分区子句的orderby只保证组内有序，特别是有多个分析函数的时候，从左到右，后面的会覆盖前面的排序结果，所以最后要显示orderby，当然具体情况具体考虑。

当orderby使用多个排序表达式的时候，对于排名函数尤其重要。

因为orderby有键值相同的话不保证排序，我们常多加一个rowid，这样能保证组内有序。

如果使用了orderby之后，仍然存在相同的行，那么分析函数对于相同行的计算结果一致（大部分函数，如rank函数，但是对有些函数比如row_number函数会对相同行任意分配递增值，是唯一的，参考后面介绍）。

Orderby限制：

9.orderby之后必须是表达式，对于sibling关键字是非法的，只对层次查询有效。

位置指定和别名指定也是非法的，其他和普通查询一致。

10.如果在windows子句中使用range逻辑划分窗口范围，并且orderby中是多个键值表达式排序，那么窗口的范围必须是下列三种情况：

a.betweenunboundedprecedingandcurrentrow--相当于没有写window，因为orderby默认就是组的首行到当前行

b.betweencurrentrowandunboundedfollowing

c.betweenunboundedprecedingandunboundedfollowing–相当于没有写orderby，表示是组的首行到组的末行

为什么有这个限制？

因为range是按照排序键值和后面的窗口范围确定计算范围的，如果有多个排序键值的话，不知道根据什么计算，所以不能有具体的范围,比如1preceding等。

rows没有这个限制。

使用range，对于orderby是单个键值表达式排序，也没有这个限制。

asc和desc指定排序规则是升序还是降序，默认升序，nullsfirst和nullslast是指定对null是出现在首行还是末行，默认升序情况是nullslast，把null当成最大的处理。

这个对于普通的orderby也是有效的。

Window子句：

11.只能在orderby之后指定window子句，有的分析函数允许有window子句，有的不允许，有windows子句，必须要有orderby子句，后面具体说明。

12.window的范围通过Rows或range关键字指定。

Rows表示物理偏移量，range表示逻辑偏移量。

用rows或range划分窗口，按照起点在上，终点在下的原则，如果违反这个原则，则分析函数的计算结果为null。

然后对窗口中的每一行应用分析函数计算结果。

对于range的限制可以查看规则10。

当使用range的时候，根据orderby中的value确定的逻辑偏移量来计算。

Range对于分析函数的计算结果总是确定的，而rows有可能产生不确定值。

如果是rows，orderby之后的value如果有重复，有可能产生不确定值，因为有可能需要多个value保证排序唯一。

（在排名（ranking）函数中要特别注意）。

参考规则8。

对于窗口：

如果有between…and…，那么between后的是窗口的起点，and后是窗口的终点。

如果无between…and…，那么表示的只是起点，终点是当前行。

相当于between…andcurrentrow.

如只有rows/rangeunboundedpreceding，则表示开始节点是组的首行，结束节点是当前行。

等同于：

Rows/rangebetweenunboundedprecedingandcurrentrow.

等同于没有写，只有orderby，也就是说当只有orderby的时候，相当于从组的首行计算到当前行。

没有between。

。

。

and。

。

。

的话，只能指定起点，不能指定终点，终点是默认当前行。

如果直接写

Rowsunboundedfollowing将报缺失表达式错误，起点不能是unboundedfollowing，值能是valuefollowing。

终点不能是unboundedpreceding，它只能用于起点。

起点若是valuefollowing，那么终点必须也是valuefollowing，这两个value值可以不一样。

终点value要大于起点的value，否则就反序找，那么返回null。

如果终点是valuepreceding，那么起点必须是valuepreceding。

值可以不一样，但是当终点的value小于起点的value，则分析函数返回null。

所以终点是preceding定义的，那么终点的value必须小于起点的value，如果起点是following，那么终点的value必须大于起点的value。

如果起点valuepreceding,终点可以是unboundedfollowing,currentrow,valuefollowing,valuepreceding的任何一个，但要注意如果终点也是valuepreceding的话，终点的value应该比起点的value要小。

否则结果为null。

如果起点是currentrow，那么终点不能是valuepreceding。

规则：

如果没有between，window子句只确定起点，终点默认当前行;如果有between，要注意起点必须在终点之前，按行从上到下的顺序。

否则结果为null。

注意是按rows物理行划分窗口还是range逻辑划分窗口。

Rows中的value表达式必须是正值。

Range是按orderby中排序的逻辑值划分窗口，所以如果使用指定的value表达式划分窗口范围，那么orderby中只能指定一个排序键值，而且若value表达式是数字类型，那么orderby排序的键值必须是数字或date类型，若value是时间间隔，那么orderby必须是date类型，而且可能需要使用到时间间隔转换函数，参考oracle10greferences：

NUMTOYMINTERVALandNUMTODSINTERVAL

如：

显示在当前雇员雇佣日期一年之内的员工的总薪水。

SELECTlast_name,start_date,salary,SUM（salary）

OVER（ORDERBYstart_date

RANGENUMTOYMINTERVAL（1,'year'）PRECEDING）ASt_sal

FROMs_emp;

上面的NUMTOYMINTERVAL（1,'year'）PRECEDING就是对当前行的日期向前推1年加上当前行的所有行为1个windows。

range中的value表达式是常量，正数表达式或时间间隔常量。

对于rows按物理行分组，很容易理解，value表达式只是行的物理位置，range逻辑分组有点不容易理解，下面分析一下，只需要注意，range中的value表达式指定的值是按照orderby排序的列来计算的，如按照range逻辑划分窗口的一个例子：

SQL>selectmanage_id,last_name,hire_date,salary,avg（salary）over（partitionbymanage_idorderbyhire_daterangebetween1precedingand2following）avg_salfromemployees;

--range按照hire_date计算，那么就是表示日期是当前行日期小一天到当前行日期大两天结束。

MANAGE_IDLAST_NAMEHIRE_DATESALARYAVG_SAL

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

1dj42008/6/33000.003333.33333

1dj22008/6/44000.003550

1dj12008/6/53000.004050

1dj32008/6/64200.004550

1dj52008/6/75000.005066.66666

1dj62008/6/86000.005500

2wj42008/6/33000.003333.33333

2wj22008/6/44000.003550

2wj12008/6/53000.004050

2wj32008/6/64200.004550

2wj52008/6/75000.005066.66666

2wj62008/6/86000.005500

3hj42008/6/33000.003333.33333

3hj22008/6/44000.003550

3hj12008/6/53000.004050

3hj32008/6/64200.004550

3hj52008/6/75000.005066.66666

3hj62008/6/86000.005500

3hj72008/9/41004.001004

avg（salary）over（partitionbymanage_idorderbyhire_daterangebetween1precedingand2following）avg_sal

含义是：

按照manage_id分组，并且按照hire_date排序，range确定一个组中的当前行的对应窗口计算范围是当前行的hire_date小一天到大两天结束。

再如：

selectid,last_name,salary,dept_id,sum（salary）over（orderbysalaryrangebetweencurrentrowand350following）froms_emp;

表示按照salary排序，那么窗口范围根据salary和value确定，当前行对应的窗口范围是当前行（或者叫当前salary）到比当前salary大350的行结束。

注意orderby如果排序产生重复行，则重复行结果相同，如下面的id为25的那行的salary为1100和id为8的salary也为1100的计算结果相同。

11.3分析函数的使用

11.3.1分析函数概览

注意keep的使用。

我们浏览下所有的分析函数（可能后续版本会增加分析函数，这里我们只是列出10g的），其中带（*）的表示可以带window子句。

其中黄色的表示常用分析函数。

--可以带window的

AVG*

CORR*

COVAR_POP*

COVAR_SAMP*

COUNT*

FIRST_VALUE*

LAST_VALUE*

MAX*

MIN*

REGR（LinearRegressions）Functions*

STDDEV*

STDDEV_POP*

STDDEV_SAMP*

SUM*

VAR_POP*

VAR_SAMP*

VARIANCE*

--不可以带windo