Redshift实现学生表与GPA表1:1左连接（取最新GPA）

表结构与需求 #

我有两张表：

学生信息表（每个student id唯一） #

id name         major
1  John Smith   Math
2  Bob Johnson  Science

GPA表（记录不同时间的GPA，student id不唯一） #

student_id gpa  timestamp
1          4.0  2024-03-22
1          4.1  2024-02-22
1          3.9  2024-01-22
2          3.0  2024-01-22
2          3.2  2023-12-22

我需要得到包含学生信息及对应最新GPA的表，结果如下：

id name         major   gpa
1  John Smith   Math    4.0
2  Bob Johnson  Science 3.0

遇到的问题 #

尝试用LEFT JOIN关联两张表时，会把每个学生和所有GPA条目关联，导致返回重复行。比如执行以下SQL：

select student.id, student.name, student.major, gpa_table.gpa from students
left join gpa_table on student.id = gpa_table.gpa

返回结果存在重复：

id name         major   gpa
1  John Smith   Math    4.0
1  John Smith   Math    4.1

尝试用SELECT DISTINCT筛选GPA表后，问题依然存在。

解决方案 #

方法1：使用窗口函数（推荐，支持Redshift等主流数据库） #

利用ROW_NUMBER()窗口函数给每个学生的GPA记录按时间降序编号，取排名第1的记录（即最新GPA），再和学生表关联：

WITH latest_gpa AS (
    SELECT 
        student_id, 
        gpa, 
        ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY timestamp DESC) AS rn
    FROM gpa_table
)
SELECT 
    s.id, 
    s.name, 
    s.major, 
    lg.gpa
FROM students s
LEFT JOIN latest_gpa lg ON s.id = lg.student_id AND lg.rn = 1;

说明：PARTITION BY student_id按学生分组，ORDER BY timestamp DESC让最新的记录排在前面，rn=1就筛选出每个学生的最新GPA，确保关联后是1:1映射。

方法2：子查询获取最新时间再匹配GPA #

先找出每个学生的最新GPA记录时间，再用该时间匹配对应的GPA值，最后关联学生表：

SELECT 
    s.id, 
    s.name, 
    s.major, 
    g.gpa
FROM students s
LEFT JOIN (
    SELECT g1.student_id, g1.gpa
    FROM gpa_table g1
    JOIN (
        SELECT student_id, MAX(timestamp) AS latest_time
        FROM gpa_table
        GROUP BY student_id
    ) g2 ON g1.student_id = g2.student_id AND g1.timestamp = g2.latest_time
) g ON s.id = g.student_id;

说明：内层子查询先获取每个学生的最新时间，再通过时间匹配到对应的GPA，最后和学生表关联，避免重复行。

内容的提问来源于stack exchange，提问作者Kevin2566