HiveSQL-面试题034 不及格课程数大于2的学生的平均成绩及其排名

数据仓库晨曦

发布于 2024-01-08 07:47:55

30300

代码可运行

文章被收录于专栏：数据仓库技术数据仓库技术

运行总次数：0

代码可运行

一、题目

有学生每科科目成绩，求不及格课程数大于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）使用聚合函数计算出每个学生的平均成绩、不及格科目数

select
sid,
avg(score) as avg_score,
sum(case when score <60 then 1 else 0 end) as fail_num
from t_scores_034
group by sid

查询结果

2)根据平均成绩计算排名

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 t_scores_034
group by sid
) t

查询结果

3.剔除不增长的记录，计算每次连续次数。（连续问题，这里使用双排序法，就不赘叙了）

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 t_scores_034
group by sid
) t
)tt
where 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)