sql中的开窗函数over()

     今天刷LeetCode的时候看到一道题,题目是这个样子

     LeetCode上面要求是用mysql来解决这道题,因为平时我上班的时候大部分时间都是在sqlserver上操作,所以一看到这个题目的要求我脑海中就闪过了开窗函数当中的密集排名。不过这道题要求是用mysql来解决,所以我先将mysql上面的解决思路理一下。

     mysql来解决这个问题的话首先想到的就是order by Score desc来将第一列完成,第二列的话我的想法是去重利用聚合函数count来给这些值重新排序。

      SELECT
          Score,
          (SELECT count(0) FROM (SELECT distinct Score s FROM Scores) tmp WHERE s >= Score) Rank
      FROM Scores

      ORDER BY Score desc

     上面就是mysql的解决方法,sqlserver的话就简单许多了,直接利用密集排名就可以搞定。

    select Score,dense_rank() over(order by Score desc) from Scores

    sqlserver的开窗函数分两大类,一类是聚合开窗函数,一类是排序开窗函数。

聚合类开窗函数聚合函数(列)over(选项)，选项可以是partition by子句,但不可以是order by 子句。

over关键字表示把聚合函数当成聚合开窗函数而不是聚合函数。SQL标准允许将所有聚合函数用做聚合开窗函数。举个例子,开窗函数count(*) over() 对于查询结果的每一行都返回所有符合条件的行的条数。over关键字后的括号中还经常添加选项用于改变进行聚合运算的窗口范围。如果over关键字后的括号中的选项为空的话,则开窗函数会对结果集中的所有数据进行聚合运算。

开窗函数的over关键字后面括号中可以使用parttition by 子句来定义行的分区来进行聚合运算。与group by 子句不同，partition by 子句创建的分区是独立于结果集的，创建的分区是进行聚合运算的，而不同的开窗函数所创建的分区互不干扰。举个例子,下面的sql语句用于显示每一个人员信息以及所在的班级和这个班级所拥有的人数：

select id,name,class,score,count(name) over(partition by class) from #students

over(partition by class)表示对结果集按照class进行分区,并计算当前所属的组的聚合计算结果。在同一个select语句中可以同时使用多个开窗函数。

排序开窗函数:row_number(行号),rank(排名),dense_rank(密集排名),ntile(分组排名)

直接看下面的sql可能会比较清晰:

select id,name,class,score,       row_number() over(order by score) as rownum,       rank() over(order by score) as rank,       dense_rank() over(order by score) as dense_rank,       ntile(6) over(order by score) as ntile from #students

对于row_number()来说,就是得出排序结果的序号。

对于rank()来说,就是得到排序结果的排名号,如果有两个第二名的话,就不会有第三名,有三个第二名就不会有第四名。

对于dense_rank()来说,就是每个人只有一种排名,然后出现两个两个并列第一的情况,这时候排在第一名后面的人就是第二名。

对于ntile(6)来说,就是分成6等分然后分成6个组,并显示组所在的序号。

排序函数和聚合开窗函数类似,都可以在over子句中使用partition by子句 。