如何用Redshift SQL识别30天内的重复客户计划注册记录

问题核心 #

常规LAG()窗口函数仅能对比当前记录的上一条记录，无法追踪客户最近的有效注册记录，因此会出现误判。要实现需求，需用递归CTE来持续关联并更新最近的有效记录日期。

示例数据 #

CREATE TABLE customer_plans (
    customer_id INT,
    enroll_date DATE
);

INSERT INTO customer_plans VALUES
(1, '2023-01-01'),
(1, '2023-01-15'),
(1, '2023-02-20'),
(2, '2023-03-05'),
(2, '2023-03-25'),
(2, '2023-04-30');

正确实现SQL #

WITH ranked_records AS (
    -- 给每个客户的记录按注册日期排序，生成行号
    SELECT 
        customer_id,
        enroll_date,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY enroll_date) AS rn
    FROM customer_plans
),
recursive_valid AS (
    -- 锚点：每个客户的第一条记录必为有效
    SELECT 
        customer_id,
        enroll_date,
        rn,
        enroll_date AS last_valid_date,
        '有效' AS record_status
    FROM ranked_records
    WHERE rn = 1

    UNION ALL

    -- 递归处理后续记录，关联最近的有效记录日期
    SELECT 
        rr.customer_id,
        rr.enroll_date,
        rr.rn,
        -- 更新最近有效日期：仅当当前记录为有效时替换
        CASE 
            WHEN rr.enroll_date > DATEADD(day, 30, rv.last_valid_date) THEN rr.enroll_date
            ELSE rv.last_valid_date
        END AS last_valid_date,
        -- 判断当前记录状态
        CASE 
            WHEN rr.enroll_date > DATEADD(day, 30, rv.last_valid_date) THEN '有效'
            ELSE '重复'
        END AS record_status
    FROM ranked_records rr
    JOIN recursive_valid rv ON rr.customer_id = rv.customer_id AND rr.rn = rv.rn + 1
)
-- 输出最终结果
SELECT 
    customer_id,
    enroll_date,
    record_status
FROM recursive_valid
ORDER BY customer_id, enroll_date;

逻辑说明 #

1. ranked_records：为每个客户的注册记录按日期排序并生成行号，确保递归能逐行处理。
2. recursive_valid：
   • 锚点部分取每个客户的第一条记录，标记为有效，并将其日期设为初始的last_valid_date。
   • 递归部分每次取客户的下一条记录，对比其日期与last_valid_date（最近有效记录的日期）：
     • 若当前记录日期晚于last_valid_date + 30天，标记为有效，并更新last_valid_date为当前记录日期。
     • 否则标记为重复，last_valid_date保持不变。
3. 最终按客户ID和注册日期排序输出结果。

方言适配提示 #

不同数据库的日期计算语法略有差异：

• PostgreSQL：替换DATEADD(day, 30, rv.last_valid_date)为rv.last_valid_date + INTERVAL '30 days'
• MySQL：替换为DATE_ADD(rv.last_valid_date, INTERVAL 30 DAY)

示例输出 #

内容的提问来源于stack exchange，提问作者Lakshmi Sruthi K