Teradata查询：基于STS=03条件计算起止日期的问题

需求与数据 #

需要从数据集中提取每个ID下连续STS=03状态段的起止日期：

• 起始日期：该段首次出现STS=03的日期
• 结束日期：该段之后第一个非STS=03状态的变更日期

原始数据（HAVE） #

ID               CHGDATE          STS
101              11/21/2022        00
101              11/22/2022        03
101              11/23/2022        03
101              11/24/2022        03
101              03/08/2023        04
102              08/05/2022        03
102              08/08/2022        04
102              11/10/2022        03
102              11/17/2022        00

期望结果（WANT） #

ID            STARTDATE           ENDDATE
101           11/22/2022           03/08/2023
102           08/05/2022           08/08/2022
102           11/10/2022           11/17/2022

现有查询的问题 #

你编写的查询仅筛选了STS=03的记录，使用LEAD只能获取同ID下下一条STS=03的日期，无法捕捉到连续03段之后的第一个非03日期，因此对ID=101这类存在连续03的情况无效，会生成多条冗余记录且结束日期错误。

解决方案 #

方法一：窗口函数标记段起始+子查询找结束日期 #

通过窗口函数标记每个连续03段的起始记录，再通过子查询找到该段之后的第一个非03日期作为结束日期：

WITH segments AS (
    SELECT 
        ID,
        CHGDATE,
        STS,
        -- 标记连续03段的起始记录：当前是03且上一条不是03，或是第一条记录
        CASE WHEN STS = '03' AND (LAG(STS) OVER (PARTITION BY ID ORDER BY CHGDATE) != '03' OR LAG(STS) OVER (PARTITION BY ID ORDER BY CHGDATE) IS NULL) THEN 1 ELSE 0 END AS is_segment_start
    FROM HAVE
)
SELECT 
    ID,
    CHGDATE AS STARTDATE,
    -- 找到当前起始记录之后的第一个非03日期
    (SELECT MIN(CHGDATE) FROM segments s2 WHERE s2.ID = s1.ID AND s2.CHGDATE > s1.CHGDATE AND s2.STS != '03') AS ENDDATE
FROM segments s1
WHERE is_segment_start = 1
ORDER BY ID, STARTDATE;

方法二：分组标记+关联取结束日期 #

先给每个连续03段分配分组ID，再通过分组提取起始日期，最后关联找到对应结束日期：

WITH grouped_data AS (
    SELECT 
        ID,
        CHGDATE,
        STS,
        -- 为连续03段分配唯一分组ID
        SUM(CASE WHEN STS = '03' AND (LAG(STS) OVER (PARTITION BY ID ORDER BY CHGDATE) != '03' OR LAG(STS) OVER (PARTITION BY ID ORDER BY CHGDATE) IS NULL) THEN 1 ELSE 0 END) OVER (PARTITION BY ID ORDER BY CHGDATE) AS group_id
    FROM HAVE
),
start_dates AS (
    SELECT ID, group_id, MIN(CHGDATE) AS STARTDATE
    FROM grouped_data
    WHERE STS = '03'
    GROUP BY ID, group_id
),
end_dates AS (
    SELECT 
        gd.ID,
        gd.group_id,
        MIN(gd.CHGDATE) AS ENDDATE
    FROM grouped_data gd
    JOIN start_dates sd ON gd.ID = sd.ID AND gd.CHGDATE > sd.STARTDATE
    WHERE gd.STS != '03'
    GROUP BY gd.ID, gd.group_id
)
SELECT sd.ID, sd.STARTDATE, ed.ENDDATE
FROM start_dates sd
LEFT JOIN end_dates ed ON sd.ID = ed.ID AND sd.group_id = ed.group_id
ORDER BY sd.ID, sd.STARTDATE;

两种方法都能正确输出期望结果，方法一代码更简洁，适合数据量不大的场景；方法二逻辑更清晰，适合复杂的分段需求。

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