分析及开窗函数

转自我以oracle中的emp和dept表为例，讲一下开窗函数。假如，现在有这样的要求：查出所有的员工的名字ename,薪水sal以及他的薪水占说有员工薪水的比例。一开始，我们的思路可能是这样：selectename,sal,sal/sum(sal)fromemp;但是这样写是不对的，sum()是一个单行统计函数，只返回一个值，不能和其他字段同时出现。解决办法就是使用开窗函数over()selectename,sal,sal/sum(sal)over()aspercentfromemp;查询结果：ENAMESALPERCENT------------------------------SMITH800.027562446ALLEN1600.055124892WARD1250.043066322JONES2975.102497847MARTIN1250.043066322BLAKE2850.098191214CLARK2450.084409991SCOTT3000.103359173KING5000.172265289TURNER1500.051679587ADAMS1100.037898363ENAMESALPERCENT------------------------------JAMES950.032730405FORD3000.103359173MILLER1300.044788975已选择14行。上面的over是指把前面的函数(本例中是sum())当成开窗函数而不是统计函数，SQL标准允许讲所有的统计函数用作开窗函数，使用over关键字来区分这两种用法。上面的“sum(sal)over()”的意思是，对于每一条记录，都去计算一次sal的和。如果over关键字后的括号中的选项为空，把上面的sql改进一下：selectename,sal,'0'||round(sal/sum(sal)over(),3)aspercentfromemp;查询结果：ENAMESALPERCENT-------------------------------------------------------------SMITH8000.028ALLEN16000.055WARD12500.043JONES29750.102MARTIN12500.043BLAKE28500.098CLARK24500.084SCOTT30000.103KING50000.172TURNER15000.052ADAMS11000.038ENAMESALPERCENT-------------------------------------------------------------JAMES9500.033FORD30000.103MILLER13000.045已选择14行。如果现在像查询每个员工的姓名ename，工资sal，以及他的工资占他所在部门的比例，按照上面的思路，这次要这样写：selectename,deptno,sal,'0'||round(sal/sum(sal)over(partitionbydeptno),3)fromemp;如果需要对sal排序，再partitionbydeptno后面再加上orderbysal:selectename,deptno,sal,'0'||round(sal/sum(sal)over(partitionbydeptnoorderbysal),3)fromemp;ORDERBY的完整语法为ORDERBY字段名RANGE|ROWSBETWEEN边界规则1AND边界规则2RANGE表示按照值的范围进行范围的定义，而ROWS表示按照行的范围进行范围的定义边界规则的取值见下表：可取值说明示例CURRENTROW当前行NPRECEDING前N行2PRECEDINGUNBOUNDEDPRECEDING一直到第一条记录NFOLLOWING后N行2FOLLOWINGUNBOUNDEDFOLLOWING一直到最后一条记录但是，如果这样写，会报错：selectename,deptno,sal,'0'||round(sal/sum(sal)over(orderbysalpartitionbydeptno),3)fromemp;可能orderby不能写在partitionby的前面。如果现在按照员工的姓名排序，并计算工资的累加和：selectename,sal,sum(sal)over(orderbysalrowsbetweenunboundedprecedingandcurrentrow)asresultfromemp;orderbysalrowsbetweenunboundedprecedingandcurrentrow的意思是：按照sal进行排序，然后计算从第一行(unboundedpreceding)到当前行(currentrow)的和，这样的结果就是按照工资进行排序的工作值的累加和。因为ROWS表示按照行的范围进行范围的定义，所以计算从第一行到当前行的累加和。如果把ROWS换成RANGE:selectename,sal,sum(sal)over(orderbysalrangebetweenunboundedprecedingandcurrentrow)asresultfromemp;ENAMESALRESULT------------------------------SMITH800800JAMES9501750(800+950)ADAMS11002850(800+950+1100)WARD12505350MARTIN12505350MILLER13006650TURNER15008150ALLEN16009750CLARK245012200BLAKE285015050JONES297518025ENAMESALRESULT------------------------------SCOTT300024025FORD300024025KING500029025已选择14行。RANGE表示按照值的范围进行范围的定义，在计算累加和的过程中，如果遇到相同的值(本例中为sal)，则计算所有的相同值同时累加(本例中SCOTT,FORD的sal全是3000，所以值是18025+3000+3000=24025)selectename,sal,sum(sal)over(orderbysalrowsbetween2precedingand2following)asresultfromemp;ENAMESALRESULT------------------------------SMITH8002850(800+950+1100)JAMES9504100ADAMS11005350WARD12505850MARTIN12506400MILLER13006900TURNER15008100(1250+1300+1500+1600+2450)ALLEN16009700CLARK245011375BLAKE285012875JONES297514275(2450+2850+2975+3000+3000)ENAMESALRESULT------------------------------SCOTT300016825FORD300013975KING500011000(3000+3000+5000)已选择14行。sum(sal)over(orderbysalrowsbetween2precedingand2following)按照sal进行排序，然后计算从当前行前两行(2preceding)到当前行后两行(2following)的累加和对于第1行到第2行(n=2)，“前2行”是不存在或不完整的，所以按照前两行不存在或不完整来计算，最后2行类似。selectename,sal,sum(sal)over(orderbysalrowsbetween1followingand3following)asresultfromemp;ENAMESALRESULT------------------------------SMITH8003300(950+1100+1250)JAMES9503600ADAMS11003800WARD12504050MARTIN12504400MILLER13005550TURNER15006900ALLEN16008275CLARK24508825BLAKE28508975JONES297511000ENAMESALRESULT------------------------------SCOTT30008000FORD30005000KING5000(后面没有数据了，所以是NULL)已选择14行。计算的某一列后1行到后3行的值selectename,sal,sum(sal)over(orderbysalrangebetweenunboundedprecedingandcurrentrow)asresultfromemp;与selectename,sal,sum(sal)over(orderbysal)asresultfromemp;是等价的。也就是说rangebetweenunboundedprecedingandcurrentrow是默认的定位方式。selectename,sal,count(*)over(orderbysaldescrowsbetweenunboundedprecedingandcurrentrow)asresultfromemp;ENAMESALRESULT------------------------------KING50001FORD30002SCOTT30003JONES29754BLAKE28505CLARK24506ALLEN16007TURNER15008MILLER13009WARD125010MARTIN125011ENAMESALRESULT------------------------------ADAMS110012JAMES95013SMITH80014已选择14行。orderbysaldescrowsbetweenunboundedprecedingandcurrentrow表示按照sal的降序排列，计算从第一行到当前行的个数，所以这个可以看作员工工资的排名。如果你颇有天赋,勤勉会天使更加完美;如果你能力平平,勤勉会补之不足!