基于INDX日期验证CODE年度季度覆盖及连续有效时间段

数据表结构 #

• Table1：字段包括 ID、INDX，其中INDX为验证Table2中CODE记录的基准日期
• Table2：字段包括 ID、CODE、DAT、QRTR，其中DAT是CODE的记录日期，QRTR对应记录的年度季度（如2024Q1）

业务需求 #

1. 判定CODE = 'A'的记录是否存在于INDX日期向前回溯300天的时间范围内
2. 若存在符合条件的CODE = 'A'记录，需验证：从该范围内第一条CODE = 'A'记录开始，每一个自然年度至少存在一条对应年度的CODE = 'A'记录（包含后续所有记录）
3. 最终输出满足上述要求的连续有效时间段，输出字段为 ID、START（有效区间起始日期）、END（有效区间结束日期）

当前实现困境 #

已编写的SQL仅能单独判断每个年度是否存在至少一条CODE = 'A'的记录，但无法生成符合要求的连续有效时间段，也无法识别是否存在新的有效周期。

以下SQL通过CTE分层处理，实现连续有效时间段的计算：

WITH base_data AS (
    -- 关联表并筛选每个ID在基准前300天内的第一条CODE='A'记录
    SELECT 
        t1.ID,
        t1.INDX,
        MIN(t2.DAT) AS first_a_date
    FROM Table1 t1
    LEFT JOIN Table2 t2 
        ON t1.ID = t2.ID 
        AND t2.CODE = 'A'
        AND t2.DAT >= DATEADD(DAY, -300, t1.INDX)
        AND t2.DAT <= t1.INDX
    GROUP BY t1.ID, t1.INDX
    HAVING MIN(t2.DAT) IS NOT NULL -- 仅保留存在有效A记录的ID
),
annual_check AS (
    -- 按年度统计每个ID的A记录情况，标记年度是否有效
    SELECT 
        bd.ID,
        bd.first_a_date,
        YEAR(t2.DAT) AS record_year,
        CASE WHEN COUNT(*) >= 1 THEN 1 ELSE 0 END AS is_valid_year
    FROM base_data bd
    JOIN Table2 t2 
        ON bd.ID = t2.ID 
        AND t2.CODE = 'A'
        AND t2.DAT >= bd.first_a_date
    GROUP BY bd.ID, bd.first_a_date, YEAR(t2.DAT)
),
valid_periods AS (
    -- 识别连续有效年度的分组，生成暂存的区间结束日期
    SELECT 
        ID,
        first_a_date AS period_start,
        DATEFROMPARTS(record_year, 12, 31) AS temp_period_end,
        -- 用累计求和标记有效区间分组：遇到无效年度则分组ID+1
        SUM(CASE WHEN is_valid_year = 0 THEN 1 ELSE 0 END) OVER (
            PARTITION BY ID 
            ORDER BY record_year 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS gap_group
    FROM annual_check
),
final_periods AS (
    -- 合并同分组的连续区间，得到最终有效时间段
    SELECT 
        ID,
        MIN(period_start) AS START,
        MAX(temp_period_end) AS END
    FROM valid_periods
    WHERE is_valid_year = 1
    GROUP BY ID, gap_group
)
SELECT ID, START, END FROM final_periods;

代码逻辑说明 #

1. base_data：完成基准时间范围的筛选，锁定每个ID的第一条有效CODE='A'记录，过滤掉无符合条件记录的ID
2. annual_check：从第一条有效A记录开始，按年度统计并标记该年度是否满足至少一条A记录的要求
3. valid_periods：通过窗口函数的累计求和，将连续的有效年度归为同一分组，同时生成每个年度的结束日期（当年12月31日）
4. final_periods：按分组合并，得到每个ID的连续有效时间段的起始和结束日期

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