Oracle9i分析函数参考手册.docx
- 文档编号:10982391
- 上传时间:2023-02-24
- 格式:DOCX
- 页数:16
- 大小:190.34KB
Oracle9i分析函数参考手册.docx
《Oracle9i分析函数参考手册.docx》由会员分享,可在线阅读,更多相关《Oracle9i分析函数参考手册.docx(16页珍藏版)》请在冰豆网上搜索。
Oracle9i分析函数参考手册
Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。
下面例子中使用的表来自Oracle自带的HR用户下的表,如果没有安装该用户,可以在SYS用户下运行$ORACLE_HOME/demo/schema/human_resources/hr_main.sql来创建。
一、Oracle分析函数简介:
在日常的生产环境中,我们接触得比较多的是OLTP系统(即OnlineTransactionProcess),这些系统的特点是具备实时要求,或者至少说对响应的时间多长有一定的要求;其次这些系统的业务逻辑一般比较复杂,可能需要经过多次的运算。
比如我们经常接触到的电子商城。
在这些系统之外,还有一种称之为OLAP的系统(即OnlineAanalyseProcess),这些系统一般用于系统决策使用。
通常和数据仓库、数据分析、数据挖掘等概念联系在一起。
这些系统的特点是数据量大,对实时响应的要求不高或者根本不关注这方面的要求,以查询、统计操作为主。
分析函数运用举例
selectjob_id,
employee_id,
salary,
sum(salary)over(partitionbyjob_id)job_salary
fromhr.employees
groupbyjob_id,employee_id,salary
over(orderbysalary)按照salary排序进行累计,orderby是个默认的开窗函数
常用分析函数:
rank():
rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,而dense_rank则不会
dense_rank():
最适合做排名,dence_rank在并列关系是,相同等级不会跳过,rank则跳过
row_number():
row_number哪怕是两个数据完全相同,排名也按照行号排序
lag(col,n,0):
在同一行中显示前n行的数据.
lead(col,n,0):
在同一行中显示后n行的数据.
sum(col),avg(col),max(col),min(col):
一般后头跟over(partitionbycol1orderbycol2rangebetween1precedingand1following)
ratio_to_report(sum(col)):
一般后头跟over(partitionbycol1),计算各col1所占col总数的百分比
first_value(col),last_value(col):
一般后头跟over(orderbysum(col1)ascrowsunboundedpreceding),按照col1取最大或最小的col
selectstart_time,
ne_class,
sum(p.outoctiups)outoctiur,
lag(ne_class,1,0)over(orderbyne_class)ne_class_last,
dense_rank()over(orderbyne_class)rn,
sum(ne_class)over(partitionbystart_timeorderbystart_timerangebetween1precedingand1following)sum_outoctiups,
ratio_to_report(sum(p.outoctiups))over(partitionbystart_time)outoctiur_percent
fromp_rnc_atm_sum_infop
wherestart_time>sysdate-1/6
groupbystart_time,ne_class;
Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。
下面例子中使用的表来自Oracle自带的HR用户下的表,如果没有安装该用户,可以在SYS用户下运行$ORACLE_HOME/demo/schema/human_resources/hr_main.sql来创建。
少数几个例子需要访问SH用户下的表,如果没有安装该用户,可以在SYS用户下运行$ORACLE_HOME/demo/schema/sales_history/sh_main.sql来创建。
如果未指明缺省是在HR用户下运行例子。
开窗函数的的理解:
开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下:
over(orderbysalary)按照salary排序进行累计,orderby是个默认的开窗函数
over(partitionbydeptno)按照部门分区
over(orderbysalaryrangebetween50precedingand150following)每行对应的数据窗口是之前行幅度值不超过该霆的salary值-50,之后行幅度值不超过salary+150的个数
SELECTlast_name,salary,
COUNT(*)OVER()AScnt1,
COUNT(*)OVER(ORDERBYsalary)AScnt2,
COUNT(*)OVER(ORDERBYsalaryRANGEBETWEEN210PRECEDINGAND200FOLLOWING)AScnt3
FROMhr.employeesh;
over(orderbysalaryrowsbetween50precedingand150following)每行对应的数据窗口是之前50行,之后150行
over(orderbysalaryrowsbetweenunboundedprecedingandunboundedfollowing)每行对应的数据窗口是从第一行到最后一行,等效:
over(orderbysalaryrangebetweenunboundedprecedingandunboundedfollowing)
over中的partition为分组,orderby是视窗内排序,先执行partition然后orderby如partitionbycol_aorderbycol_b的执行排序效果类似于orderbycol_a,col_b这样的排序效果,如果再在最后加orderby,是在前边分组排序的结果基础上进行排序。
主要参考资料:
《expertone-on-one》TomKyte《Oracle9iSQLReference》第6章
AVG
功能描述:
用于计算一个组和数据窗口内表达式的平均值。
SAMPLE:
下面的例子中列c_mavg计算员工表中每个员工的平均薪水报告,该平均值由当前员工和与之具有相同经理的前一个和后一个三者的平均数得来;
SELECTmanager_id,last_name,hire_date,salary,
AVG(salary)OVER(PARTITIONBYmanager_idORDERBYhire_dateROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASc_mavg
FROMemployees;
MANAGER_IDLAST_NAME HIRE_DATE SALARY C_MAVG
----------------------------------------------------------------
100Kochhar 21-SEP-89 17000 17000
100DeHaan 13-JAN-93 17000 15000
100Raphaely 07-DEC-94 1100011966.6667
100Kaufling 01-MAY-95 790010633.3333
100Hartstein 17-FEB-96 130009633.33333
100Weiss 18-JUL-96 800011666.6667
100Russell 01-OCT-96 1400011833.3333
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_NUMBER CUM_CORR
-------------------------------
1
2 1
3.994309382
4.852040875
5.846652204
6.871250628
7.910029803
8.917556399
9.920154356
10.86720251
11.844864765
12.903542662
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_ID CUM_COVP CUM_COVS
-----------------------------------------
1774 103088 0
1775 103087 1473.25 2946.5
1794 1030961702.777782554.16667
1825 103093 1926.252568.33333
2004 103086 1591.4 1989.25
2005 103086 1512.5 1815
2416 1030881475.979591721.97619
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_ID CUM_COVP CUM_COVS
-----------------------------------------
1774 103088 0
1775 103087 1473.25 2946.5
1794 1030961702.777782554.16667
1825 103093 1926.252568.33333
2004 103086 1591.4 1989.25
2005 103086 1512.5 1815
2416 1030881475.979591721.97619
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(ORDERBYsalaryrangeBETWEEN51PRECEDINGAND150FOLLOWING)AScnt3
FROMhr.employeesh;
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_ID LAST_NAME SALARYCUME_DIST
-------------------------------------------------------
PU_CLERK Colmenares 2500 .2
PU_CLERK Himuro 2600 .4
PU_CLERK Tobias 2800 .6
PU_CLERK Baida 2900 .8
PU_CLERK Khoo 3100 1
PU_MAN Raphaely 11000 1
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_NAME SALARY DRANK
----------------------------------------------------------
60Lorentz 4200 1
60Austin 4800 2
60Pataballa 4800 2
60Ernst 6000 3
60Hunold 9000 4
90Kochhar 17000 1
90DeHaan 17000 1
90King 24000 2
FIRST_VALUE
功能描述:
返回组中数据窗口的第一个值。
SAMPLE:
下面例子计算按部门分区按薪水排序的数据窗口的第一个值对应的名字,如果薪水的第一个值有多个,则从多个对应的名字中取缺省排序的第一个名字
SELECTdepartment_id,last_name,salary,
FIRST_VALUE(last_name)OVER(PARTITIONBYdepartment_idORDERBYsalaryASC)ASlowest_sal
FROMemployees
WHEREdepartment_idin(20,30);
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
功能描述:
从DENSE_RANK返回的集合中取出排在最后面的一个值的行(可能多行,因为值可能相等),因此完整的语法需要在开始处加上一个集合函数以从中取出记录
SAMPLE:
下面例子中DENSE_RANK按部门分区,再按佣金commission_pct排序,FIRST取出佣金最低的对应的所有行,然后前面的MAX函数从这个集合中取出薪水最低的值;LAST取出佣金最高的对应的所有行,然后前面的MIN函数从这个集合中取出薪水最高的值
LAST_VALUE
功能描述:
返回组中数据窗口的最后一个值。
SAMPLE:
下面例子计算按部门分区按薪水排序的数据窗口的最后一个值对应的名字,如果薪水的最后一个值有多个,则从多个对应的名字中取缺省排序的最后一个名字
SELECTdepartment_id,last_name,salary,
LAST_VALUE(last_name)OVER(PARTITIONBYdepartment_idORDE
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle9i 分析 函数 参考手册