面试被经常问的SQL窗口函数

面试题

有一张“学生成绩表”，包含4个字段：班级id、学生id、课程id、成绩。

在这里插入图片描述

问题1： 求出每个学生成绩最高的三条记录

问题2： 找出每门课程都高于班级课程平均分的学生

技术提升

技术要学会分享、交流，不建议闭门造车。一个人走的很快、一堆人可以走的更远。

我这边梳理一套数据分析面试题宝典，此外文章中的源码、资料、数据、技术交流提升，均可加知识星球交流群获取，群友已超过2000人，添加时切记的备注方式为：来源+兴趣方向，方便找到志同道合的朋友。

方式①、添加微信号：pythoner666，备注：来自 CSDN + 面试题
方式②、微信搜索公众号：Python学习与数据挖掘，后台回复：加群

【解题步骤】

1. topN问题

问题1是常见的排名问题（topN问题），要想到用SQL的窗口函数来解决这类业务问题。

用窗口函数获取顺序有三种：rank()，dense_rank()和row_number()。

同样是按“值”从小到大排序，三者的区别如下：

在这里插入图片描述

根据问题的描述，我们应该使用dense_rank窗户函数

select *
from (
select *,dense_rank() over (partition by 班级id,学生id order by 成绩 desc) as 顺序
from 学生成绩表
) t1
where 顺序 <= 3;

查询结果：

在这里插入图片描述

2. 汇总分析

问题2要求找出每门课程都高于班级课程平均分的学生，可以拆解成以下几个问题：

1）求出每个班级，每门课程的平均分

2）将学生每门课程的成绩与所在班级的对应课程平均分相减，结果大于0就说明该学生的这门成绩高于课程平均分

3）“找出每门课程都高于班级课程平均分的学生”说明对于学生来说，最小的“相减结果”都是大于0的

首先用汇总分析求出每个班级，每门课程的平均分。

select 班级id,课程id,avg(成绩) as 课程平均分
from 学生成绩表
group by 班级id,课程id;

查询结果：

在这里插入图片描述

3. 多表联结

涉及到多表查询，需要用到多表联结。

这里目的是为了将“将学生每门课程的成绩与所在班级的对应课程平均分相减”。

所以，是将原始的“学生成绩表”与“班级的课程平均分”进行联结。

为了保持左表“学生成绩表”的所有数据，将所有学生的成绩都与“课程平均分”x相减，所以选择“左联结（left join）”。

在这里插入图片描述

select t1.班级id,t1.学生id,t1.课程id,t1.成绩,t1.成绩 - t2.课程平均分 as 相减结果
from 学生成绩表 t1
left join (
select 班级id,课程id,avg(成绩) as 课程平均分
from 学生成绩表
group by 班级id,课程id
) t2 on t1.班级id = t2.班级id and t1.课程id = t2.课程id;

在这里插入图片描述

最后使用分组汇总，并结合having条件筛选出“相减结果的最小值大于0”的学生。

select 班级id,学生id
from (
select t1.班级id,t1.学生id,t1.课程id,t1.成绩,t1.成绩 - t2.课程平均分 as 相减结果
from 学生成绩表 as t1
left join (
select 班级id,课程id,avg(成绩) as 课程平均分
from 学生成绩表
group by 班级id,课程id
) as t2 on t1.班级id = t2.班级id and t1.课程id = t2.课程id
) as tmp
group by 班级id,学生id
having min(相减结果) > 0;