一、题目

一张学生成绩表(student_scores)，有year-学年，subject-课程，student-学生，score-分数这四个字段，请完成如下问题：

问题1：每年每门学科排名第一的学生

问题2：每年总成绩都有所提升的学生

数据如下

二、思路

问题1：每年每门学科排名第一的学生。这个问题考察的是如何获取分组中的某个值。这里的组是年份+学科，比如18年语文，可以使用rank函数获取18年语文成绩中分数最大的一个。知识点：rank开窗函数。

问题2：每年总成绩都有所提升的学生。如果一个学生的成绩每年都会提升，那么按总成绩升序排序后年份与序号的差值相同，比如学生A：2018年成绩为210，序号为0；2019年成绩为230，序号为1；2020年成绩为260，序号为2，可以看出2018 -0 = 2019-1 = 2020-2。那么这个问题的解题思路就是：1)计算每个学生每年的总成绩 2）对1中的总成绩升序排列，并计算年份和序号的差值 3）如果每个学生的差值只有一个说明成绩每年都会提升。知识点：row_number开窗函数。

三、解答

问题1：每年每门学科排名第一的学生。

通过rank开窗函数计算每年没学科的排名，并取排名第一的学生。

select 
  *
from (
  select 
    year,
    subject,
    student,
    score,
    rank() over(partition by year,subject order by score desc) rk
  from mst.student_scores
) t11
where rk = 1

问题2：每年总成绩都有所提升的学生。

-- 每年总成绩都有所提升的学生
with tab_detail as (
  -- 学生各年总成绩排序打标（由小到大）
  select 
    student,
    year,
    score,
    row_number() over(partition by student order by score) rn
  from (
    -- 每个学生各年份的总成绩
    select 
      student,
      year,
      sum(score) score
    from mst.student_scores
    group by student,
      year
  ) t11
  order by student, year
)
  
select 
  student
from (
  select 
    student,
    count(distinct diff_val) cnt
  from (
    -- 如果每年成绩都提升则 diff_val 相等
    select 
      student,
      year,
      score,
      year - rn as diff_val
    from tab_detail
  ) t11
  group by student
) t21
where cnt = 1

数据

# 建表
-- 建表语句
create table mst.student_scores(
  year string,
  subject string,
  student string,
  score int 
)
stored as parquet;
-- 数据插入语句
INSERT INTO table 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);

注：题目获取自网络