理解over()函数

1.1、两个order by的执行时机
分析函数（以及与其配合的开窗函数over（））是在整个sql查询结束后(sql语句中的order by的执行比较特殊)再进行的操作, 也就是说sql语句中的order by也会影响分析函数的执行结果：

a) 两者一致：如果sql语句中的order by满足与分析函数配合的开窗函数over（）分析时要求的排序，即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容一样，

那么sql语句中的排序将先执行,分析函数在分析时就不必再排序；
b) 两者不一致：如果sql语句中的order by不满足与分析函数配合的开窗函数over（）分析时要求的排序，即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容不一样，

那么sql语句中的排序将最后在分析函数分析结束后执行排序。

           

1.2、开窗函数over（）分析函数中的分组/排序/窗口
      开窗函数over（）分析函数包含三个分析子句：分组子句(partition by)， 排序子句(order by)， 窗口子句(rows)
      窗口就是分析函数分析时要处理的数据范围，就拿sum来说,它是sum窗口中的记录而不是整个分组中的记录，因此我们在想得到某个栏位的累计值时，我们需要把窗口指定到该分组中的第一行数据到当前行, 如果你指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个sum值都会一样，即整个组的总和。

      窗口子句在这里我只说rows方式的窗口，range方式和滑动窗口也不提。

 

      窗口子句中我们经常用到指定第一行，当前行，最后一行这样的三个属性：
第一行是 unbounded preceding，
当前行是 current row，
最后一行是 unbounded following，

注释：

当开窗函数over（）出现分组(partition by)子句时，

unbounded preceding即第一行是指表中一个分组里的第一行， unbounded following即最后一行是指表中一个分组里的最后一行；

当开窗函数over（）省略了分组(partition by)子句时， 

unbounded preceding即第一行是指表中的第一行， 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  --整个组 

 

而无论是否省略分组子句，如下结论都是成立的：

1、窗口子句不能单独出现，必须有order by子句时才能出现。

2、当省略窗口子句时:
a) 如果存在order by则默认的窗口是unbounded preceding and current row  --当前组的第一行到当前行，即在当前组中，第一行到当前行
b) 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following  --整个组

              
所以，

 

lag(sal) over(order by sal) 解释

over（order by salary）表示意义如下：

首先，我们要知道由于省略分组子句，所以当前组的范围为整个表的数据行，

然后，在当前组（此时为整个表的数据行）这个范围里执行排序（即order by salary），

最后，我们知道分析函数lag(sal)在当前组（此时为整个表的数据行）这个范围里的窗口范围为当前组的第一行到当前行，即分析函数lag(sal)在这个窗口范围执行。

 

 

 

例

select UserType from Users

 

 

 

select UserType, SUM(UserType)
OVER(order by UserType
rows between unbounded preceding and unbounded following ) from Users

 

 

 

select UserType, SUM(UserType)
OVER(order by UserType
rows between unbounded preceding and current row) from Users

 

转载于:https://www.cnblogs.com/cjm123/p/8038912.html