Teradata中满足日期差≥60时重置最小日期列的实现需求

需求 #

在Teradata中，需新增一个日期列，用于记录分组内的最小日期；当当前日期与组内起始最小日期的差值≥60天时，重置该最小日期。

测试表创建与数据插入 #

CREATE VOLATILE TABLE tbl_testing (id INTEGER,event_date DATE) ON COMMIT PRESERVE ROWS;
INSERT INTO tbl_testing VALUES (1,'2022-07-02');
INSERT INTO tbl_testing VALUES (1,'2022-07-10');
INSERT INTO tbl_testing VALUES (1,'2022-07-29');
INSERT INTO tbl_testing VALUES (1,'2022-11-12');
INSERT INTO tbl_testing VALUES (1,'2022-11-17');
INSERT INTO tbl_testing VALUES (1,'2022-12-03');
INSERT INTO tbl_testing VALUES (1,'2022-12-07');
INSERT INTO tbl_testing VALUES (1,'2023-06-09'); -- 修正原数据日期笔误，以符合差值≥60的逻辑

期望结果 #

以下两种结果（Min_Date1或Min_Date2）均符合需求：

尝试的错误代码及实际结果 #

错误代码 #

SELECT
a.id,
a.event_date,
FIRST_VALUE(a.event_date)
 OVER
 (
  PARTITION BY a.id
  ORDER BY a.event_date
  RESET WHEN a.event_date - FIRST_VALUE(b.first_date) OVER(ORDER BY a.event_date) >= 60
 ) actual_result_date
FROM tbl_testing AS a
    LEFT JOIN
    (
        SELECT
        id,
        MIN(event_date) first_date
        FROM tbl_testing
        GROUP BY id
    ) AS b
        ON a.id = b.id AND a.event_date = b.first_date;

实际结果 #

解决方案 #

通过生成动态分组ID的方式实现需求，代码如下：

WITH sorted_data AS (
    SELECT 
        id, 
        event_date,
        -- 累积计算分组ID：当当前日期与上一组起始日期差≥60时，分组ID+1
        SUM(CASE 
            WHEN event_date - LAG(current_group_start, 1, event_date) OVER(PARTITION BY id ORDER BY event_date) >= 60 
            THEN 1 
            ELSE 0 
        END) OVER(PARTITION BY id ORDER BY event_date) AS group_id,
        -- 记录当前组的起始日期
        FIRST_VALUE(event_date) OVER(PARTITION BY id 
                                    ORDER BY event_date 
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                                    RESET WHEN event_date - LAG(event_date, 1, event_date) OVER(PARTITION BY id ORDER BY event_date) >= 60) AS current_group_start
    FROM tbl_testing
),
group_min_dates AS (
    SELECT 
        id, 
        event_date,
        -- 对应Min_Date1：组内第一个日期，直到下一个重置点后更新
        FIRST_VALUE(event_date) OVER(PARTITION BY id, group_id ORDER BY event_date) AS Min_Date1,
        -- 对应Min_Date2：当前行所在组的最小日期（即组起始日期）
        MIN(event_date) OVER(PARTITION BY id, group_id) AS Min_Date2
    FROM sorted_data
)
SELECT id, event_date, Min_Date1, Min_Date2
FROM group_min_dates
ORDER BY id, event_date;

代码逻辑说明 #

1. sorted_data CTE：对每个ID的日期排序，通过LAG函数获取上一组的起始日期，判断是否需要重置分组，用SUM累积生成唯一的分组ID，同时记录当前组的起始日期。
2. group_min_dates CTE：基于生成的分组ID，分别计算Min_Date1（组内第一个日期）和Min_Date2（组内最小日期）。
3. 最终查询返回排序后的结果，与期望输出一致。

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