Hive-SQL查询每年总成绩都有所提升的学生
一、题目
一张学生成绩表(student_scores)，有year-学年，subject-课程，student-学生，score-分数这四个字段，请完成如下问题：
- 问题1：每年每门学科排名第一的学生
- 问题2：每年总成绩都有所提升的学生
数据内容如下
+-------+----------+----------+--------+
| year | subject | student | score |
+-------+----------+----------+--------+
| 2018 | 语文 | A | 84 |
| 2018 | 数学 | A | 59 |
| 2018 | 英语 | A | 30 |
| 2018 | 语文 | B | 44 |
| 2018 | 数学 | B | 76 |
| 2018 | 英语 | B | 68 |
| 2019 | 语文 | A | 51 |
| 2019 | 数学 | A | 94 |
| 2019 | 英语 | A | 71 |
| 2019 | 语文 | B | 87 |
| 2019 | 数学 | B | 44 |
| 2019 | 英语 | B | 38 |
| 2020 | 语文 | A | 91 |
| 2020 | 数学 | A | 50 |
| 2020 | 英语 | A | 89 |
| 2020 | 语文 | B | 81 |
| 2020 | 数学 | B | 84 |
| 2020 | 英语 | B | 98 |
+-------+----------+----------+--------+二、分析
1.题目1查询每年每科分数最高的学生，开窗函数考察；可以使用row_number(),rank() 等，但是由于咱们公号使用这两个太多，这次使用first_value();
2.题目2每年总成绩都有提升，首先是计算每年的成绩，聚合函数sum（）;然后使用有序计算开窗函数lag（）得出上一年分数；
3.去掉第一年的数据，即lag()产出结果为空的行；
4.本年分数进行比较得出本年是否有进步；
5.“取全”，要求行数与符合条件行数相同；
6.两个问题整体考察了多个开窗函数，考察了“取全部“的逻辑处理，聚合函数；
维度 | 评分 |
|---|---|
题目难度 | ⭐️⭐️⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️⭐️ |
三、查询SQL
3.1 问题1：每年每门学科排名第一的学生
1.按照年份、学科分组，按照分数排序，计算出相同年份，相同学科排名第一的人，添加到本行
--计算排名第一的人
select year,subject,student,score,first_value(student)over(partition by year,subject order by score desc) as first_student
from student_scores查询结果
2.去重，计算出最终结果
select year,subject,first_student
from
(select year,subject,first_value(student)over(partition by year,subject order by score desc) as first_student
from student_scores) t
group by year,subject,first_student查询结果
3.2 问题2：每年总成绩都有所提升的学生
1.计算每年每个学生的总成绩
--每年每个学生总成绩
select year,student,sum(score) as total_score
from student_scores
group by year,student查询结果
2.使用lag函数，在本行添加上一学年成绩
select
year,
student,
total_score,
lag(total_score)over(partition by student order by year) as last_year_score
from
(select year,student,sum(score) as total_score
from student_scores
group by year,student) t查询结果
3.剔除lag()结果字段为空数据，然后比较判断是否有进步
select
year,student,total_score,last_year_score,if(total_score>last_year_score,1,0) as improve_flag
from
(select
year,
student,
total_score,
lag(total_score)over(partition by student order by year) as last_year_score
from
(select year,student,sum(score) as total_score
from student_scores
group by year,student) t
) t1
where last_year_score is not null查询结果
4.取每年进步
-- 是否有进步明细
with t_improve as
(select
year,student,total_score,last_year_score,if(total_score>last_year_score,1,0) as improve_flag
from
(select
year,
student,
total_score,
lag(total_score)over(partition by student order by year) as last_year_score
from
(select year,student,sum(score) as total_score
from student_scores
group by year,student) t
) t1
where last_year_score is not null)
select
student
from
t_improve
group by student
having count(1) = sum(improve_flag)
查询结果
四、建表语句和数据插入
-- 建表语句
CREATE TABLE student_scores
(
year STRING,
subject STRING,
student STRING,
score INT )
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
-- 数据插入语句
INSERT INTO student_scores
(year, subject, student, score) VALUES
(2018, '语文', 'A', 84),
(2018, '数学', 'A', 59),
(2018, '英语', 'A', 30),
(2018, '语文', 'B', 44),
(2018, '数学', 'B', 76),
(2018, '英语', 'B', 68),
(2019, '语文', 'A', 51),
(2019, '数学', 'A', 94),
(2019, '英语', 'A', 71),
(2019, '语文', 'B', 87),
(2019, '数学', 'B', 44),
(2019, '英语', 'B', 38),
(2020, '语文', 'A', 91),
(2020, '数学', 'A', 50),
(2020, '英语', 'A', 89),
(2020, '语文', 'B', 81),
(2020, '数学', 'B', 84),
(2020, '英语', 'B', 98);本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2023-06-07，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录