Oracle 的开窗函数

　　对面测试小姐姐问问会不会开窗函数，刚开始是懵逼的，我以为是算法啥的（？？？）百度一下发现是oracle的，阿西吧。。

在这记一下开窗函数，记录这可耻的事情。。

测试小姐姐是类似这样子的需求：

学生，班级，年级，成绩。在一张table1表里面有学生学号，学生班级，学生成绩在里面，希望在table1表中取出对应年级的所有班级的成绩前二十个学生的成绩和个人信息。最初懵逼的我先是百度半天开窗函数怎么用，然后发现oracle没有limit语句，瞬间爆炸。转而思考子查询，然后在我快要写出来的时候测试小jj说他找别人要到了。。那位写好的哥哥用了rank()over语句，我是不知道有这么个东西，所有在最里层的查询使用rownum给编号，所以写的比较慢。

先学习下rank()over：

 rank()over, dense_rank()over,row_number()over，三个XX（）over

区别：

1.rank()over:查出指定条件后的进行排名。成绩相同的两名是并列形式的，也就是成绩相同rank（）出来的数据是相同的，讲道理那个用rank（）over的小哥这样写并不对，不过测试小jj没说啥，我也就没说啥了

2.row_number()over ：row_number()故名思意行取出多少就是多少，分数相同的也会增加row rank，

3.dense_rank()over:与rank（）over不同的是，分数相同的话不增加row rank但是会影响出来的row行数 比如 同班级前二十名都是99分的话 查出来的rank 就是1，但是有二十个1，而rank()over查出来的会大于二十个数据，，比如前三十个数据都是99的话就会有三十个1被查出来，row（）over则不会这样。

值得注意的是rank（）over默认空值是最大的，如果排序字段有null的话就不好了，可以在over(partition by XX order by XXX desc nulls last)使得null值最小。

开窗函数三个分析子句：partition，order，rows 分别对应着 班级 分数 二十个数据（又叫窗口），这个窗口可以是滑动的 （range preceding  following） ，窗口子句不能单独出现，必须有order by子句时才允许出现，不过使用order by可以不使用窗口语句。当省略窗口子句时：

1）如果存在order by 则默认的窗口是：unbounded preceding and current row

2）如果不存在order by 则默认的窗口是”unbounded preceding and unbounded following

 

 

二、over（）函数中 order by的执行时机

由于分析函数是在sql查询结束后再进行的操作，即sql中的order by也会影响分析函数的执行过程：

当sql语句中order by满足分析函数要求的order时，sql中的order会先被执行，分析函数在分析时就不需要再进行order；

当sql语句中order by不满足分析函数的要求时，sql语句中排序将最后在分析函数结束后执行order。