二、理解over()函数 |
您所在的位置：网站首页 › sqlserver的over函数 › 二、理解over()函数 |
二、理解over()函数
|
1.1、两个order by的执行时机分析函数是在整个sql查询结束后(sql语句中的order by的执行比较特殊)再进行的操作, 也就是说sql语句中的order by也会影响分析函数的执行结果： a) 两者一致：如果sql语句中的order by满足分析函数分析时要求的排序，那么sql语句中的排序将先执行,分析函数在分析时就不必再排序；b) 两者不一致：如果sql语句中的order by不满足分析函数分析时要求的排序，那么sql语句中的排序将最后在分析函数分析结束后执行排序。
1.2、分析函数中的分组/排序/窗口 分析函数包含三个分析子句：分组(partition by)， 排序(order by)， 窗口(rows) 窗口就是分析函数分析时要处理的数据范围，就拿sum来说,它是sum窗口中的记录而不是整个分组中的记录，因此我们在想得到某个栏位的累计值时，我们需要把窗口指定到该分组中的第一行数据到当前行, 如果你指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个sum值都会一样，即整个组的总和。 窗口子句在这里我只说rows方式的窗口，range方式和滑动窗口也不提。 窗口子句中我们经常用到指定第一行，当前行，最后一行这样的三个属性。第一行是 unbounded preceding，当前行是 current row，最后一行是 unbounded following，窗口子句不能单独出现，必须有order by子句时才能出现，如: last_value(sal) over(partition by deptno order by sal rows between unbounded preceding and unbounded following)以上示例指定窗口为整个分组。而出现order by子句的时候，不一定要有窗口子句，但效果会很不一样，此时的窗口默认是当前组的第一行到当前行！
当省略窗口子句时:a) 如果存在order by则默认的窗口是unbounded preceding and current row --当前组的第一行到当前行b) 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following --整个组 如果省略分组,则把全部记录当成一个组:a) 如果存在order by则默认窗口是unbounded preceding and current row --当前组的第一行到当前行b) 如果这时省略order by则窗口默认为unbounded preceding and unbounded following --整个组
1.3、帮助理解over()的实例 例1：关注点：sql无排序，over()排序子句省略 SELECT DEPTNO, EMPNO, ENAME, SAL, LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO)FROM EMP;运行结果：
例2：关注点：sql无排序，over()排序子句有，窗口省略 SELECT DEPTNO, EMPNO, ENAME, SAL, LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) FROM EMP; 运行结果：
例3：关注点：sql无排序，over()排序子句有，窗口也有，窗口特意强调全组数据 SELECT DEPTNO, EMPNO, ENAME, SAL, LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL FROM EMP; 运行结果：
例4：关注点：sql有排序（正序），over()排序子句无，先做sql排序再进行分析函数运算 SELECT DEPTNO, MGR, ENAME, SAL, HIREDATE, LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO) LAST_VALUE FROM EMP WHERE DEPTNO = 30 ORDER BY DEPTNO, MGR; 运行结果：
例5：关注点：sql有排序（倒序），over()排序子句无，先做sql排序再进行分析函数运算 SELECT DEPTNO, MGR, ENAME, SAL, HIREDATE, LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO) LAST_VALUE FROM EMP WHERE DEPTNO = 30 ORDER BY DEPTNO, MGR DESC; 运行结果：
例6：关注点：sql有排序（倒序），over()排序子句有，窗口子句无，此时的运算是：sql先选数据但是不排序，而后排序子句先排序并进行分析函数处理（窗口默认为第一行到当前行），最后再进行sql排序
SELECT DEPTNO, MGR, ENAME, SAL, HIREDATE, MIN(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ASC) LAST_VALUE FROM EMP WHERE DEPTNO = 30 ORDER BY DEPTNO, MGR DESC; 运行结果：
SELECT DEPTNO, MGR, ENAME, SAL, HIREDATE, MIN(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) LAST_VALUE FROM EMP WHERE DEPTNO = 30 ORDER BY DEPTNO, MGR DESC; 运行结果：
---------------------------------------------------------------------------------------Blog：http://www.cnblogs.com/linjiqin/J2EE、Android、Linux、Oracle QQ交流群：142463980、158560018(满) 题外话：本人来自铁观音的发源地——泉州安溪，正宗安溪铁观音，有需要的友友欢迎加我Q:416501600。茶叶淘宝店：http://shop61968332.taobao.com/ |
【本文地址】
今日新闻 |
推荐新闻 |