Redshift SQL中基于动态30天窗口生成营销触达判定布尔列

1. 准备示例数据 #

假设你的交易表结构如下（可根据实际表名/列名调整）：

CREATE TABLE transactions (
    user_id VARCHAR(50),
    transaction_date DATE
);

-- 插入示例数据
INSERT INTO transactions VALUES
('user1', '2024-01-01'),
('user1', '2024-01-15'),
('user1', '2024-02-10'),
('user1', '2024-02-20'),
('user2', '2024-01-05'),
('user2', '2024-02-06'),
('user2', '2024-03-10');

2. 核心实现逻辑 #

普通窗口函数（如LAG()）无法处理这种触发后动态重置窗口的场景，这里使用Redshift支持的递归CTE逐行跟踪上一次触达日期，判断当前交易是否需要触发营销触达：

WITH ranked_transactions AS (
    -- 为每个用户的交易按日期排序，生成行号
    SELECT 
        user_id,
        transaction_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date) AS rn
    FROM transactions
),
recursive_reach AS (
    -- 递归初始条件：每个用户第一笔交易默认需触达，记录首次触达日期
    SELECT 
        user_id,
        transaction_date,
        rn,
        TRUE AS need_reach,
        transaction_date AS last_reach_date
    FROM ranked_transactions
    WHERE rn = 1

    UNION ALL

    -- 递归逻辑：逐行判断当前交易是否需触达
    SELECT 
        rt.user_id,
        rt.transaction_date,
        rt.rn,
        -- 间隔超过30天则标记需触达
        CASE WHEN DATEDIFF(day, rr.last_reach_date, rt.transaction_date) > 30 THEN TRUE ELSE FALSE END AS need_reach,
        -- 需触达则重置触达日期，否则沿用原日期
        CASE WHEN DATEDIFF(day, rr.last_reach_date, rt.transaction_date) > 30 THEN rt.transaction_date ELSE rr.last_reach_date END AS last_reach_date
    FROM ranked_transactions rt
    JOIN recursive_reach rr 
        ON rt.user_id = rr.user_id 
        AND rt.rn = rr.rn + 1
)
-- 输出最终结果
SELECT 
    user_id,
    transaction_date,
    need_reach
FROM recursive_reach
ORDER BY user_id, transaction_date;

3. 逻辑说明 #

• ranked_transactions：对每个用户的交易按日期排序生成行号，确保递归可按交易顺序逐行处理。
• recursive_reach：
  • 初始部分：每个用户的第一笔交易必然触发触达，last_reach_date设为该交易日期。
  • 递归部分：关联当前交易与上一行的结果，通过DATEDIFF计算间隔天数：
    • 间隔>30天：标记need_reach为TRUE，重置last_reach_date为当前交易日期。
    • 间隔≤30天：标记need_reach为FALSE，保持last_reach_date不变。

4. 示例输出 #

执行上述SQL后，输出结果如下：

5. 性能优化提示 #

• 确保user_id和transaction_date上有合适的索引，加速排序和关联操作。
• 若数据集极大，可考虑分批次处理，或结合Redshift的自定义聚合函数简化逻辑（实现复杂度较高）。

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