常见大数据面试SQL-查询每个学科第三名的学生的学科成绩总成绩及总排名

一、题目

常见大数据面试SQL-查询每个学科第三名的学生的学科成绩总成绩及总排名
有学生成绩表，包含学生姓名、学科、成绩三个字段，请用一条SQL查询出每个学科排名第三名的学生，他的学科成绩、总成绩、以及总排名。

样例数据

+----------+----------+--------+
| student  | subject  | score  |
+----------+----------+--------+
| 张三       | 语文       | 95     |
| 李四       | 语文       | 90     |
| 王五       | 语文       | 88     |
| 赵六       | 语文       | 77     |
| 张三       | 数学       | 80     |
| 李四       | 数学       | 90     |
| 王五       | 数学       | 92     |
| 赵六       | 数学       | 84     |
| 张三       | 英语       | 82     |
| 李四       | 英语       | 93     |
| 王五       | 英语       | 88     |
| 赵六       | 英语       | 68     |
+----------+----------+--------+

二、分析

本题要求查询排名第三的学生的成绩，考察的是排序函数，要查询总成绩，考察sum()over(partition by )开窗函数。总排名则是再一次的考察排序函数。

维度	评分
题目难度	⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️

三、SQL

1.查询学科排名、每个学生总成绩

查看学科内排名，使用row_number函数，按照学科进行分组，分数倒叙排名。这里可以使用rank,dense_rank，题目并无特殊要求，这里使用row_number，如果有特殊要求可以根据不同要求使用，并无影响。
执行SQL

select student,
       subject,
       score,
       row_number() over (partition by subject order by score desc) as subject_rn,
       sum(score) over (partition by student)                       as total_score
from t_student_score_13

查询结果

+----------+----------+--------+-------------+--------------+
| student  | subject  | score  | subject_rn  | total_score  |
+----------+----------+--------+-------------+--------------+
| 张三       | 语文       | 95     | 1           | 257          |
| 张三       | 英语       | 82     | 3           | 257          |
| 张三       | 数学       | 80     | 4           | 257          |
| 李四       | 语文       | 90     | 2           | 273          |
| 李四       | 英语       | 93     | 1           | 273          |
| 李四       | 数学       | 90     | 2           | 273          |
| 王五       | 语文       | 88     | 3           | 268          |
| 王五       | 英语       | 88     | 2           | 268          |
| 王五       | 数学       | 92     | 1           | 268          |
| 赵六       | 语文       | 77     | 4           | 229          |
| 赵六       | 英语       | 68     | 4           | 229          |
| 赵六       | 数学       | 84     | 3           | 229          |
+----------+----------+--------+-------------+--------------+

这里subject_rn = 3就代表该学生在该科目中排名第三。
total_score 代表了该学生的总分。

2.根据学生总分计算学生总排名

我们先计算学生的总排名，已经在每行（即每个学科的记录）上添加了学生的总分，所以我们使用row_number()函数，根据学科分组，按照总分排序。这里就可以得出一个总排名，可以在结果中看到，同一个学生每个学科记录上的总分排名是一致的。

执行SQL

select student,
       subject,
       score,
       subject_rn,
       total_score,
       row_number() over (partition by subject order by total_score desc) as total_rn
from (select student,
             subject,
             score,
             row_number() over (partition by subject order by score desc) as subject_rn,
             sum(score) over (partition by student)                       as total_score
      from t_student_score_13) t

查询结果

+----------+----------+--------+-------------+--------------+-----------+
| student  | subject  | score  | subject_rn  | total_score  | total_rn  |
+----------+----------+--------+-------------+--------------+-----------+
| 李四       | 数学       | 90     | 2           | 273          | 1         |
| 王五       | 数学       | 92     | 1           | 268          | 2         |
| 张三       | 数学       | 80     | 4           | 257          | 3         |
| 赵六       | 数学       | 84     | 3           | 229          | 4         |
| 李四       | 英语       | 93     | 1           | 273          | 1         |
| 王五       | 英语       | 88     | 2           | 268          | 2         |
| 张三       | 英语       | 82     | 3           | 257          | 3         |
| 赵六       | 英语       | 68     | 4           | 229          | 4         |
| 李四       | 语文       | 90     | 2           | 273          | 1         |
| 王五       | 语文       | 88     | 3           | 268          | 2         |
| 张三       | 语文       | 95     | 1           | 257          | 3         |
| 赵六       | 语文       | 77     | 4           | 229          | 4         |
+----------+----------+--------+-------------+--------------+-----------+

3.查询每个学科的第三名

我们已经把所有需要的字段都查询出来了，只需要限定subject_rn = 3得到学科排名第三的同学记录即可

执行SQL

select student,
       subject,
       score,
       total_score,
       total_rn
from (select student,
             subject,
             score,
             subject_rn,
             total_score,
             row_number() over (partition by subject order by total_score desc) as total_rn
      from (select student,
                   subject,
                   score,
                   row_number() over (partition by subject order by score desc) as subject_rn,
                   sum(score) over (partition by student)                       as total_score
            from t_student_score_13) t) tt
where subject_rn = 3

查询结果

+----------+----------+--------+--------------+-----------+
| student  | subject  | score  | total_score  | total_rn  |
+----------+----------+--------+--------------+-----------+
| 赵六       | 数学       | 84     | 229          | 4         |
| 张三       | 英语       | 82     | 257          | 3         |
| 王五       | 语文       | 88     | 268          | 2         |
+----------+----------+--------+--------------+-----------+

四、建表语句和数据插入

--建表语句
create table if not exists t_student_score_13
(
    student string,
    subject string,
    score   bigint
)
    ROW FORMAT DELIMITED
        FIELDS TERMINATED BY ','
    STORED AS orc;

--插入数据

insert into t_student_score_13(student, subject, score)
values ('张三', '语文', 95),
       ('李四', '语文', 90),
       ('王五', '语文', 88),
       ('赵六', '语文', 77),
       ('张三', '数学', 80),
       ('李四', '数学', 90),
       ('王五', '数学', 92),
       ('赵六', '数学', 84),
       ('张三', '英语', 82),
       ('李四', '英语', 93),
       ('王五', '英语', 88),
       ('赵六', '英语', 68);

常见大数据面试SQL-查询每个学科第三名的学生的学科成绩总成绩及总排名