大数据面试SQL042-所有考试科目的成绩都大于对应学科的平均成绩的学生(3)

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取

一、题目

有学生每科科目成绩，找出所有科目成绩都大于对应学科的平均成绩的学生

+------+------+--------+
| sid  | cid  | score  |
+------+------+--------+
| 1    | 1    | 90     |
| 1    | 2    | 50     |
| 1    | 3    | 72     |
| 2    | 1    | 40     |
| 2    | 2    | 50     |
| 2    | 3    | 22     |
| 3    | 1    | 30     |
| 3    | 2    | 50     |
| 3    | 3    | 52     |
| 4    | 1    | 90     |
| 4    | 2    | 90     |
| 4    | 3    | 72     |
+------+------+--------+

二、分析

题目要求找出每科科目成绩都大于对应学科平均成绩的学生，难点有两个：

1）给每行记录（每个学生每个学科）添加该学科的平均成绩，这里开窗函数可以解决；

2）查询出“所有”科目都大于平均成绩的学生，这里的所有比较难处理，有个技巧：对每个学生的每个科目满足“成绩>科目平均成绩”的记录打0，不满足的打1，然后对学生所有科目标签求和，和为0则满足“所有科目都大于平均成绩”，和>0则不满足；

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️

三、SQL

1）使用开窗函数给每行记录添加对应科目的平均成绩

select
sid,
cid,
score,
avg(score) over(partition by cid) as avg_score
from t_scores_042

查询结果

2)给每个学生每个科目进行打标，科目成绩>科目平均成绩的标记为0，反之为1；

select 
	sid,
	cid,
	score,
	avg_score,
	if(score>avg_score,0,1) as flag
from
(
	select
		sid,
		cid,
		score,
		avg(score) over(partition by cid) as avg_score
	from t_scores_042
) t

查询结果

3）使用sum统计flag和为0的学生即满足条件的学生

select
	sid
from
(


![img](https://img-blog.csdnimg.cn/img_convert/cff09cabb0a48512940d4d4f71e6f573.png)
![img](https://img-blog.csdnimg.cn/img_convert/87446e8499fe4a2a9f5114bafbca6b39.png)
![img](https://img-blog.csdnimg.cn/img_convert/c117f40952b59c515bdc505f05596476.png)

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.youkuaiyun.com/forums/4f45ff00ff254613a03fab5e56a57acb)**

、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.youkuaiyun.com/forums/4f45ff00ff254613a03fab5e56a57acb)**