窗口函数是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 不会。表格如下
![4ac838b448bba82c1e4099094365ae91.png](https://i-blog.csdnimg.cn/blog_migrate/30900ca5d2d56231da82edb8fe3caa1e.jpeg)
最后的结果可以显示如下
![8396f45d8fbd519b079c249998614f9c.png](https://i-blog.csdnimg.cn/blog_migrate/c8b9a355b624684a87137e05fffea68b.png)
那么都是表示排序,rank,denserank, row_number 有什么区别呢
如图
![02b62b68911e9a8359be7091eda1c8d8.png](https://i-blog.csdnimg.cn/blog_migrate/f0c242da033bfdcedadfe1c2cdc0cd5d.jpeg)
rank排序的时候,如果遇到同样的数值,会给出同一个排序序号,但是这些数值不占用其他排序的顺序,也就是说是纯粹排序的序号,但是计数。比如两个第二名后,接着就是第四名,因为3这个位置被第二个2 占用了
dense_rank 遇到同样的数值,也给出同一个排序序号,但是不会计数。上面情况,第二个2之后是第三
row_number 是单纯的行号,不管数值,只是行号,跟 python 的dataframe 中的index 的意思一样
还是上面的表格,运行结果如下
![f0afdc3767ba832beaa30e7eaed800db.png](https://i-blog.csdnimg.cn/blog_migrate/38d9c5a06b928d32fd6e7065e87c8be6.jpeg)
需要注意窗口函数的位置和写法和普通的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值。其他聚合函数同样如此,所以在需要用到移动平均和累计求和的时候,可以用聚合函数类型的窗口函数计算。
三、组内比较成绩
![b5233307780b5a364cba501fb97e2bb3.png](https://i-blog.csdnimg.cn/blog_migrate/0e55a84adac60a9e9f0c1254d157999a.jpeg)
如何寻找单科成绩高于该科目平均成绩的学生
这道题如果不用窗口函数,SQL 查询如下
![7a156e785d196624efba5627d51ffbb5.png](https://i-blog.csdnimg.cn/blog_migrate/b50c019e2e62343ccce9ec4f5d0cc732.jpeg)
如果使用窗口函数,查询语句和结果如下
![d7aaea41289b18f901dc018a690c548d.png](https://i-blog.csdnimg.cn/blog_migrate/0087e5d942b5f0de00c45f82f5e0841c.png)
可以看出来,使用窗口函数会简单一些。原因在于如果不用窗口函数,因为分组操作,表格发生了变化,所以我们不得不用同一个表拼接处符合条件的语句;窗口函数能够保留表结构不变,同时生产比较列作为标志位,在这个扩展的表格里,我们就可以直接操作 ,选取需要的列就可以了。
总结
- partition 用来分组,不用就不会分组。partition的分组是保留所有行的分组,group by 是合并成一行的分组。
- 窗口函数可以是专用的窗口函数,也可以是聚合函数。
- 窗口函数只能写在select 后面
- 窗口函数适用于需要分组排序的问题,比如分组比较,Top N等