一、题目
一张学生成绩表(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查询每年每科分数最高的学生，开窗函数考察；可以使用row_number(),rank() 等，由于使用这两个太多，这次使用first_value();
- 题目2每年总成绩都有提升，首先是计算每年的成绩，聚合函数sum（）;然后使用有序计算开窗函数lag（）得出上一年分数；
- 去掉第一年的数据，即lag()产出结果为空的行；
- 本年分数进行比较得出本年是否有进步；
- “取全”，要求行数与符合条件行数相同；
- 两个问题整体考察了多个开窗函数，考察了“取全部“的逻辑处理，聚合函数；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
三、SQL
问题1：每年每门学科排名第一的学生
1.按照年份、学科分组，按照分数排序，计算出相同年份，相同学科排名第一的人，添加到本行
执行SQL
--计算排名第一的人
select year,
subject,
student,
score,
first_value(student) over (partition by year,subject order by score desc) as first_student
from t_student_scores
查询结果
+-------+----------+----------+--------+----------------+
| year | subject | student | score | first_student |
+-------+----------+----------+--------+----------------+
| 2018 | 数学 | B | 76 | B |
| 2018 | 数学 | A | 59 | B |
| 2018 | 英语 | B | 68 | B |
| 2018 | 英语 | A | 30 | B |
| 2018 | 语文 | A | 84 | A |
| 2018 | 语文 | B | 44 | A |
| 2019 | 数学 | A | 94 | A |
| 2019 | 数学 | B | 44 | A |
| 2019 | 英语 | A | 71 | A |
| 2019 | 英语 | B | 38 | A |
| 2019 | 语文 | B | 87 | B |
| 2019 | 语文 | A | 51 | B |
| 2020 | 数学 | B | 84 | B |
| 2020 | 数学 | A | 50 | B |
| 2020 | 英语 | B | 98 | B |
| 2020 | 英语 | A | 89 | B |
| 2020 | 语文 | A | 91 | A |
| 2020 | 语文 | B | 81 | A |
+-------+----------+----------+--------+----------------+
2.去重，计算出最终结果
执行SQL
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 t_student_scores) t
group by year, subject, first_student
查询结果
+-------+----------+----------------+
| year | subject | first_student |
+-------+----------+----------------+
| 2018 | 数学 | B |
| 2018 | 英语 | B |
| 2018 | 语文 | A |
| 2019 | 数学 | A |
| 2019 | 英语 | A |
| 2019 | 语文 | B |
| 2020 | 数学 | B |
| 2020 | 英语 | B |
| 2020 | 语文 | A |
+-------+----------+----------------+
问题2：每年总成绩都有所提升的学生
1.计算每年每个学生的总成绩
执行SQL
--每年每个学生总成绩
select year, student, sum(score) as total_score
from t_student_scores
group by year, student
查询结果
+-------+----------+--------------+
| year | student | total_score |
+-------+----------+--------------+
| 2018 | A | 173 |
| 2018 | B | 188 |
| 2019 | A | 216 |
| 2019 | B | 169 |
| 2020 | A | 230 |
| 2020 | B | 263 |
+-------+----------+--------------+
2.使用lag函数，在本行添加上一学年成绩
执行SQL
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 t_student_scores
group by year, student) t
查询结果
+-------+----------+--------------+------------------+
| year | student | total_score | last_year_score |
+-------+----------+--------------+------------------+
| 2018 | A | 173 | NULL |
| 2019 | A | 216 | 173 |
| 2020 | A | 230 | 216 |
| 2018 | B | 188 | NULL |
| 2019 | B | 169 | 188 |
| 2020 | B | 263 | 169 |
+-------+----------+--------------+------------------+
3.剔除lag()结果字段为空数据，然后比较判断是否有进步
执行SQL
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 t_student_scores
group by year, student) t) t1
where last_year_score is not null
查询结果
+-------+----------+--------------+------------------+---------------+
| year | student | total_score | last_year_score | improve_flag |
+-------+----------+--------------+------------------+---------------+
| 2019 | A | 216 | 173 | 1 |
| 2020 | A | 230 | 216 | 1 |
| 2019 | B | 169 | 188 | 0 |
| 2020 | B | 263 | 169 | 1 |
+-------+----------+--------------+------------------+---------------+
4.取每年进步
执行SQL
-- 是否有进步明细
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 t_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)
查询结果
+----------+
| student |
+----------+
| A |
+----------+
四、建表语句和数据插入
-- 建表语句
CREATE TABLE t_student_scores
(
year STRING,
subject STRING,
student STRING,
score INT )
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
-- 数据插入语句
INSERT INTO t_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);
本文首发数据仓库技术网站常见大数据面试SQL-每年总成绩都有所提升的学生
