Teradata时态表错误插入/删除产生的脏数据清理方案求助

这个需求属于经典的时间序列岛屿问题（Gaps and Islands），可以通过窗口函数生成分组标识的方式解决，逻辑完全适配你的场景：不管时间段是否重叠/相邻，仅合并按时间排序后连续出现的、所有非时间列完全一致的记录，中间属性发生过变化的同属性记录会被分到不同分组，不会出现跨区间合并的问题。

实现步骤及示例代码 #

假设你的时态表为temporal_table，需要校验一致的非时间列为col1、col2、col3，时态字段为eff_vt_dttm，按主体IDuser_id分区处理：

WITH mark_group AS (
    SELECT
        user_id,
        col1,
        col2,
        col3,
        eff_vt_dttm,
        -- 标记当前行与上一行非时间属性是否一致，不一致则开启新分组
        CASE
            WHEN (col1, col2, col3) = LAG((col1, col2, col3)) OVER (PARTITION BY user_id ORDER BY BEGIN(eff_vt_dttm))
            THEN 0
            ELSE 1
        END AS new_group_flag
    FROM temporal_table
),
calc_group_id AS (
    SELECT
        *,
        -- 累加标记生成唯一分组ID，同分组内为可合并的连续同属性记录
        SUM(new_group_flag) OVER (PARTITION BY user_id ORDER BY BEGIN(eff_vt_dttm)) AS group_id
    FROM mark_group
)
-- 聚合每个分组的最小开始时间、最大结束时间
SELECT
    user_id,
    col1,
    col2,
    col3,
    PERIOD(MIN(BEGIN(eff_vt_dttm)), MAX(END(eff_vt_dttm))) AS merged_eff_vt_dttm
FROM calc_group_id
GROUP BY user_id, col1, col2, col3, group_id
ORDER BY user_id, MIN(BEGIN(eff_vt_dttm));

逻辑说明 #

• 先按主体维度分区、按时间段起始时间升序排序，保证记录的时间顺序正确
• 每遇到和上一行非时间属性不一致的记录，就生成一个新分组的标记
• 对标记做累加得到分组ID，同一个ID对应的行都是时间线上连续出现的同属性记录，哪怕中间时间段有间隙也可以合并
• 最终按分组聚合生成新的时间段，即可得到你预期的结果，不会出现1、2行和5、6行跨中间不同属性行合并的问题

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