如何按序列规则计算数据集内各ID对应的下一个临床里程碑

前提假设 #

先统一假设你使用的数据集表名为clinical_milestones，包含核心字段：

• id：案例ID
• sequence_num：序列编号（数值越小代表里程碑顺序越靠前）
• milestone_name：临床里程碑名称
• actual：实际完成日期（空值代表未完成/无有效数据）
• planned：计划完成日期

核心逻辑说明 #

你之前考虑用LAG函数的思路容易受空值、日期倒序/重复的影响，更稳妥的方式是完全以序列编号为排序基准，忽略actual日期的异常问题，步骤如下：

1. 对每个ID的所有里程碑标记已完成状态：只要actual不为空即视为已完成节点
2. 取每个ID已完成节点的最大sequence_num（不用最大actual日期，从根源避免日期倒序、重复的冲突问题）
3. 取每个ID在「最大已完成序列」之后的第一个里程碑，就是下一个待完成的节点
4. 兜底处理：如果该ID没有任何已完成节点（所有actual为空），就取planned不为空的序列最靠前的里程碑

参考SQL代码（兼容Hive/Spark SQL，其他SQL方言仅需调整窗口函数语法即可） #

WITH milestone_flag AS (
    SELECT 
        id,
        sequence_num,
        milestone_name,
        actual,
        planned,
        -- 标记已完成节点
        CASE WHEN actual IS NOT NULL THEN 1 ELSE 0 END AS is_completed,
        -- 取当前ID已完成节点的最大序列编号
        MAX(CASE WHEN actual IS NOT NULL THEN sequence_num ELSE NULL END) OVER(PARTITION BY id) AS max_completed_seq,
        -- 给planned非空的节点按序列排序，用于兜底场景
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY CASE WHEN planned IS NOT NULL THEN 0 ELSE 1 END, sequence_num) AS planned_rn
    FROM clinical_milestones
)
SELECT 
    DISTINCT id,
    CASE 
        WHEN max_completed_seq IS NOT NULL THEN FIRST_VALUE(milestone_name) OVER(PARTITION BY id ORDER BY sequence_num ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
        ELSE MAX(CASE WHEN planned_rn = 1 THEN milestone_name ELSE NULL END) OVER(PARTITION BY id)
    END AS next_milestone
FROM milestone_flag
WHERE 
    (max_completed_seq IS NOT NULL AND sequence_num > max_completed_seq)
    OR (max_completed_seq IS NULL AND planned_rn = 1)

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