oracle分析函数 介绍

《oracle分析函数 介绍》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。下面例子中使用的表来自Oracle自带的HR用户下的表，如果没有安装该用户，可以在SYS用户下运行$ORACLE_HOME/demo/schema/human_resources/hr_main.sql来创建。除本文内容外，你还可参考：ROLLUP与CUBEhttp://xsb.itpub.net/post/419/29159分析函数使用例子介绍：http://xsb.itpub.net/post/419/44634本文如果未指明，缺省是在H

2、R用户下运行例子。开窗函数的的理解：开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：over（orderbysalary）按照salary排序进行累计，orderby是个默认的开窗函数over（partitionbydeptno）按照部门分区over（orderbysalaryrangebetween50precedingand150following）每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150over（orderbysalaryrowsbetween50precedingand150following）每行对应的数据窗口

3、是之前50行，之后150行over（orderbysalaryrowsbetweenunboundedprecedingandunboundedfollowing）每行对应的数据窗口是从第一行到最后一行，等效：over（orderbysalaryrangebetweenunboundedprecedingandunboundedfollowing）主要参考资料：《expertone-on-one》TomKyte《Oracle9iSQLReference》第6章AVG功能描述：用于计算一个组和数据窗口内表达式的平均值。SAMPLE：下面的例子中列c_mavg计算员工表中每个员工的平均薪水报告，该

4、平均值由当前员工和与之具有相同经理的前一个和后一个三者的平均数得来；SELECTmanager_id,last_name,hire_date,salary,AVG(salary)OVER(PARTITIONBYmanager_idORDERBYhire_dateROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASc_mavgFROMemployees;MANAGER_IDLAST_NAMEHIRE_DATESALARYC_MAVG----------------------------------------------------------------100Kochh

5、ar21-SEP-891700017000100DeHaan13-JAN-931700015000100Raphaely07-DEC-941100011966.6667100Kaufling01-MAY-95790010633.3333100Hartstein17-FEB-96130009633.33333100Weiss18-JUL-96800011666.6667100Russell01-OCT-961400011833.3333CORR功能描述：返回一对表达式的相关系数，它是如下的缩写：COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP

6、(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_CORRFROMsaless,timestWHEREs.time_id=t.

7、time_idANDcalendar_year=1998GROUPBYt.calendar_month_numberORDERBYt.calendar_month_number;CALENDAR_MONTH_NUMBERCUM_CORR-------------------------------1213.9943093824.8520408755.8466522046.8712506287.91