oracle分析函数等高级sql.doc

Oracle 9i 分析函数参考手册? ? ? ? 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（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数over（partition by deptno）按照部门分区over（order by salary range between 50 preceding and 150 following）每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150over（order by salary rows between 50 preceding and 150 following）每行对应的数据窗口是之前50行，之后150行over（order by salary rows between unbounded preceding and unbounded following）每行对应的数据窗口是从第一行到最后一行，等效：over（order by salary range between unbounded preceding and unbounded following）主要参考资料：《expert one-on-one》 Tom Kyte??《Oracle9i SQL Reference》第6章AVG 功能描述：用于计算一个组和数据窗口内表达式的平均值。SAMPLE：下面的例子中列c_mavg计算员工表中每个员工的平均薪水报告，该平均值由当前员工和与之具有相同经理的前一个和后一个三者的平均数得来；SELECT manager_id, last_name, hire_date, salary,? ?AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ? ?ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg? ?FROM employees;MANAGER_ID LAST_NAME? ?? ?? ?? ?? ???HIRE_DATE? ???SALARY? ???C_MAVG---------- ------------------------- --------- ---------- ----------? ?? ? 100 Kochhar? ?? ?? ?? ?? ?? ? 21-SEP-89? ?? ?17000? ?? ?17000? ?? ? 100 De Haan? ?? ?? ?? ?? ?? ? 13-JAN-93? ?? ?17000? ?? ?15000? ?? ? 100 Raphaely? ?? ?? ?? ?? ?? ?07-DEC-94? ?? ?11000 11966.6667? ?? ? 100 Kaufling? ?? ?? ?? ?? ?? ?01-MAY-95? ?? ? 7900 10633.3333? ?? ? 100 Hartstein? ?? ?? ?? ?? ???17-FEB-96? ?? ?13000 9633.33333? ?? ? 100 Weiss? ?? ?? ?? ?? ?? ?? ?18-JUL-96? ?? ? 8000 11666.6667? ?? ? 100 Russell? ?? ?? ?? ?? ?? ? 01-OCT-96? ?? ?14000 11833.3333...CORR 功能描述：返回一对表达式的相关系数，它是如下的缩写：? ?? ?? ? COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2))? ?? ?? ? 从统计上讲，相关性是变量之间关联的