分析及开窗函数

整理文档很辛苦,赏杯茶钱您下走!

免费阅读已结束,点击下载阅读编辑剩下 ...

阅读已结束,您可以下载文档离线阅读编辑

资源描述

转自我以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的降序排列,计算从第一行到当前行的个数,所以这个可以看作员工工资的排名。如果你颇有天赋,勤勉会天使更加完美;如果你能力平平,勤勉会补之不足!

1 / 7
下载文档,编辑使用

©2015-2020 m.777doc.com 三七文档.

备案号:鲁ICP备2024069028号-1 客服联系 QQ:2149211541

×
保存成功