计算非连续注册月份差值：查询Enrollment间隔的实现方法

一、依赖Consecutive_Months列的实现 #

你可以通过以下SQL逻辑实现需求：

1. 为每个ID内的连续序列分配唯一标识；
2. 提取每个序列的结束月份（该序列中Consecutive_Months最大的记录对应的Enrollment_Month）；
3. 将新序列的起始行与前一序列的结束行关联，计算月份差值。

示例SQL（MySQL环境）：

WITH sequence_groups AS (
    -- 给每个ID下的连续序列分配组ID
    SELECT 
        ID,
        Enrollment_Month,
        Consecutive_Months,
        SUM(CASE WHEN Consecutive_Months = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY ID ORDER BY Enrollment_Month) AS seq_id
    FROM enrollment_table
),
sequence_end_dates AS (
    -- 获取每个序列的最后一个月份
    SELECT 
        ID,
        seq_id,
        MAX(Enrollment_Month) AS seq_end_month
    FROM sequence_groups
    GROUP BY ID, seq_id
)
-- 计算间隔：只针对非第一个序列的起始行
SELECT 
    s.ID,
    s.Enrollment_Month AS current_seq_start_month,
    prev_seq.seq_end_month AS last_seq_end_month,
    -- 用PERIOD_DIFF计算YYYYMM格式的月份差
    PERIOD_DIFF(
        CAST(s.Enrollment_Month AS CHAR),
        CAST(prev_seq.seq_end_month AS CHAR)
    ) AS month_gap
FROM sequence_groups s
JOIN sequence_end_dates prev_seq 
    ON s.ID = prev_seq.ID 
    AND s.seq_id = prev_seq.seq_id + 1
WHERE s.Consecutive_Months = 1;

逻辑说明：

• sequence_groups通过累加Consecutive_Months=1的次数，给每个连续序列生成唯一的seq_id；
• sequence_end_dates提取每个序列的结束月份；
• 最后关联新序列起始行和前序结束行，用PERIOD_DIFF直接计算YYYYMM格式的月份差值。

二、不依赖Consecutive_Months列的实现 #

如果没有Consecutive_Months列，可以直接通过日期判断识别连续序列，步骤如下：

1. 将Enrollment_Month转换为可计算的日期格式，计算当前月与上一个月的差值；
2. 当差值不为1时，标记为新序列的开始；
3. 提取每个序列的结束月份，再计算间隔。

示例SQL（MySQL环境）：

WITH month_conversion AS (
    -- 转换日期格式，并计算当前月与上月的差值
    SELECT 
        ID,
        Enrollment_Month,
        STR_TO_DATE(CONCAT(Enrollment_Month, '01'), '%Y%m%d') AS enroll_date,
        PERIOD_DIFF(
            CAST(Enrollment_Month AS CHAR),
            CAST(LAG(Enrollment_Month) OVER (PARTITION BY ID ORDER BY Enrollment_Month) AS CHAR)
        ) AS month_diff
    FROM enrollment_table
),
sequence_groups AS (
    -- 给每个连续序列分配组ID：首次记录或月份差不为1时，序列编号加1
    SELECT 
        *,
        SUM(CASE WHEN month_diff IS NULL OR month_diff != 1 THEN 1 ELSE 0 END) OVER (PARTITION BY ID ORDER BY enroll_date) AS seq_id
    FROM month_conversion
),
sequence_end_dates AS (
    -- 获取每个序列的结束月份
    SELECT 
        ID,
        seq_id,
        MAX(Enrollment_Month) AS seq_end_month
    FROM sequence_groups
    GROUP BY ID, seq_id
)
-- 计算间隔：只针对非第一个序列的起始行
SELECT 
    s.ID,
    s.Enrollment_Month AS current_seq_start_month,
    prev_seq.seq_end_month AS last_seq_end_month,
    PERIOD_DIFF(
        CAST(s.Enrollment_Month AS CHAR),
        CAST(prev_seq.seq_end_month AS CHAR)
    ) AS month_gap
FROM sequence_groups s
JOIN sequence_end_dates prev_seq 
    ON s.ID = prev_seq.ID 
    AND s.seq_id = prev_seq.seq_id + 1
WHERE (s.month_diff IS NULL OR s.month_diff != 1);

逻辑说明：

• month_conversion用LAG窗口函数获取上一个月份，计算与当前月的差值；
• sequence_groups通过累加“非连续”标记，生成序列ID；
• 后续步骤和依赖列的方法一致，最终得到序列间的间隔月份。

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