常见大数据面试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     |
+------+------+--------+

二、分析

1. 本题比较简单，考查的是聚合函数、条件函数和排序开窗函数，重点在考察基础知识点
2. 先计算出每个学生的平均成绩、不及格的科目数；
3. 根据平均成绩计算排名；
4. 得出最后结果；

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

三、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)

本文同步在微信公众号“数据仓库技术”和“个人博客”数据仓库技术发表；