常见大数据面试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查询每年每科分数最高的学生，开窗函数考察；可以使用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);

本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术 (opens in a new tab)“发表；
欢迎扫码关注公众号，添加博主好友加入数据仓库技术交流群；
如果觉得本文对您有帮助，欢迎扫码打赏

微信扫码备注「数据仓库」
加入数据仓库技术交流群

微信关注公众号「数据仓库技术」

微信扫码打赏

支付宝扫码打赏

1.计算每个用户的受欢迎程度 2.连续点击三次用户