分析函数djWord文档格式.docx
- 文档编号:16746663
- 上传时间:2022-11-25
- 格式:DOCX
- 页数:27
- 大小:262.75KB
分析函数djWord文档格式.docx
《分析函数djWord文档格式.docx》由会员分享,可在线阅读,更多相关《分析函数djWord文档格式.docx(27页珍藏版)》请在冰豆网上搜索。
这是一个计算排名的分析函数,这里使用了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使用分析函数计算组值。
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,要注意起点必须在终点之前,按行从上到下的顺序。
注意是按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,'
)PRECEDING就是对当前行的日期向前推1年加上当前行的所有行为1个windows。
range中的value表达式是常量,正数表达式或时间间隔常量。
对于rows按物理行分组,很容易理解,value表达式只是行的物理位置,range逻辑分组有点不容易理解,下面分析一下,只需要注意,range中的value表达式指定的值是按照orderby排序的列来计算的,如按照range逻辑划分窗口的一个例子:
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 分析 函数 dj