ORACLE 分析函数的应用.doc

分析函数的应用(更新版) 前言:1. 对原文重新排版,增强可读性2. 文中列举的例子都在scott/tiger用户下测试通过,增强可操作性，易验证。3. 继续增加:分析函数在美同统计系统中的应用，分析函数性能比较4. 欢迎大家批评指正. 概述:分析函数（oracle8.1.6以后版本支持），为解决Calculate a running total, Find percentages within a group, Top-N queries, Compute a moving average等问题而设计的。标准的PL/SQL可以解决大部分问题，然而性能不尽人意。分析函数就是标准SQL的语言插件，不仅编码简单，而且性能比纯SQL和PL/SQL好。分析函数已经获得ANSI SQL机构鉴定，增添到标准的SQL规范里。 分析函数如何工作的呢？：分析函数用来计算一组数据的集合的值。不同于每组返回多行的集合函数。一组多行数据的集合称为窗口，可以用分析子句实现界定。针对每行数据而言，定义了一个多行的移动的窗口.在计算当前行数据时候，窗口决定了计算多行数据的一个范围，窗口的尺寸由物理的行数或逻辑分割点（如：时间）决定。除了order by 语句外，分析函数是在查询语句中后最后执行的语句，所有的表连接(joins)，group by,having 语句都是在分析函数之前执行。故分析函数只出现在select和order by语句中。语法: Analytic-Function(Argument,Argument,...)OVER (Query-Partition-ClauseOrder-By-ClauseWindowing-Clause ) 分析函数:（黑体函数本文有例子解释） AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST,LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.CUBE,ROLLUP 参数：分析函数需要0到3的参数 partition by语句：根据partition by条件，逻辑上把一组结果集分为N组。关键字partition 和 group常常同时出现。分析函数独立应用于每组。 Order by 语句：在每组(分区)排序,作用于分析函数的结果集.Order by 后可以接以下语句：Asc|Desc 升序/降序Nulls First|Nulls Last 表示空值在排序中，置于开始还是最后 Windowsing-clause语句：Between … and 指定窗口范围的起点与终点Unbonded preceding 表示窗口的起点就是分区的第1行Unbonded following 表示窗口的终点就是分区的最后1行Current row 当前行Rows 表示行数范围Range 表示字段的范围 举例说明: 例子1: Calculate a running Total(累加) SELECT ename Ename, deptno Deptno, sal Sal,SUM(sal) OVER (ORDER BY deptno, ename) Running Total, --根据deptno,name排序后的结果集累加SUM(SAL) OVER (PARTITION BY deptno ORDER BY ename) Dept Total,--先deptno分区,再ename排序后结果集,然后累加ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ENAME) SeqFROM emp --行数统计ORDER BY deptno, ename Ename Deptno Sal Running Tota Dept Total SeqCLARK 10 2450.00 2450 2450 1KING 10 5000.00 7450 7450 2MILLER 10 1300.00 8750 8750 3ADAMS 20 1100.00 9850 1100 1FORD 20 3000.00 12850 4100 2JONES 20 2975.00 15825 7075 3SCOTT 20 3