首页 > 其他 > 详细

二、理解over()函数

时间:2016-01-19 23:34:21      阅读:258      评论:0      收藏:0      [点我收藏+]

 

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;
技术分享

运行结果:

技术分享

              

二、理解over()函数

原文:http://www.cnblogs.com/tian830937/p/5143627.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!