Oracle常用分析函数说明.docx
《Oracle常用分析函数说明.docx》由会员分享，可在线阅读，更多相关《Oracle常用分析函数说明.docx（28页珍藏版）》请在冰豆网上搜索。
Oracle常用分析函数说明
Oracle分析函数
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
下面例子中使用的表来自Oracle自带的HR用户下的表，如果没有安装该用户，可以在SYS用户下运行$ORACLE_HOME/demo/schema/human_resources/hr_main.sql来创建。
除本文内容外，你还可参考：
ROLLUP与CUBE
分析函数使用例子介绍：
本文如果未指明，缺省是在HR用户下运行例子。
开窗函数的的理解：
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over（orderbysalary）按照salary排序进行累计，orderby是个默认的开窗函数
over（partitionbydeptno）按照部门分区
over（orderbysalaryrangebetween50precedingand150following）
每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
over（orderbysalaryrowsbetween50precedingand150following）
每行对应的数据窗口是之前50行，之后150行
over（orderbysalaryrowsbetweenunboundedprecedingandunboundedfollowing）
每行对应的数据窗口是从第一行到最后一行，等效：
over（orderbysalaryrangebetweenunboundedprecedingandunboundedfollowing）
主要参考资料：
《expertone-on-one》TomKyte《Oracle9iSQLReference》第6章
1）.AVG
功能描述：
用于计算一个组和数据窗口内表达式的平均值。
SAMPLE：
下面的例子中列c_mavg计算员工表中每个员工的平均薪水报告，该平均值由当前员工和与之具有相同经理的前一个和后一个三者的平均数得来；
SELECTmanager_id,last_name,hire_date,salary,
AVG（salary）OVER（PARTITIONBYmanager_idORDERBYhire_date
ROWSBETWEEN1PRECEDINGAND1FOLLOWING）ASc_mavg
FROMemployees;
MANAGER_IDLAST_NAMEHIRE_DATESALARYC_MAVG
----------------------------------------------------------------
100Kochhar21-SEP-891700017000
100DeHaan13-JAN-931700015000
100Raphaely07-DEC-941100011966.6667
100Kaufling01-MAY-95790010633.3333
100Hartstein17-FEB-96130009633.33333
100Weiss18-JUL-96800011666.6667
100Russell01-OCT-961400011833.3333
2）.CORR
功能描述：
返回一对表达式的相关系数，它是如下的缩写：
COVAR_POP（expr1,expr2）/STDDEV_POP（expr1）*STDDEV_POP（expr2））
从统计上讲，相关性是变量之间关联的强度，变量之间的关联意味着在某种程度
上一个变量的值可由其它的值进行预测。
通过返回一个-1~1之间的一个数,相关
系数给出了关联的强度，0表示不相关。
SAMPLE：
下例返回1998年月销售收入和月单位销售的关系的累积系数（本例在SH用户下运行）
SELECTt.calendar_month_number,
CORR（SUM（s.amount_sold）,SUM（s.quantity_sold））
OVER（ORDERBYt.calendar_month_number）asCUM_CORR
FROMsaless,timest
WHEREs.time_id=t.time_idANDcalendar_year=1998
GROUPBYt.calendar_month_number
ORDERBYt.calendar_month_number;
CALENDAR_MONTH_NUMBERCUM_CORR
-------------------------------
1
21
3.994309382
4.852040875
5.846652204
6.871250628
7.910029803
8.917556399
9.920154356
10.86720251
11.844864765
12.903542662
3）.COVAR_POP
功能描述：
返回一对表达式的总体协方差。
SAMPLE：
下例CUM_COVP返回定价和最小产品价格的累积总体协方差
SELECTproduct_id,supplier_id,
COVAR_POP（list_price,min_price）
OVER（ORDERBYproduct_id,supplier_id）ASCUM_COVP,
COVAR_SAMP（list_price,min_price）
OVER（ORDERBYproduct_id,supplier_id）ASCUM_COVS
FROMproduct_informationp
WHEREcategory_id=29
ORDERBYproduct_id,supplier_id;
PRODUCT_IDSUPPLIER_IDCUM_COVPCUM_COVS
-----------------------------------------
17741030880
17751030871473.252946.5
17941030961702.777782554.16667
18251030931926.252568.33333
20041030861591.41989.25
20051030861512.51815
24161030881475.979591721.97619
.
.
4）.COVAR_SAMP
功能描述：
返回一对表达式的样本协方差
SAMPLE：
下例CUM_COVS返回定价和最小产品价格的累积样本协方差
SELECTproduct_id,supplier_id,
COVAR_POP（list_price,min_price）
OVER（ORDERBYproduct_id,supplier_id）ASCUM_COVP,
COVAR_SAMP（list_price,min_price）
OVER（ORDERBYproduct_id,supplier_id）ASCUM_COVS
FROMproduct_informationp
WHEREcategory_id=29
ORDERBYproduct_id,supplier_id;
PRODUCT_IDSUPPLIER_IDCUM_COVPCUM_COVS
-----------------------------------------
17741030880
17751030871473.252946.5
17941030961702.777782554.16667
18251030931926.252568.33333
20041030861591.41989.25
20051030861512.51815
24161030881475.979591721.97619
.
.
5）.COUNT
功能描述：
对一组内发生的事情进行累积计数，如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；可以使用DISTINCT来记录去掉一组中完全相同的数据后出现的行数。
SAMPLE：
下面例子中计算每个员工在按薪水排序中当前行附近薪水在[n-50,n+150]之间的行数，n表示当前行的薪水
例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行没有，所以count计数值cnt3为2（包括自己当前行）；cnt2值相当于小于等于当前行的SALARY值的所有行数
SELECTlast_name,salary,COUNT（*）OVER（）AScnt1,
COUNT（*）OVER（ORDERBYsalary）AScnt2,
COUNT（*）OVER（ORDERBYsalaryRANGEBETWEEN50PRECEDING
AND150FOLLOWING）AScnt3FROMemployees;
LAST_NAMESALARYCNT1CNT2CNT3
-----------------------------------------------------------------
Olson210010713
Markle220010732
Philtanker220010732
Landry240010758
Gee240010758
Colmenares25001071110
Patel25001071110
.
.
6）.CUME_DIST
功能描述：
计算一行在组中的相对位置，CUME_DIST总是返回大于0、小于或等于1的数，该数表示该行在N行中的位置。
例如，在一个3行的组中，返回的累计分布值为1/3、2/3、3/3
SAMPLE：
下例中计算每个工种的员工按薪水排序依次累积出现的分布百分比
SELECTjob_id,last_name,salary,CUME_DIST（）
OVER（PARTITIONBYjob_idORDERBYsalary）AScume_dist
FROMemployeesWHEREjob_idLIKE'PU%';
JOB_IDLAST_NAMESALARYCUME_DIST
-------------------------------------------------------
PU_CLERKColmenares2500.2
PU_CLERKHimuro2600.4
PU_CLERKTobias2800.6
PU_CLERKBaida2900.8
PU_CLERKKhoo31001
PU_MANRaphaely110001
7）.DENSE_RANK
功能描述：
根据ORDERBY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。
组内的数据按ORDERBY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。
每次ORDERBY表达式的值发生变化时，该序列也随之增加。
有同样值的行得到同样的数字序号（认为null时相等的）。
密集的序列返回的时没有间隔的数
SAMPLE：
下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与RANK函数的区别）
SELECTd.department_id,e.last_name,e.salary,DENSE_RANK（）
OVER（PARTITIONBYe.department_idORDERBYe.salary）asdrank
FROMemployeese,departmentsd
WHEREe.department_id=d.department_id
ANDd.department_idIN（'60','90'）;
DEPARTMENT_IDLAST_NAMESALARYDRANK
----------------------------------------------------------
60Lorentz42001
60Austin48002
60Pataballa48002
60Ernst60003
60Hunold90004
90Kochhar170001
90DeHaan170001
90King240002
8）.FIRST
功能描述：
从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录
SAMPLE：
下面例子中DENSE_RANK按部门分区，再按佣金commission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最高的值
SELECTlast_name,department_id,salary,
MIN（salary）KEEP（DENSE_RANKFIRSTORDERBYcommission_pct）
OVER（PARTITIONBYdepartment_id）"Worst",
MAX（salary）KEEP（DENSE_RANKLASTORDERBYcommission_pct）
OVER（PARTITIONBYdepartment_id）"Best"
FROMemployees
WHEREdepartment_idin（20,80）
ORDERBYdepartment_id,salary;
LAST_NAMEDEPARTMENT_IDSALARYWorstBest
--------------------------------------------------------------------
Fay206000600013000
Hartstein2013000600013000
Kumar806100610014000
Banda806200610014000
Johnson806200610014000
Ande806400610014000
Lee806800610014000
Tuvault807000610014000
Sewall807000610014000
Marvins807200610014000
Bates807300610014000
.
.
.
9）.FIRST_VALUE
功能描述：
返回组中数据窗口的第一个值。
SAMPLE：
下面例子计算按部门分区按薪水排序的数据窗口的第一个值对应的名字，如果薪水的第一个值有多个，则从多个对应的名字中取缺省排序的第一个名字
SELECTdepartment_id,last_name,salary,FIRST_VALUE（last_name）
OVER（PARTITIONBYdepartment_idORDERBYsalaryASC）ASlowest_sal
FROMemployees
WHEREdepartment_idin（20,30）;
DEPARTMENT_IDLAST_NAMESALARYLOWEST_SAL
--------------------------------------------------------------
20Fay6000Fay
20Hartstein13000Fay
30Colmenares2500Colmenares
30Himuro2600Colmenares
30Tobias2800Colmenares
30Baida2900Colmenares
30Khoo3100Colmenares
30Raphaely11000Colmenares
10）.LAG
功能描述：
可以访问结果集中的其它行而不用进行自连接。
它允许去处理游标，就好像游标是一个数组一样。
在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。
Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行），其相反的函数是LEAD
SAMPLE：
下面的例子中列prev_sal返回按hire_date排序的前1行的salary值
SELECTlast_name,hire_date,salary,
LAG（salary,1,0）OVER（ORDERBYhire_date）ASprev_sal
FROMemployees
WHEREjob_id='PU_CLERK';
LAST_NAMEHIRE_DATESALARYPREV_SAL
-------------------------------------------------------
Khoo18-5月-9531000
Tobias24-7月-9728003100
Baida24-12月-9729002800
Himuro15-11月-9826002900
Colmenares10-8月-9925002600
11）.LAST
功能描述：
从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录
SAMPLE：
下面例子中DENSE_RANK按部门分区，再按佣金commission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最高的值
SELECTlast_name,department_id,salary,
MIN（salary）KEEP（DENSE_RANKFIRSTORDERBYcommission_pct）
OVER（PARTITIONBYdepartment_id）"Worst",
MAX（salary）KEEP（DENSE_RANKLASTORDERBYcommission_pct）
OVER（PARTITIONBYdepartment_id）"Best"
FROMemployees
WHEREdepartment_idin（20,80）
ORDERBYdepartment_id,salary;
LAST_NAMEDEPARTMENT_IDSALARYWorstBest
--------------------------------------------------------------------
Fay206000600013000
Hartstein2013000600013000
Kumar806100610014000
Banda806200610014000
Johnson806200610014000
Ande806400610014000
Lee806800610014000
Tuvault807000610014000
Sewall807000610014000
Marvins807200610014000
Bates807300610014000
.
12）.LAST_VALUE
功能描述：
返回组中数据窗口的最后一个值。
SAMPLE：
下面例子计算按部门分区按薪水排序的数据窗口的最后一个值对应的名字，如果薪水的最后一个值有多个，则从多个对应的名字中取缺省排序的最后一个名字
SELECTdepartment_id,last_name,salary,LAST_VALUE（last_name）
OVER（PARTITIONBYdepartment_idORDERBYsalary）AShighest_sal
FROMemployees
WHEREdepartment_idin（20,30）;
DEPARTMENT_IDLAST_NAMESALARYHIGHEST_SAL
------------------------------------------------------------
20Fay6000Fay
20Hartstein13000Hartstein
30Colmenares2500Colmenares
30Himuro2600Himuro
30Tobias2800Tobias
30Baida2900Baida
30Khoo3100Khoo
30Raphaely11000Raphaely
13）.LEAD
功能描述：
LEAD与LAG相反，LEAD可以访问组中当前行之后的行。
Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行）
SAMPLE：
下面的例子中每行的"NextHired"返回按hire_date排序的下一行的hire_date值
SELECTlast_name,hire_date,
LEAD（hire_date,1）OVER（ORDERBYhire_date）AS"NextHired"
FROMemployeesWHEREdepartment_id=30;
LAST_NAMEHIRE_DATENextHired
-------------------------------------------
Raphaely07-DEC-9418-MAY-95
Khoo18-MAY-9524-JUL-97
Tobias24-JUL-9724-DEC-97
Baida24-D
升级会员 