一、题目
一张学生成绩表(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_studentfrom student_scores
查询结果
2.去重，计算出最终结果
select year,subject,first_studentfrom(select year,subject,first_value(student)over(partition by year,subject order by score desc) as first_studentfrom student_scores) tgroup by year,subject,first_student
查询结果
3.2 问题2：每年总成绩都有所提升的学生
1.计算每年每个学生的总成绩
--每年每个学生总成绩select year,student,sum(score) as total_scorefrom student_scoresgroup by year,student
查询结果
2.使用lag函数，在本行添加上一学年成绩
selectyear,student,total_score,lag(total_score)over(partition by student order by year) as last_year_scorefrom(select year,student,sum(score) as total_scorefrom student_scoresgroup by year,student) t
3.剔除lag()结果字段为空数据，然后比较判断是否有进步
selectyear,student,total_score,last_year_score,if(total_score>last_year_score,1,0) as improve_flagfrom(selectyear,student,total_score,lag(total_score)over(partition by student order by year) as last_year_scorefrom(select year,student,sum(score) as total_scorefrom student_scoresgroup by year,student) t) t1where last_year_score is not null
查询结果
4.取每年进步
-- 是否有进步明细with t_improve as(selectyear,student,total_score,last_year_score,if(total_score>last_year_score,1,0) as improve_flagfrom(selectyear,student,total_score,lag(total_score)over(partition by student order by year) as last_year_scorefrom(select year,student,sum(score) as total_scorefrom student_scoresgroup by year,student) t) t1where last_year_score is not null)selectstudentfromt_improvegroup by studenthaving 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);
点点关注不迷路，天天学习有进步
分享↓收藏↓点赞↓再看↓
原文作者：数据仓库技术；如有侵权，请联系管理员删除。
上一篇：
将间隔小于60s的数据分到同一组
下一篇：
hiveSQL-占据多少个好友的封面
请登录后发表评论
登录
全部评论
