Oracle分析函数

Oracle分析函数

以下内容基本来自《名师讲坛：Oracle开发实战经典》（李兴华、马云涛著），另外少许注释是我自己添加的，以其能方便理解和记忆。

一、基本语法

函数名（[参数,...]）OVER(

[PARTITION BY 字段]

[ORDER BY 字段 [ASC|DESC] [NULLS FIRST|NULLS LAST]]

[WINDOWING 子句]

);

a.函数名：如SUM()、COUNT()、ROW_NUMBER()等

b.OVER:为分析函数指明一个结果集，此语句在SELECT子句中使用。

c.PARTITION BY:将一个结果集分为N组（分区），而后按照不同的组对数据进行统计。缺省时默认全部数据为同一个分区。

d.ORDER BY:明确指明数据在每个组内的排列顺序，分析函数的结果集和排列顺序有关。

e.WINDOWING:缺省时为“RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”

二、PARTITION BY子句

   在分析函数中，最重要的概念就是将数据进行分区，而后才可以利用分析函数针对每一分区的数据进行操作。如果在定义时没有使用PARTITION BY子句分区，则表示全部的结果集将成为一组。

例子2.1：使用PARTITION BY子句。

SELECT deptno,
　　　　ename,
　　　　sal,
　　　　SUM(sal) OVER (PARTITION BY deptno) sum 
FROM emp;

例子2.2：不使用PARTITION BY子句进行分区，直接利用OVER子句操作。

SELECT deptno,ename,sal,SUM(sal) OVER() sum FROM emp;

例子2.3：通过PARTITION BY子句设置多个分区字段。

SELECT deptno,
　　　　ename,
　　　　sal,
　　　　job,
　　　　SUM(sal) OVER(PARTITION BY deptno,job) sum
FROM emp;

三、ORDER BY子句

　　ORDER BY子句用于设置在每个分区内数据的排序结果，排序结果将直接影响分析函数的计算结果。

例子3.1：设置多个排序字段。

SELECT deptno,
　　　　ename,
　　　　sal,
　　　　hiredate,
　　　　RANK() OVER(PARTITION BY deptno ORDER BY sal,hiredate DESC) rk
FROM emp;

例子3.2：直接利用ORDER BY排序所有数据。

SELECT deptno,ename,sal,SUM(sal) OVER(ORDER BY ename DESC) sum
FROM emp;

四、WINDOWING子句

　　分窗子句主要用于定义一个变化或固定的数据窗口方法，以及分析函数在操作行的集合，分窗子句有以下两种实现方式：

1.值域窗（RANGE WINDOW）,逻辑偏移。当前分区中当前行的前N行到当前行的记录集。（注：此处“N”的取值取决于统计函数所统计的字段的值和当前行直接的差距，详细例子见例子4.1）

2.行窗（ROWS WINDOW）,物理偏移。以排序的结果顺序计算偏移当前行的起始行记录集。（注：即指定数字N，则表示以排序结果顺序计算偏移当前行N行的记录集）

如果要指定RANGE或ROWS的偏移量，可以采用如下几种排序列：

a.RANGE|ROWS 数字 PRECEDING

b.RANGE|ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

c.RANGE|ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

其中，

a.PECEDING：“前面的”的意思，配合“RANGE|ROWS 数字 PRECEDING”中的数字表示符合条件的前N行。

b.BETWEEN...AND..:设置一个偏移量的范围。

c.UNBOUNDED:不限制边界。UNBOUNDED PRECEDING表示不设置前N行边界，UNBOUNDED FOLLOWING表示后N行边界。

d.CURRENT ROW:表示当前行。

e.FOLLOWING:表示当前行与下N行数据进行比较。

(一)、RANGE子句

例子4.1：在sal上设置偏移量

SELECT deptno,
　　　　ename,
　　　　sal,
　　　　SUM(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE 300 PRECEDING) sum 
FROM emp;

--300 PRECEDING：表示计算当前行之上的前N行与当前行的结果。

注：与ROWS子句不同，使用RANGE子句，相同的sal值对应的SUM(sal)值也是相同的。

例子4.2：设置偏移量为300，采用向下匹配方式处理

SELECT deptno,
　　　　ename,
　　　　sal,
　　　　SUM(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND 300 FOLLOWING) sum
FROM emp;

例子4.3：匹配当前行数据

SELECT deptno,
　　　　ename,
　　　　sal,
　　　　SUM(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND CURRENT ROW) sum
FROM emp;

例子4.4：使用UNBOUNDED不设边界

SELECT deptno,
　　　　ename,
　　　　sal,
　　　　SUM(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum
FROM emp;

此例由于设置了一个没有边界的范围，所以所有的数据都会在所在分区内进行SUM()函数的统计。

（二）、ROWS子句

例子4.5：设置两行物理偏移

SELECT deptno,
　　　　ename,
　　　　sal,
　　　　SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS 2 PRECEDING) sum
FROM emp;

注：与RANGE子句不同，即使sal值同为3000，统计函数SUM(sal)统计后的结果也是不一致的。

例子4.6：设置查询行的范围

SELECT deptno,
　　　　ename,
　　　　sal,
　　　　SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sum
FROM emp;

注：此处改成RANGE子句也会得到同样的结果。

Oracle分析函数

原文：http://www.cnblogs.com/OracleLayman/p/5240386.html