oracle分析函数讲解.doc

一、 分析函数 概述:分析函数（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 表示字段的范围 举例说明:SELECT ename Ename, deptno Deptno, sal Sal, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal) Seq FROM emp ORDER BY deptno -------------------------- Ename,Deptno,Sal,Seq MILLER,10,1800.00,1 KING,10,5500.00,2 CLARK,10,12950.00,3 SMITH,20,1300.00,1 ADAMS,20,1600.00,2 SCOTT,20,3500.00,3 FORD,20,3500.00,4 JONES,20,13475.00,5 JAMES,30,1450.00,1 WARD,30,1750.00,2 MARTIN,30,1750.00,3 TURNER,30,2000.00,4 ALLEN,30,2100.00,5 BLAKE,30,13350.00,6 例子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()