Teradata SQL历史表压缩：合并MTN_STATUS_IND状态记录

针对同一KEY_c下连续出现相同MTN_STATUS_IND的多条历史记录，可通过SQL窗口函数分组压缩的方式，保留最小EFF_DT和最大EXP_DT，同时维持状态变更的时序逻辑，具体实现如下：

核心SQL实现 #

WITH ranked_data AS (
    SELECT 
        KEY_c,
        MTN_STATUS_IND,
        EFF_DT,
        EXP_DT,
        -- 生成分组标识：连续相同状态的记录会被归为同一group_id
        ROW_NUMBER() OVER (PARTITION BY KEY_c ORDER BY EFF_DT) 
        - ROW_NUMBER() OVER (PARTITION BY KEY_c, MTN_STATUS_IND ORDER BY EFF_DT) AS group_id
    FROM your_history_table
),
compressed_results AS (
    SELECT 
        KEY_c,
        MTN_STATUS_IND,
        MIN(EFF_DT) AS MIN_EFF_DT,
        MAX(EXP_DT) AS MAX_EXP_DT
    FROM ranked_data
    GROUP BY KEY_c, MTN_STATUS_IND, group_id
    ORDER BY KEY_c, MIN_EFF_DT
)
SELECT * FROM compressed_results;

逻辑说明 #

1. 分组标识生成：
   第一个CTEranked_data中，通过两个ROW_NUMBER()窗口函数的差值生成group_id：

   • 第一个ROW_NUMBER()按KEY_c分组、EFF_DT排序，给每条记录全局编号；
   • 第二个ROW_NUMBER()按KEY_c+MTN_STATUS_IND分组、EFF_DT排序，给同状态的记录编号；
   • 当状态连续不变时，两个编号的差值固定；状态变更时，差值会跳变，从而将连续同状态的记录划分为同一组。

2. 记录压缩：
   第二个CTEcompressed_results按KEY_c、MTN_STATUS_IND和group_id分组，取每组的最小生效日期和最大失效日期，完成连续同状态记录的合并。

注意事项 #

• 确保历史表中KEY_c下的记录按EFF_DT递增排列，无日期范围重叠的异常数据（若存在重叠，需先通过排序或去重处理）；
• 不同数据库（如MySQL、PostgreSQL、SQL Server）均支持上述窗口函数，无需额外适配。

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