如何使用ROW_NUMBER() PARTITION BY()筛选用户当前在读课程？

问题说明 #

需要从PROGRAM_DATE_TRACK数据集里筛选出用户当前正在参与的课程，排除已完成的课程，以及那些已经有后续完成记录的历史未完成课程（比如用户2在2022年8月加入的芭蕾课，后续在2023年5月完成了同课程的学习，这条旧记录就属于已结束的历史课程，需要排除）。

数据集字段说明：

• PERSON_ID：用户ID
• PROGRAM_JOINED：参与的课程名称
• EFFECTIVE_DATE：课程生效日期
• PROGRAM_FINISHED_FLAG：课程完成标记（Y=已完成，N=未完成）

原数据集内容：

目标结果 #

只保留用户当前在读的课程，最终结果如下：

原代码的问题 #

你写的SQL存在两个问题：

1. 字段名拼写错误：EFFECTIVE DATE应该是EFFECTIVE_DATE，排序时的EFFECTIVE DATEE也是拼写错误；
2. 逻辑漏洞：只筛选了PROGRAM_FINISHED_FLAG = 'N'的记录，但没有考虑同一用户同一课程有后续已完成的情况——比如用户2的芭蕾课，2022年的记录是未完成，但2023年已经完成了同课程，这条旧记录应该被排除。

原代码：

SELECT
    PERSON_ID,
    PROGRAM_JOINED,
    EFFECTIVE DATE,
    PROGRAM_FINISHED_FLAG
    ROW_NUMBER() OVER (PARTITION BY PERSON_ID, PROGRAM_JOINED  ORDER BY EFFECTIVE DATEE DESC) AS RANK
FROM PROGRAM_DATE_TRACK
WHERE PROGRAM_FINISHED_FLAG = 'N'

正确SQL实现 #

我们需要先对每个用户的同一课程，按生效日期倒序排序，标记出最新的一条记录，然后只保留这条最新记录中未完成的课程：

WITH ranked_courses AS (
    SELECT
        PERSON_ID,
        PROGRAM_JOINED,
        EFFECTIVE_DATE,
        PROGRAM_FINISHED_FLAG,
        ROW_NUMBER() OVER (PARTITION BY PERSON_ID, PROGRAM_JOINED ORDER BY EFFECTIVE_DATE DESC) AS rn
    FROM PROGRAM_DATE_TRACK
)
SELECT
    PERSON_ID,
    PROGRAM_JOINED,
    EFFECTIVE_DATE,
    PROGRAM_FINISHED_FLAG
FROM ranked_courses
WHERE rn = 1 AND PROGRAM_FINISHED_FLAG = 'N';

逻辑解释 #

1. 用CTE（公共表表达式）给每个用户的同一课程按EFFECTIVE_DATE倒序排，rn=1代表该用户该课程的最新记录；
2. 最后筛选出最新记录且完成标记为N的课程——这样就能排除那些虽然标记未完成，但后续有同课程完成记录的历史课程（比如用户2的2022年芭蕾课，它的rn是2，会被过滤掉）。

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