Teradata中提取指定状态码的生效起始与变更结束日期

需求说明 #

• 针对包含ID、STCODE（状态码）、DATE字段的数据集，仅处理状态码为3或8的记录
• 连续的3/8状态视为一个区间，取区间内首次出现的日期作为STDATE
• 当状态变更为3/8以外的值时，该变更日期作为对应区间的ENDDATE
• 同一ID下非连续的3/8状态需拆分为独立记录

示例数据 #

输入数据 #

ID         STCODE                 DATE
  101         3                     10/21/2022
  101         3                     10/22/2022
  101         3                     10/23/2022
  101         6                     10/25/2022
  101         3                     10/26/2022
  101         7                     10/27/2022
  102         8                     10/25/2022
  102         5                     10/26/2022

期望输出 #

ID           STDATE              ENDDATE
    101        10/21/2022            10/25/2022
    101        10/26/2022            10/27/2022
    102        10/25/2022            10/26/2022

原SQL问题分析 #

你尝试的SQL存在两个核心问题：

1. 仅按ID分组，会把同一ID下所有3/8状态合并成一条记录，无法区分非连续的状态区间
2. 窗口函数的分区逻辑错误，PARTITION BY STCODE无法追踪同一ID内的状态变化，达不到分组连续区间的目的

WITH STS AS (
    SELECT 
    ID,
    STCODE,
    DATE,
    ROW_NUMBER() OVER (ORDER BY DATE) AS rn,
    ROW_NUMBER() OVER (PARTITION BY STCODE ORDER BY DATE) AS str_rn
FROM 
    MyTable
)
 SELECT 
  ID,
  MIN(DATE) AS STDATE,
  MAX(DATE) AS ENDDATE
 FROM STS
  WHERE 
  STCODE in (3,8)
  GROUP BY ID

正确SQL实现 #

WITH ranked_data AS (
    SELECT 
        ID,
        STCODE,
        DATE,
        CASE WHEN STCODE IN (3,8) THEN 1 ELSE 0 END AS is_target,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE) AS rn
    FROM MyTable
),
grouped_targets AS (
    SELECT 
        ID,
        DATE,
        -- 生成连续目标状态的分组ID，非连续区间会分配不同ID
        SUM(CASE 
            WHEN is_target = 1 AND (rn = 1 OR LAG(is_target) OVER (PARTITION BY ID ORDER BY rn) = 0) 
            THEN 1 ELSE 0 
        END) OVER (PARTITION BY ID ORDER BY rn) AS group_id
    FROM ranked_data
    WHERE is_target = 1
),
non_target_dates AS (
    SELECT 
        ID,
        DATE AS end_date,
        rn
    FROM ranked_data
    WHERE is_target = 0
)
SELECT 
    gt.ID,
    MIN(gt.DATE) AS STDATE,
    MIN(ntd.end_date) AS ENDDATE
FROM grouped_targets gt
LEFT JOIN non_target_dates ntd 
    ON gt.ID = ntd.ID 
    AND ntd.rn > (SELECT MAX(rn) FROM ranked_data WHERE ID = gt.ID AND DATE <= MAX(gt.DATE))
GROUP BY gt.ID, gt.group_id
ORDER BY gt.ID, STDATE;

逻辑说明 #

1. ranked_data：标记每条记录是否为目标状态（3/8），并按ID+DATE排序生成行号，用于追踪状态顺序
2. grouped_targets：通过窗口函数计算连续目标状态的分组ID，当状态从非目标切换为目标时，分组ID递增，确保非连续区间被拆分
3. non_target_dates：筛选所有非目标状态的记录，用于获取区间结束日期
4. 最终关联分组与后续最早的非目标状态日期，取分组内最早日期作为STDATE，后续最早非目标日期作为ENDDATE

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