SQL实现基于历史行的Occurrence与Instance增量计算需求

需求核心规则 #

• 仅当Paycode = 'Sick'时，显示Occurrence和Instance，否则为null
• 病假后仅间隔1天无病假（Paycode = null），再次病假时Instance继续累加，Occurrence保持不变
• 病假后间隔≥2天无病假，再次病假时视为新的Occurrence，Occurrence递增，Instance重置为1

分步实现SQL逻辑 #

通过窗口函数（LAG()、SUM()、ROW_NUMBER()）结合条件判断即可实现需求，以下是兼容多数SQL方言的代码（以SQL Server为例，其他方言可微调日期差函数）：

WITH cte_sick_flags AS (
    SELECT 
        Date,
        ID,
        Paycode,
        -- 标记当前行是否为病假行
        CASE WHEN Paycode = 'Sick' THEN 1 ELSE 0 END AS is_sick,
        -- 获取上一次病假的日期（仅筛选病假行）
        LAG(Date) OVER (PARTITION BY ID ORDER BY Date) FILTER (WHERE Paycode = 'Sick') AS prev_sick_date
    FROM MyTable
),
cte_occurrence_start AS (
    SELECT 
        *,
        -- 判断当前病假是否为新Occurrence的起点：
        -- 1. 是首次病假；2. 与上一次病假间隔超过2天（即中间≥2天无病假）
        CASE 
            WHEN is_sick = 1 AND (prev_sick_date IS NULL OR DATEDIFF(day, prev_sick_date, Date) > 2)
            THEN 1 
            ELSE 0 
        END AS new_occurrence_flag
    FROM cte_sick_flags
),
cte_occurrence_numbers AS (
    SELECT 
        *,
        -- 累计计算Occurrence编号
        SUM(new_occurrence_flag) OVER (PARTITION BY ID ORDER BY Date) AS Occurrence
    FROM cte_occurrence_start
)
SELECT 
    Date,
    ID,
    Paycode,
    -- 非病假行Occurrence设为null
    CASE WHEN is_sick = 1 THEN Occurrence ELSE NULL END AS Occurrence,
    -- 同一Occurrence内的病假行计数，非病假行设为null
    CASE WHEN is_sick = 1 THEN ROW_NUMBER() OVER (PARTITION BY ID, Occurrence ORDER BY Date) ELSE NULL END AS Instance
FROM cte_occurrence_numbers
ORDER BY ID, Date;

代码逻辑说明 #

1. cte_sick_flags：标记病假行，并获取当前病假行的上一次病假日期，用于判断间隔天数
2. cte_occurrence_start：识别每个病假行是否为新Occurrence的起点——首次病假或与上一次病假间隔超过2天（即中间至少2天无病假）
3. cte_occurrence_numbers：通过累计求和new_occurrence_flag，生成每个病假行的Occurrence编号
4. 最终查询：对非病假行的Occurrence和Instance设为null，同时在同一Occurrence内生成连续的Instance计数

原始代码问题修正 #

• 未按用户ID（ID）分区，会导致多用户计数混淆
• 未使用LAG()追踪上一次病假状态，无法判断是否需要重置Instance和递增Occurrence
• 全局SUM()无法实现Instance的重置逻辑，需按ID和Occurrence分组后使用ROW_NUMBER()

执行上述代码后，将得到你期望的输出结果。

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