sql中排序序号_SQL 的窗口函数-CSDN博客

本文链接：https://blog.csdn.net/weixin_35384453/article/details/113320904

窗口函数是SQL中相对比较高级的函数，一般用于一些分组操作，比如组内排名和TopN问题。窗口函数的英文名字是 online Anallytical processing，联机分析处理。主要用来对数据进行分析和处理。

主要的窗口函数包括： RANK, DENSERANK, ROW_NUMBER, SUM, AVG, COUNT, MAX, MIN.可以看出来，这些主要是聚合函数和专用窗口函数，主要的功能是排序，聚合，同时对原表没有影响。

窗口函数主要针对分组操作决定了窗口函数要使用在select 子句中。

SQL中的分组操作，最常用的是之前用的group by。但是group by 有一个问题，就是分组之后记录就合并成一个记录了。如果希望依然保持原数据表格不变，或者按照层级分组，group by 就不能用了，所以SQL 又提供了窗口函数的partition by 函数，这个函数按照字面理解是分区的意思，所以，它的功能也是分区，数据根据指定要求分区后，行数据并不发生变化。

一、分组和排序类

Rank：

如果一个成绩表，有班级，学号，成绩三个列，其中班级可能多个，每个班级中有不同学号，但是不同班级学号可以重复。用rank可以根据班级进行排队，并且把排序结果显示出来。同样功能order by 两次可以做到，但是序号就显示不出来了。

select rank() over (partition by 班级 order by 成绩 desc）as ranking from 班级表

这个语句的作用是把数据先按照班级分区，然后班级内按照成绩降序排序，并且增加一列ranking 显示排序的结果。这里跟group by 最大的区别是，group by 会把汇总数据合并成一行，partition by 不会。表格如下

最后的结果可以显示如下

那么都是表示排序，rank，denserank, row_number 有什么区别呢

如图

rank排序的时候，如果遇到同样的数值，会给出同一个排序序号，但是这些数值不占用其他排序的顺序，也就是说是纯粹排序的序号，但是计数。比如两个第二名后，接着就是第四名，因为3这个位置被第二个2 占用了

dense_rank 遇到同样的数值，也给出同一个排序序号，但是不会计数。上面情况，第二个2之后是第三

row_number 是单纯的行号，不管数值，只是行号，跟 python 的dataframe 中的index 的意思一样

还是上面的表格，运行结果如下

需要注意窗口函数的位置和写法和普通的SQL函数有多不同，在语句中要指定函数操作的对象，使用over 关键字。

窗口函数的另外一个经典应用是TopN问题

求每个学生的最高的两个成绩。需要分析每个学生，每个品种，每个列别的时候，就需要用分组函数了。

由于需要对学生分组，并且要排序，这个时候可以考虑使用窗口函数。由于我们指定要前两个数据，所以 row_number 比较适合于这种情况。

窗口函数好处是可以生成一列新的数据，并且呈现在表中，可以引用。

select * from（

select * ,row_number() over(patition by 姓名 order by 成绩 DESC） as

ranking from score） as a where ranking <=2;

这里斜体的部分会先执行,返回按照姓名和成绩降序分区后的表格，然后在这个新排序的表格里寻找ranking <=2 的数据。

另外要注意的问题是什么时候需要用到子查询。SQL最先执行的是from 语句，然后是过滤和分组操作，最后从这里选择需要的列，所以当select 语句执行时以上信息还没有生成的时候要用子查询先生成，子查询里有select ，会把符合要求的行过滤后生成一个子集，这时候外层的select 就可以调用了。

二、聚合函数类

聚合函数作为窗口函数位置和分组类一样。聚合窗口函数在运行的时候粒度是表级别的，也就是说会把符合条件的所有行进行聚合。比如sum，运行时实际显示的是移动求和，而且是从第一行到本行的和，这样到最后一行就是totol值。其他聚合函数同样如此，所以在需要用到移动平均和累计求和的时候，可以用聚合函数类型的窗口函数计算。

三、组内比较成绩

如何寻找单科成绩高于该科目平均成绩的学生

这道题如果不用窗口函数，SQL 查询如下

如果使用窗口函数，查询语句和结果如下

可以看出来，使用窗口函数会简单一些。原因在于如果不用窗口函数，因为分组操作，表格发生了变化，所以我们不得不用同一个表拼接处符合条件的语句;窗口函数能够保留表结构不变，同时生产比较列作为标志位，在这个扩展的表格里，我们就可以直接操作，选取需要的列就可以了。

总结

partition 用来分组，不用就不会分组。partition的分组是保留所有行的分组，group by 是合并成一行的分组。
窗口函数可以是专用的窗口函数，也可以是聚合函数。
窗口函数只能写在select 后面
窗口函数适用于需要分组排序的问题，比如分组比较，Top N等