转自http://hi.baidu.com/edgar108/blog/item/e24c7fd66b0817d7a144dfc8
我以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
---------- ---------- ----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
ENAME
---------- ---------- ----------
JAMES
FORD
MILLER
已选择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
---------- ---------- -----------------------------------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
ENAME
---------- ---------- -----------------------------------------
JAMES
FORD
MILLER
已选择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
UNBOUNDED PRECEDING
N 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
---------- ---------- ----------
SMITH
JAMES
ADAMS
WARD
MARTIN
MILLER
TURNER
ALLEN
CLARK
BLAKE
JONES
ENAME
---------- ---------- ----------
SCOTT
FORD
KING
已选择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
---------- ---------- ----------
SMITH
JAMES
ADAMS
WARD
MARTIN
MILLER
TURNER
ALLEN
CLARK
BLAKE
JONES
ENAME
---------- ---------- ----------
SCOTT
FORD
KING
已选择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
---------- ---------- ----------
SMITH
JAMES
ADAMS
WARD
MARTIN
MILLER
TURNER
ALLEN
CLARK
BLAKE
JONES
ENAME
---------- ---------- ----------
SCOTT
FORD
KING
已选择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
---------- ---------- ----------
KING
FORD
SCOTT
JONES
BLAKE
CLARK
ALLEN
TURNER
MILLER
WARD
MARTIN
ENAME
---------- ---------- ----------
ADAMS
JAMES
SMITH
已选择14行。
order by sal desc rows between unbounded preceding and current row 表示按照sal的降序排列,计算从第一行到当前行的个数,所以这个可以看作员工工资的排名。