使用Teradata合并日期范围的技术实现问询

针对ML_ORIGINAL_RANGES表中按会员分组的连续/重叠日期范围，可通过窗口函数实现合并，具体SQL如下：

SELECT
    MEMBER_ID,
    MIN(FROM_DT) AS FROM_DT,
    MAX(TO_DT) AS TO_DT
FROM (
    SELECT
        MEMBER_ID,
        FROM_DT,
        TO_DT,
        SUM(new_group_flag) OVER (PARTITION BY MEMBER_ID ORDER BY FROM_DT, TO_DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM (
        SELECT
            MEMBER_ID,
            FROM_DT,
            TO_DT,
            CASE
                -- 组内第一行标记为新组起始
                WHEN LAG(TO_DT) OVER (PARTITION BY MEMBER_ID ORDER BY FROM_DT, TO_DT) IS NULL THEN 1
                -- 当前行起始日期晚于上一行结束日期+1天，标记为新组起始
                WHEN FROM_DT > LAG(TO_DT) OVER (PARTITION BY MEMBER_ID ORDER BY FROM_DT, TO_DT) + INTERVAL '1' DAY THEN 1
                ELSE 0
            END AS new_group_flag
        FROM ML_ORIGINAL_RANGES
    ) t1
) t2
GROUP BY MEMBER_ID, group_id
ORDER BY MEMBER_ID, FROM_DT;

执行逻辑说明 #

1. 标记新组起始：内层子查询t1通过LAG()窗口函数获取当前会员上一行的结束日期，判断当前行是否属于新的连续区间：
   • 若为会员的第一行数据，直接标记为新组
   • 若当前行的起始日期比上一行的结束日期晚超过1天，标记为新组
2. 生成组ID：中间子查询t2通过累积求和SUM()，将同一连续区间的行分配相同的group_id
3. 聚合合并：外层查询按MEMBER_ID和group_id分组，取组内最早的起始日期和最晚的结束日期，得到合并后的日期范围

预期结果 #

执行上述SQL后，将得到合并后的日期范围：

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