Oracle常用分析函数说明.doc

Oracle分析函数

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

下面例子中使用的表来自Oracle自带的HR用户下的表，如果没有安装该用户，可以在SYS用户下运行$ORACLE_HOME/demo/schema/human_resources/hr_main.sql来创建。

除本文内容外，你还可参考：

ROLLUP与CUBE/post/419/29159

分析函数使用例子介绍：/post/419/44634

本文如果未指明，缺省是在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