分析及开窗函数

转自[url]http://hi.baidu.com/edgar108/blog/item/e24c7fd66b0817d7a144dfc8.html[/url]

我以oracle中的emp 和dept表为例，讲一下开窗函数。

假如，现在有这样的要求：查出所有的员工的名字ename,薪水sal 以及他的薪水占说有员工薪水的比例。
一开始，我们的思路可能是这样：
select ename ,sal ,sal/sum(sal) from emp;
但是这样写是不对的，sum()是一个单行统计函数，只返回一个值，不能和其他字段同时出现。
解决办法就是使用开窗函数over()
select ename ,sal ,sal/sum(sal) over() as percent from emp;
查询结果：
ENAME SAL PERCENT
---------- ---------- ----------
SMITH 800 .027562446
ALLEN 1600 .055124892
WARD 1250 .043066322
JONES 2975 .102497847
MARTIN 1250 .043066322
BLAKE 2850 .098191214
CLARK 2450 .084409991
SCOTT 3000 .103359173
KING 5000 .172265289
TURNER 1500 .051679587
ADAMS 1100 .037898363

ENAME SAL PERCENT
---------- ---------- ----------
JAMES 950 .032730405
FORD 3000 .103359173
MILLER 1300 .044788975

已选择14行。

上面的over是指把前面的函数(本例中是sum())当成开窗函数而不是统计函数，SQL标准允许讲所有的统计函数
用作开窗函数，使用over关键字来区分这两种用法。

上面的“sum(sal) over()”的意思是，对于每一条记录，都去计算一次sal的和。如果over关键字后的括号中的选项为空，

把上面的sql改进一下：
select ename ,sal ,'0'||round(sal/sum(sal) over(),3) as percent from emp;
查询结果：

ENAME SAL PERCENT
---------- ---------- -----------------------------------------
SMITH 800 0.028
ALLEN 1600 0.055
WARD 1250 0.043
JONES 2975 0.102
MARTIN 1250 0.043
BLAKE 2850 0.098
CLARK 2450 0.084
SCOTT 3000 0.103
KING 5000 0.172
TURNER 1500 0.052
ADAMS 1100 0.038

ENAME SAL PERCENT
---------- ---------- -----------------------------------------
JAMES 950 0.033
FORD 3000 0.103
MILLER 1300 0.045

已选择14行。

如果现在像查询每个员工的姓名ename，工资sal，以及他的工资占他所在部门的比例，按照上面的思路，这次要这样写：

select ename,deptno,sal,'0'|| round(sal/sum(sal) over(partition by deptno),3) from emp;

如果需要对sal排序，再partition by deptno 后面 再加上order by sal:

select ename,deptno,sal,'0'|| round(sal/sum(sal) over(partition by deptno order by sal),3) from emp;

ORDER BY 的完整语法为 ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2
RANGE 表示 按照值的范围进行范围的定义，而 ROWS 表示按照行的范围进行范围的定义

边界规则的取值见下表：

可取值 说明 示例

CURRENT ROW 当前行

N PRECEDING 前N行 2 PRECEDING

UNBOUNDED PRECEDING 一直到第一条记录

N FOLLOWING 后N行 2 FOLLOWING

UNBOUNDED FOLLOWING 一直到最后一条记录


但是，如果这样写，会报错：
select ename,deptno,sal,'0'|| round(sal/sum(sal) over(order by sal partition by deptno ),3) from emp;可能 order by不能写在partition by的前面。


如果现在按照员工的姓名排序，并计算工资的累加和：

select ename ,sal ,sum(sal) over(order by sal rows between unbounded preceding and current row) as result from emp;

order by sal rows between unbounded preceding and current row 的意思是： 按照sal进行排序，然后计算从第一行(unbounded preceding)到当前行
(current row)的和，这样的结果就是按照工资进行排序的工作值的累加和。

因为ROWS 表示按照行的范围进行范围的定义，所以计算从第一行到当前行的累加和。

如果把ROWS换成 RANGE :

select ename ,sal ,sum(sal) over(order by sal range between unbounded preceding and current row) as result from emp;

ENAME SAL RESULT
---------- ---------- ----------
SMITH 800 800
JAMES 950 1750 (800+950)
ADAMS 1100 2850 (800+950+1100)
WARD 1250 5350
MARTIN 1250 5350
MILLER 1300 6650
TURNER 1500 8150
ALLEN 1600 9750
CLARK 2450 12200
BLAKE 2850 15050
JONES 2975 18025

ENAME SAL RESULT
---------- ---------- ----------
SCOTT 3000 24025
FORD 3000 24025
KING 5000 29025

已选择14行。


RANGE 表示 按照值的范围进行范围的定义 ，在计算累加和的过程中，如果遇到相同的值(本例中为sal)，则计算所有的相同值同时累加
(本例中SCOTT,FORD的sal全是3000，所以值是 18025+3000+3000=24025)

select ename ,sal ,sum(sal) over(order by sal rows between 2 preceding and 2 following) as result from emp;

ENAME SAL RESULT
---------- ---------- ----------
SMITH 800 2850 (800+950+1100)
JAMES 950 4100
ADAMS 1100 5350
WARD 1250 5850
MARTIN 1250 6400
MILLER 1300 6900
TURNER 1500 8100 (1250+1300+1500+1600+2450)
ALLEN 1600 9700
CLARK 2450 11375
BLAKE 2850 12875
JONES 2975 14275 (2450+2850+2975+3000+3000)

ENAME SAL RESULT
---------- ---------- ----------
SCOTT 3000 16825
FORD 3000 13975
KING 5000 11000 (3000+3000+5000)

已选择14行。

sum(sal) over(order by sal rows between 2 preceding and 2 following)
按照sal进行排序，然后计算从当前行前两行(2 preceding) 到 当前行后两行(2 following)的累加和

对于第1行到第2行(n=2)，“前2行”是不存在或不完整的，所以按照前两行不存在或不完整来计算，最后2行类似。

select ename ,sal ,sum(sal) over(order by sal rows between 1 following and 3 following) as result from emp;

ENAME SAL RESULT
---------- ---------- ----------
SMITH 800 3300 (950+1100+1250)
JAMES 950 3600
ADAMS 1100 3800
WARD 1250 4050
MARTIN 1250 4400
MILLER 1300 5550
TURNER 1500 6900
ALLEN 1600 8275
CLARK 2450 8825
BLAKE 2850 8975
JONES 2975 11000

ENAME SAL RESULT
---------- ---------- ----------
SCOTT 3000 8000
FORD 3000 5000
KING 5000 (后面没有数据了，所以是NULL)

已选择14行。

计算的某一列后1行到后3行的值

select ename ,sal ,sum(sal) over(order by sal range between unbounded preceding and current row) as result from emp;
与
select ename ,sal ,sum(sal) over(order by sal) as result from emp;
是等价的。
也就是说 range between unbounded preceding and current row 是默认的定位方式。

select ename ,sal ,count(*) over(order by sal desc rows between unbounded preceding and current row) as result from emp;

ENAME SAL RESULT
---------- ---------- ----------
KING 5000 1
FORD 3000 2
SCOTT 3000 3
JONES 2975 4
BLAKE 2850 5
CLARK 2450 6
ALLEN 1600 7
TURNER 1500 8
MILLER 1300 9
WARD 1250 10
MARTIN 1250 11

ENAME SAL RESULT
---------- ---------- ----------
ADAMS 1100 12
JAMES 950 13
SMITH 800 14

已选择14行。
order by sal desc rows between unbounded preceding and current row 表示按照sal的降序排列，计算从第一行到当前行的个数，所以这个可以看作员工工资的排名。