一、题目
有学生每科科目成绩，求不及格课程数大于2的学生的平均成绩及其成绩平均值后所在的排名。
+------+------+--------+| 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.先计算出每个学生的平均成绩、不及格的科目数；
3.根据平均成绩计算排名；
4.得出最后结果；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
三、SQL
1）使用聚合函数计算出每个学生的平均成绩、不及格科目数
selectsid,avg(score) as avg_score,sum(case when score <60 then 1 else 0 end) as fail_numfrom t_scores_034group by sid
查询结果
2)根据平均成绩计算排名
selectsid,avg_score,fail_num,dense_rank()over(order by avg_score desc) as rnfrom(--计算学生的平均成绩，不及格科目数selectsid,avg(score) as avg_score,sum(case when score <60 then 1 else 0 end) as fail_numfrom t_scores_034group by sid) t
查询结果
3.剔除不增长的记录，计算每次连续次数。（连续问题，这里使用双排序法，就不赘叙了）
selectsid,avg_score,rnfrom(selectsid,avg_score,fail_num,dense_rank()over(order by avg_score desc) as rnfrom(--计算学生的平均成绩，不及格科目数selectsid,avg(score) as avg_score,sum(case when score <60 then 1 else 0 end) as fail_numfrom t_scores_034group by sid) t)ttwhere fail_num >2
查询结果
四、建表语句和数据插入
--建表语句CREATE TABLE t_scores_034 (sid bigint COMMENT '学生ID',cid bigint COMMENT '课程ID',score bigint COMMENT '得分') COMMENT '用户课程分数'ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';-- 插入数据insert into t_scores_034(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)
原文作者：数据仓库技术；如有侵权，请联系管理员删除。
请登录后发表评论
登录
全部评论
