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

jarckv

于 2024-05-15 22:04:52 发布

阅读量271

点赞数 9

文章标签：大数据面试学习

本文链接：https://blog.youkuaiyun.com/jarckv/article/details/138925270

版权

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

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

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

三、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
(
	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
) tt
group by sid
having sum(flag) = 0

查询结果
请添加图片描述

四、建表语句和数据插入

--建表语句
CREATE TABLE t_scores_042 (
sid bigint COMMENT '学生ID',
cid bigint COMMENT '课程ID',
score bigint COMMENT '得分'
) COMMENT '用户课程分数'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
;
-- 插入数据
insert into t_scores_042(sid,cid,score)
values
(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),


![img](https://img-blog.csdnimg.cn/img_convert/83b4278e6e93bbdfcb68af29c0b96f9a.png)
![img](https://img-blog.csdnimg.cn/img_convert/4c369985d1df6bb9da4229b6fd3724de.png)

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

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


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**

et/topics/618545628)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**