【函数】oracle窗口函数over()的理解

over()开窗函数和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
over（order by salary range between 50 preceding and 150 following）
每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
over（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）

可能细心的人会看到有range between和rows between两种写法，其实一开始我也挺困惑的，做了些实验，下面看一下。

SQL> select * from t1;

ID VALUE
---------- ----------
1 5
1 6
1 10
2 2
2 4
2 7

6 rows selected.

SQL> select id,value,
2 sum(value) over(partition by id order by value range between 1 preceding an
d 1 following) by_range,
3 sum(value) over(partition by id order by value rows between 1 preceding and
1 following) by_rows
4 from t1
5 order by id;

ID VALUE BY_RANGE BY_ROWS
---------- ---------- ---------- ----------
1 5 11 11
1 6 11 21 --这里5和6之间相差1 在RANGE内 因此把5和6相加得到11
1 10 10 16 --但是6和10之间相差4 在RANGE外 因此这里就显示出的是10 并没有和6相加
2 2 2 6
2 4 4 13
2 7 7 11 --2和4 4和7之间相差分别为2和3 均在RANGE范围外 因此都显示了当前行的原值

6 rows selected.

SQL> select id,value,
2 sum(value) over(partition by id order by value range between 4 preceding an
d 4 following) by_range,
3 sum(value) over(partition by id order by value rows between 1 preceding and
1 following) by_rows
4 from t1
5 order by id;

ID VALUE BY_RANGE BY_ROWS
---------- ---------- ---------- ----------
1 5 11 11
1 6 21 21
1 10 16 16
2 2 6 6
2 4 13 13
2 7 11 11 --增大了RANGE范围到4 这时得到的结果就和ROWS得到的相同了 因为在VALUE列中前后两行相差最大就是4

6 rows selected.

RANGE只指定了前后两个值之间相差值的范围，而ROWS则指定了前后多少行的范围。

警告：有些窗口函数强制要求对分区中的行排序。因此，对于有些窗口函数，ORDER BY子句是必需的。

当在窗口函数的OVER子句中使用ORDER BY子句时，就指定了两件事：

1. 分区中的行如何排序
2. 在计算中包含哪些行

SQL> select deptno,ename,hiredate,sal,
2 sum(sal) over(partition by deptno) sum1,
3 sum(sal) over(partition by deptno order by hiredate) sum2
4 from emp
5 where deptno=10;

DEPTNO ENAME HIREDATE SAL SUM1 SUM2
---------- ---------- --------------- ---------- ---------- ----------
10 CLARK 09-JUN-81 2450 8750 2450
10 KING 17-NOV-81 5000 8750 7450
10 MILLER 23-JAN-82 1300 8750 8750

SUM1中没有指定ORDER BY，计算出来的是部门10中3名员工的总工资。而在SUM2中使用了ORDER BY子句，其实下面两句效果相同：

sum(sal) over(partition by deptno order by hiredate)

sum(sal) over(partition by deptno order by hiredate range between unbounded preceding and current row)

下面再通过一个较为全面的例子，来展示一下ORDER BY中范围指定对查询输出的影响

SQL> select ename,sal,
2 min(sal) over(order by sal) min1,
3 max(sal) over(order by sal) max1,
4 min(sal) over(order by sal range between unbounded preceding and unbounded
following) min2,
5 max(sal) over(order by sal range between unbounded preceding and unbounded
following) max2,
6 min(sal) over(order by sal range between current row and current row) min3,

7 max(sal) over(order by sal range between current row and current row) max3,

8 max(sal) over(order by sal rows between 3 preceding and 3 following) max4
9 from emp;

ENAME SAL MIN1 MAX1 MIN2 MAX2 MIN3 MAX3 MAX4
---------- ----- ----- ----- ----- ----- ----- ---------- ----------
SMITH 800 800 800 800 5000 800 800 1250
JAMES 950 800 950 800 5000 950 950 1250
ADAMS 1100 800 1100 800 5000 1100 1100 1300
WARD 1250 800 1250 800 5000 1250 1250 1500
MARTIN 1250 800 1250 800 5000 1250 1250 1600
MILLER 1300 800 1300 800 5000 1300 1300 2450
TURNER 1500 800 1500 800 5000 1500 1500 2850
ALLEN 1600 800 1600 800 5000 1600 1600 2975
CLARK 2450 800 2450 800 5000 2450 2450 3000
BLAKE 2850 800 2850 800 5000 2850 2850 3000
JONES 2975 800 2975 800 5000 2975 2975 5000

ENAME SAL MIN1 MAX1 MIN2 MAX2 MIN3 MAX3 MAX4
---------- ----- ----- ----- ----- ----- ----- ---------- ----------
SCOTT 3000 800 3000 800 5000 3000 3000 5000
FORD 3000 800 3000 800 5000 3000 3000 5000
KING 5000 800 5000 800 5000 5000 5000 5000

14 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26892340/viewspace-722241/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26892340/viewspace-722241/