一、题目
有学生每科科目成绩，找出所有科目成绩都大于对应学科的平均成绩的学生
+------+------+--------+| 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）使用开窗函数给每行记录添加对应科目的平均成绩
selectsid,cid,score,avg(score) over(partition by cid) as avg_scorefrom t_scores_042
查询结果
2)给每个学生每个科目进行打标，科目成绩>科目平均成绩的标记为0，反之为1；
selectsid,cid,score,avg_score,if(score>avg_score,0,1) as flagfrom(selectsid,cid,score,avg(score) over(partition by cid) as avg_scorefrom t_scores_042) t
查询结果
3）使用sum统计flag和为0的学生即满足条件的学生
selectsidfrom(selectsid,cid,score,avg_score,if(score>avg_score,0,1) as flagfrom(selectsid,cid,score,avg(score) over(partition by cid) as avg_scorefrom t_scores_042) t) ttgroup by sidhaving 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),(4,1,90),(4,2,90),(4,3,72)
本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术“地址：
同时有“数据仓库技术”社群以及有几十位小伙伴一起讨论数据仓库相关技术，欢迎你的加入，社群免费。
原文作者：数据仓库技术；如有侵权，请联系管理员删除。
下一篇：
大数据面试SQL035-用户行为路径分析
请登录后发表评论
登录
全部评论
