Teradata如何对5秒时间窗口内的A01事件按维度分组取最小EVENT_ID

原有SQL问题说明 #

你当前的SQL按精确到秒的EVENT_TIME分组，只要时间数值不同（哪怕仅差1秒）就会被划分为独立分组，不符合「5秒内记录合并为一组」的需求。

调整后SQL实现 #

WITH event_with_ts AS (
    -- 第一步：拼接日期时间为完整时间戳，同时过滤A01事件
    SELECT 
        EVENT_ID,
        CUSTOMER,
        EVENT_CODE,
        EVENT_DATE,
        -- 如果EVENT_DATE/EVENT_TIME是字符串类型，需先转格式：
        -- TO_TIMESTAMP(EVENT_DATE || ' ' || EVENT_TIME, 'DD/MM/YYYY HH24:MI:SS') AS full_ts
        EVENT_DATE + EVENT_TIME AS full_ts -- 如果本身是DATE/TIME类型可直接拼接
    FROM MY_TABLE
    WHERE EVENT_CODE = 'A01'
),
event_with_group_flag AS (
    -- 第二步：计算和上一条记录的时间差，标记新分组的起始点
    SELECT 
        *,
        CASE 
            WHEN LAG(full_ts) OVER (PARTITION BY CUSTOMER, EVENT_CODE, EVENT_DATE ORDER BY full_ts) IS NULL 
                THEN 1 -- 分区内第一条记录，标记为新分组
            WHEN TIMESTAMPDIFF(SECOND, LAG(full_ts) OVER (PARTITION BY CUSTOMER, EVENT_CODE, EVENT_DATE ORDER BY full_ts), full_ts) > 5 
                THEN 1 -- 和上一条间隔超过5秒，标记为新分组
            ELSE 0 -- 间隔<=5秒，和上一组同组
        END AS new_group_flag
    FROM event_with_ts
),
event_with_group_id AS (
    -- 第三步：累加标记得到组号，相同组号属于同一个5秒内的记录集合
    SELECT 
        *,
        SUM(new_group_flag) OVER (PARTITION BY CUSTOMER, EVENT_CODE, EVENT_DATE ORDER BY full_ts ROWS UNBOUNDED PRECEDING) AS group_id
    FROM event_with_group_flag
)
-- 最终按组分组取最小EVENT_ID
SELECT 
    MIN(EVENT_ID) AS MIN_EVENT_ID,
    CUSTOMER,
    EVENT_CODE,
    EVENT_DATE
FROM event_with_group_id
GROUP BY CUSTOMER, EVENT_CODE, EVENT_DATE, group_id;

逻辑说明 #

• 先将日期和时间字段合并为完整的时间戳，方便计算时间间隔
• 利用LAG窗口函数获取同分组下上一条记录的时间，判断是否需要开启新分组
• 通过累加分组标记生成组号，所有5秒内连续的记录会被分配到同一个组号
• 最终按组聚合取最小的EVENT_ID即可得到去重后的结果，你给出的示例数据执行后仅会返回123456，符合预期。

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