常见大数据面试SQL-不及格课程数大于2的学生的平均成绩及其排名
一、题目
有学生每科科目成绩，求不及格课程数大于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 |
+------+------+--------+二、分析
- 本题比较简单，考查的是聚合函数、条件函数和排序开窗函数，重点在考察基础知识点
- 先计算出每个学生的平均成绩、不及格的科目数；
- 根据平均成绩计算排名；
- 得出最后结果；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
三、SQL
1.使用聚合函数计算出每个学生的平均成绩、不及格科目数
执行SQL
select sid,
avg(score) as avg_score,
sum(case when score < 60 then 1 else 0 end) as fail_num
from t6_scores
group by sid查询结果
+------+---------------------+-----------+
| sid | avg_score | fail_num |
+------+---------------------+-----------+
| 1 | 70.66666666666667 | 1 |
| 2 | 37.333333333333336 | 3 |
| 3 | 44.0 | 3 |
| 4 | 84.0 | 0 |
+------+---------------------+-----------+2.根据平均成绩计算排名
执行SQL
select sid,
avg_score,
fail_num,
dense_rank() over (order by avg_score desc) as rn
from (
--计算学生的平均成绩，不及格科目数
select sid,
avg(score) as avg_score,
sum(case when score < 60 then 1 else 0 end) as fail_num
from t6_scores
group by sid) t查询结果
+------+---------------------+-----------+-----+
| sid | avg_score | fail_num | rn |
+------+---------------------+-----------+-----+
| 4 | 84.0 | 0 | 1 |
| 1 | 70.66666666666667 | 1 | 2 |
| 3 | 44.0 | 3 | 3 |
| 2 | 37.333333333333336 | 3 | 4 |
+------+---------------------+-----------+-----+3.得到最终结果
执行SQL
select sid,
avg_score,
rn
from (select sid,
avg_score,
fail_num,
dense_rank() over (order by avg_score desc) as rn
from (
--计算学生的平均成绩，不及格科目数
select sid,
avg(score) as avg_score,
sum(case when score < 60 then 1 else 0 end) as fail_num
from t6_scores
group by sid) t) tt
where fail_num > 2查询结果
+------+---------------------+-----+
| sid | avg_score | rn |
+------+---------------------+-----+
| 3 | 44.0 | 3 |
| 2 | 37.333333333333336 | 4 |
+------+---------------------+-----+四、建表语句和数据插入
--建表语句
CREATE TABLE t6_scores (
sid bigint COMMENT '学生ID',
cid bigint COMMENT '课程ID',
score bigint COMMENT '得分'
) COMMENT '用户课程分数';
-- 插入数据
insert into t6_scores(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)本文同步在微信公众号“数据仓库技术”和“个人博客”数据仓库技术 (opens in a new tab)发表；