T-SQL 为指定亚人群构建2019-2020连续参保区间的实现问题求解

核心实现逻辑 #

方案从数据筛选、边界拆分、连续段聚合三个环节做机制设计，完全覆盖现有逻辑的三类缺陷，无需重复编写分年度运行的重复代码。

关键设计规则 #

• 源头严格过滤范围：只拉取2019-2020自然年区间内、归属Adult Expansion亚人群的参保记录，从根本上杜绝非目标亚人群段被错纳入聚合结果的问题。
• 强制做自然年边界裁切：对跨2019/2020年度的参保段直接拆分为两个独立的年度内记录，从机制上避免全量聚合时跨年度合并连续段的错误，不再需要拆分两个年度跑重复逻辑。
• 基于孤岛检测的连续段合并：对同一CLIENTID下、同一年度内的目标参保段做时间间断识别，只要相邻段存在时间间隔就拆分为独立区间，即使用户中途切换其他亚人群、后续再切回目标亚人群，对应的参保段也会被独立识别，不会出现CLIENTID记录丢失的问题。

可落地实现SQL #

WITH base_elig AS (
    -- 第一步：源头过滤目标范围，排除非目标亚人群、时间范围外的无效记录
    SELECT 
        CLIENTID,
        -- 按自然年边界裁切参保时间段，跨年度的段自动拆分为两个独立记录
        GREATEST(ELIGBEGIN, '2019-01-01'::DATE) AS s2019,
        LEAST(ELIGEND, '2019-12-31'::DATE) AS e2019,
        GREATEST(ELIGBEGIN, '2020-01-01'::DATE) AS s2020,
        LEAST(ELIGEND, '2020-12-31'::DATE) AS e2020
    FROM source_eligibility_table
    WHERE 
        -- 仅保留和2019、2020年有时间交集的记录
        ELIGEND >= '2019-01-01' 
        AND ELIGBEGIN <= '2020-12-31'
        -- 仅保留目标亚人群记录
        AND DEMONSTRATION_POPULATION = 'Adult Expansion'
),
year_split_records AS (
    -- 第二步：将裁切后的年度记录打平为统一格式
    -- 提取2019年有效段
    SELECT CLIENTID, 2019 AS cal_year, s2019 AS seg_start, e2019 AS seg_end
    FROM base_elig
    WHERE s2019 <= e2019 -- 过滤裁切后无有效时间的空段
    UNION ALL
    -- 提取2020年有效段
    SELECT CLIENTID, 2020 AS cal_year, s2020 AS seg_start, e2020 AS seg_end
    FROM base_elig
    WHERE s2020 <= e2020 -- 过滤裁切后无有效时间的空段
),
gap_calc AS (
    -- 第三步：计算相邻段的时间间隔，识别连续段的断点
    SELECT
        CLIENTID,
        cal_year,
        seg_start,
        seg_end,
        DATEDIFF(
            day,
            -- 取同CLIENTID、同年度下上一段的结束时间
            LAG(seg_end, 1, '1900-01-01'::DATE) OVER (
                PARTITION BY CLIENTID, cal_year 
                ORDER BY seg_start
            ),
            seg_start
        ) AS gap_to_prev_seg
    FROM year_split_records
),
island_group AS (
    -- 第四步：给每个连续无间断的参保段分配独立组ID
    SELECT
        CLIENTID,
        cal_year,
        seg_start,
        seg_end,
        SUM(CASE WHEN gap_to_prev_seg > 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY CLIENTID, cal_year 
            ORDER BY seg_start
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM gap_calc
)
-- 第五步：聚合输出最终结果，生成顺序编号
SELECT
    CLIENTID,
    cal_year AS calendar_year,
    MIN(seg_start) AS continuous_elig_start,
    MAX(seg_end) AS continuous_elig_end,
    ROW_NUMBER() OVER (
        PARTITION BY CLIENTID, cal_year 
        ORDER BY MIN(seg_start)
    ) AS Sequence_ID
FROM island_group
GROUP BY CLIENTID, cal_year, group_id
ORDER BY CLIENTID, calendar_year, Sequence_ID;

问题匹配验证 #

• 不会触发跨年度合并的错误：所有参与聚合的记录都被限制在同一年度内，2019年末和2020年初的两段连续记录会被拆分为两个独立的年度内段，最终作为两个独立区间输出，不需要分年度重复运行逻辑。
• 不会丢失切换过其他亚人群的CLIENTID：只要是归属Adult Expansion的参保段，都会被纳入识别流程，即使用户中间存在其他亚人群的参保记录、后续再切回目标亚人群，对应的目标段也会被识别为独立的连续段，不会出现整户记录丢失的问题。
• 亚人群过滤逻辑从源头生效：所有进入后续流程的记录都是经过筛选的目标亚人群段，不存在非目标亚人群的参保段被错误纳入聚合结果的情况。

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