SQL实现按条件筛选先修class2后修class3的学生选课记录

需求说明 #

现有STUDENT_INFORMATION数据集，包含学生姓名、班级、选课日期字段。需要筛选出先修class2、之后紧接着修class3的学生对应选课记录，支持学生重复选课的场景（如ashley）。当前SQL仅能按学生和班级分组取最新选课日期，需调整实现目标需求。

数据集示例 #

目标结果示例 #

现有SQL代码 #

SELECT   
    "STUDENT NAME",
    "CLASS",
    MAX("DATE THEY TOOK THE CLASS") 
FROM 
    STUDENT_INFORMATION
GROUP BY 
    "STUDENT NAME","CLASS"

解决方案 #

使用窗口函数LEAD()和LAG()追踪学生的选课顺序，精准匹配class2之后紧跟class3的记录组合，同时支持重复选课场景：

WITH student_class_sequence AS (
    SELECT
        "STUDENT NAME",
        "CLASS",
        "DATE THEY TOOK THE CLASS",
        -- 获取当前选课之后的下一个班级
        LEAD("CLASS") OVER (PARTITION BY "STUDENT NAME" ORDER BY "DATE THEY TOOK THE CLASS") AS next_class
    FROM STUDENT_INFORMATION
)
SELECT
    "STUDENT NAME",
    "CLASS",
    "DATE THEY TOOK THE CLASS"
FROM student_class_sequence
WHERE
    -- 筛选class2之后直接选class3的class2记录
    ("CLASS" = 'class 2' AND next_class = 'class 3')
    -- 同时筛选对应的class3记录（即上一个选课是class2的class3记录）
    OR ("CLASS" = 'class 3' AND LAG("CLASS") OVER (PARTITION BY "STUDENT NAME" ORDER BY "DATE THEY TOOK THE CLASS") = 'class 2')
ORDER BY "STUDENT NAME", "DATE THEY TOOK THE CLASS";

思路说明 #

1. 构建选课序列：通过PARTITION BY "STUDENT NAME"按学生分组，ORDER BY "DATE THEY TOOK THE CLASS"按选课日期排序，用LEAD()获取每条记录的后续选课班级信息。
2. 筛选目标记录：
   • 匹配所有class2且下一个选课是class3的记录；
   • 匹配所有class3且上一个选课是class2的记录；
3. 排序输出：按学生姓名和选课日期排序，得到符合要求的完整选课记录组合。

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