数据分析（SQL）常见面试题：开窗函数

素颜马尾好姑娘i 2022-09-11 03:17 113阅读 0赞

### 一、什么是开窗函数 ###

开窗函数/分析函数：over()  
       开窗函数也叫分析函数，有两类：一类是聚合开窗函数，一类是排序开窗函数。  
开窗函数的调用格式为：

> 函数名(列名) OVER(partition by 列名 order by列名)

如果你没听说过开窗函数，看到上面开窗函数的调用方法，你可能还会有些疑惑。但只要你了解聚合函数，那么理解开窗函数就非常容易了。  
       我们知道聚合函数对一组值执行计算并返回单一的值，如sum()，count()，max()，min()， avg()等，这些函数常与group by子句连用。除了 COUNT 以外，聚合函数忽略空值。  
       但有时候一组数据只返回一组值是不能满足需求的，如我们经常想知道各个地区的前几名、各个班或各个学科的前几名。这时候需要每一组返回多个值。用开窗函数解决这类问题非常方便。  
       开窗函数和聚合函数的区别如下：  
       (1）SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。  
       （2）聚合函数每组只返回一个值，开窗函数每组可返回多个值。

注：常见主流数据库目前都支持开窗函数，但mysql数据库目前还不支持。

### 二、常见面试题 ###

1. 分区排序：row\_number () over()  
有如下学生成绩表：students\_grades  
![在这里插入图片描述][412dbcac66c04caebafba9134d05d9b3.png]

查询每门课程course\_name前三名的学生姓名及成绩，要求输出列格式如下：  
course\_name, number, stu\_name, grades  
查询语句如下：  
![在这里插入图片描述][b5501a634ddc4b5cbe97a63d48e647a5.png]

2.几个排序函数row\_number() over()、rank() over()、dense\_rank() over()、ntile() over()的区别

（1） row\_number() over()：对相等的值不进行区分，相等的值对应的排名相同，序号从1到n连续。

（2） rank() over()：相等的值排名相同，但若有相等的值，则序号从1到n不连续。如果有两个人都排在第3名，则没有第4名。

（3） dense\_rank() over()：对相等的值排名相同，但序号从1到n连续。如果有两个人都排在第一名，则排在第2名（假设仅有1个第二名）的人是第3个人。

（4） ntile( n ) over()：可以看作是把有序的数据集合平均分配到指定的数量n的桶中,将桶号分配给每一行，排序对应的数字为桶号。如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的数据条数最多相差1。

学生成绩表同上，查询语句如下：  
![在这里插入图片描述][88e29a588d2646bfa2aa6ef7c3420957.png]

查询结果如下：  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5aSn56KX5ZCD5LiN5LiL_size_19_color_FFFFFF_t_70_g_se_x_16]  
原博客原址：https://www.douban.com/group/topic/155112949/

[412dbcac66c04caebafba9134d05d9b3.png]: /images/20220828/9d2c9ef492cc4048acfa3772a571b0a3.png
[b5501a634ddc4b5cbe97a63d48e647a5.png]: /images/20220828/5674da8473534a1e8ff74272541151d4.png
[88e29a588d2646bfa2aa6ef7c3420957.png]: /images/20220828/e659f412b87c4b2ca96a3e2afc539722.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5aSn56KX5ZCD5LiN5LiL_size_19_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/1cbd448327c349b1917e964bb0116b25.png