基于30天间隔的Teradata SQL客户交易分组需求

需求明确：按客户对交易记录分组，若某笔交易与该客户的上一笔交易间隔≤30天，则归为同一组；若间隔＞30天，则启动一个新组。

原始交易数据表（transaction_data） #

预期分组结果表 #

Teradata SQL实现代码 #

WITH transaction_with_lag AS (
    SELECT
        customer_id,
        transaction_date,
        transaction_amount,
        LAG(transaction_date) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS prev_transaction_date
    FROM transaction_data
),
group_flags AS (
    SELECT
        customer_id,
        transaction_date,
        transaction_amount,
        CASE
            WHEN prev_transaction_date IS NULL THEN 1  -- 客户第一笔交易，作为新组起始
            WHEN transaction_date - prev_transaction_date > 30 THEN 1  -- 间隔超30天，启动新组
            ELSE 0  -- 间隔≤30天，归为上一组
        END AS is_new_group
    FROM transaction_with_lag
)
SELECT
    customer_id,
    transaction_date,
    transaction_amount,
    SUM(is_new_group) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS UNBOUNDED PRECEDING) AS group_id
FROM group_flags
ORDER BY customer_id, transaction_date;

逻辑说明 #

1. transaction_with_lag CTE：用LAG()窗口函数按客户分组、交易日期排序，获取每笔交易的上一笔交易日期。
2. group_flags CTE：通过CASE标记新组起始——第一笔交易或与上一笔间隔超30天的交易标记为1，其余为0。
3. 最终查询：对每个客户的标记值做累加，累加结果就是组ID，同一组内累加值不变，新组启动时累加值加1。

Teradata中日期直接相减会返回间隔天数，这个语法特性简化了间隔计算，无需额外函数转换。

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