Snowflake SQL：校验受试者查询日期前3个月时间区间连续性

数据表结构与数据 #

现有一张受试者记录表，每位受试者对应1条或多条记录，包含以下字段：

• SUBJECT：受试者ID
• BEGIN_DATE：区间开始日期
• END_DATE：区间结束日期
• INQUIRY_DATE：查询日期

具体数据如下：

需求说明 #

为每位受试者判断：其INQUIRY_DATE前3个月的时间段（即[INQUIRY_DATE - 3个月, INQUIRY_DATE]）是否被BEGIN_DATE与END_DATE组成的区间连续覆盖。其中连续的定义为：相邻区间的结束日期与下一个开始日期间隔不超过14天。

• 示例：受试者1的区间从1988-01-01到2022-10-02连续，完全覆盖回溯时间段，符合要求；受试者2的区间存在超过14天的间隙，不符合要求。

现有代码片段 #

用户已写出部分Snowflake SQL代码：

with lookback as (select *, INQUIRY_DATE - interval '3 months' as look_back_3m from tbl)
select *, case when diff >= 14 then 1 else 0 end as flag from (
    select SUBJECT, BEGIN_DATE - lag(END_DATE) over(partition by subject order by BEGIN_DATE) as diff from tbl
) z

完善后的Snowflake SQL #

以下是实现需求的完整SQL：

WITH lookback AS (
    SELECT 
        *,
        INQUIRY_DATE - INTERVAL '3 months' AS look_back_3m
    FROM tbl
),
subject_intervals AS (
    SELECT 
        SUBJECT,
        BEGIN_DATE,
        END_DATE,
        look_back_3m,
        INQUIRY_DATE,
        -- 标记与回溯时间段有交集的区间，过滤完全无关的记录
        CASE 
            WHEN END_DATE < look_back_3m THEN NULL
            WHEN BEGIN_DATE > INQUIRY_DATE THEN NULL
            ELSE 1 
        END AS is_relevant
    FROM lookback
),
filtered_intervals AS (
    SELECT 
        SUBJECT,
        BEGIN_DATE,
        END_DATE,
        look_back_3m,
        INQUIRY_DATE
    FROM subject_intervals
    WHERE is_relevant = 1
    ORDER BY SUBJECT, BEGIN_DATE
),
interval_gaps AS (
    SELECT 
        SUBJECT,
        look_back_3m,
        INQUIRY_DATE,
        BEGIN_DATE,
        END_DATE,
        -- 计算当前区间与前一个区间的间隔天数
        BEGIN_DATE - LAG(END_DATE) OVER (PARTITION BY SUBJECT ORDER BY BEGIN_DATE) AS gap_days,
        -- 标记每个受试者的第一个区间
        ROW_NUMBER() OVER (PARTITION BY SUBJECT ORDER BY BEGIN_DATE) AS rn
    FROM filtered_intervals
),
subject_checks AS (
    SELECT 
        SUBJECT,
        look_back_3m,
        INQUIRY_DATE,
        -- 计算回溯起始点到第一个区间开始的间隙（若第一个区间开始晚于回溯起始）
        MAX(CASE WHEN rn = 1 THEN GREATEST(look_back_3m - BEGIN_DATE, 0) ELSE 0 END) AS start_gap,
        -- 取所有相邻区间的最大间隙
        MAX(COALESCE(gap_days, 0)) AS max_interval_gap,
        -- 最早的区间开始日期
        MIN(BEGIN_DATE) AS earliest_begin_date,
        -- 最晚的区间结束日期
        MAX(END_DATE) AS latest_end_date
    FROM interval_gaps
    GROUP BY SUBJECT, look_back_3m, INQUIRY_DATE
)
SELECT 
    SUBJECT,
    look_back_3m,
    INQUIRY_DATE,
    CASE 
        -- 判断三个核心条件：
        -- 1. 回溯起始点到第一个区间的间隙≤14天，或第一个区间开始早于等于回溯起始
        -- 2. 所有相邻区间的间隙都≤14天
        -- 3. 最后一个区间结束到查询日期的间隙≤14天，或最后一个区间结束晚于等于查询日期
        WHEN (earliest_begin_date <= look_back_3m OR start_gap <= 14)
             AND max_interval_gap <= 14
             AND (latest_end_date >= INQUIRY_DATE OR (INQUIRY_DATE - latest_end_date) <= 14)
        THEN '符合要求'
        ELSE '不符合要求'
    END AS coverage_result
FROM subject_checks;

代码逻辑说明 #

1. lookback：计算每个记录对应的回溯起始日期（查询日期往前推3个月）。
2. subject_intervals：标记出与回溯时间段有交集的区间，过滤掉完全在回溯时间段之外的无效记录。
3. filtered_intervals：仅保留有效区间，并按受试者和区间开始日期排序。
4. interval_gaps：计算相邻区间的间隔天数，同时标记每个受试者的第一个区间，方便后续判断起始间隙。
5. subject_checks：聚合每个受试者的关键指标，包括最早/最晚区间日期、最大区间间隙、起始间隙。
6. 最终查询：根据聚合指标判断是否满足连续覆盖要求，输出每位受试者的结果。

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