理解over()开窗函数

问题：

先建一个表数据：
create table test_min_max
(
YearID number,
Line number
);


insert into test_min_max(yearID,line) values(2016,1001);
insert into test_min_max(yearID,line) values(2016,1002);
insert into test_min_max(yearID,line) values(2016,1003);
insert into test_min_max(yearID,line) values(2016,1004);
insert into test_min_max(yearID,line) values(2016,1007);
insert into test_min_max(yearID,line) values(2016,1008);
insert into test_min_max(yearID,line) values(2017,1011);
insert into test_min_max(yearID,line) values(2017,1012);
insert into test_min_max(yearID,line) values(2017,1013);
insert into test_min_max(yearID,line) values(2017,1014);
insert into test_min_max(yearID,line) values(2017,1015);
insert into test_min_max(yearID,line) values(2017,1017);
Commit;

【SQL1】
SELECT t.Yearid,
       t.Line AS Cur_Line,
       MIN(t.Line) Over(PARTITION BY t.Yearid ORDER BY t.Line) Min_Line,
       MAX(t.Line) Over(PARTITION BY t.Yearid ORDER BY t.line) Max_Line
  FROM test_min_max t;

【SQL2】
SELECT t.Yearid,
       t.Line AS Cur_Line,
       MIN(t.Line) Over(PARTITION BY t.Yearid) Min_Line,
       MAX(t.Line) Over(PARTITION BY t.Yearid) Max_Line
  FROM test_min_max t;

两个SQL对比，仅从结果表面来看，不然得出结论：
① Min()分析函数加不加Order by结果都一致；
② Min()，Max()分析函数加Order by计算逻辑不一致。（不知道是什么原因导致）

答复：

如果不加ORDER BY, 就没有窗口，计算范围是整个分区；
加上ORDER BY, 默认窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，就是排序后从分区第一行一直到当前行为止。
那么MIN还是第一行，而MAX就是当前行了

由此我们来详细了解下窗口和over()开窗函数

      窗口就是分析函数分析时要处理的数据范围，就拿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;

运行结果：