求BigQuery查询语句：按31天周期去重保留首条访问记录

需求：编写BigQuery SQL，按31天周期筛选客户访问记录——每个周期内仅保留客户的首次访问记录，剔除同周期内其他记录；当前周期结束后，再保留下一周期的首次访问，以此类推。

示例数据 #

WITH customer_visit AS (
    SELECT '554' AS CUS_ID, DATE('2023-12-31') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2023-12-31') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2024-01-16') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2024-01-30') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2024-02-05') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2024-02-10') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2024-02-15') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2024-02-29') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2024-03-06') AS visit_date
)

预期输出 #

CUS_ID   visit_date
-------------------
554      2023-12-31
555      2023-12-31
555      2024-02-05
555      2024-03-06

解决方案SQL #

WITH customer_visit AS (
    SELECT '554' AS CUS_ID, DATE('2023-12-31') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2023-12-31') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2024-01-16') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2024-01-30') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2024-02-05') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2024-02-10') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2024-02-15') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2024-02-29') AS visit_date UNION ALL
    SELECT '555' AS CUS_ID, DATE('2024-03-06') AS visit_date
),
ranked_visits AS (
    -- 按客户分组，访问日期排序，添加行号
    SELECT 
        CUS_ID,
        visit_date,
        ROW_NUMBER() OVER(PARTITION BY CUS_ID ORDER BY visit_date) AS rn
    FROM customer_visit
),
recursive_visits AS (
    -- 初始记录：每个客户的第一次访问，作为第一个周期的起始
    SELECT 
        CUS_ID,
        visit_date,
        visit_date + INTERVAL 31 DAY AS next_period_start
    FROM ranked_visits
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归查找下一个周期的首次访问
    SELECT 
        rv.CUS_ID,
        rv.visit_date,
        rv.visit_date + INTERVAL 31 DAY AS next_period_start
    FROM ranked_visits rv
    JOIN recursive_visits rv_prev ON rv.CUS_ID = rv_prev.CUS_ID
    WHERE rv.visit_date >= rv_prev.next_period_start
    -- 确保是当前周期的第一条记录
    AND NOT EXISTS (
        SELECT 1 
        FROM ranked_visits rv_sub
        WHERE rv_sub.CUS_ID = rv.CUS_ID
        AND rv_sub.visit_date >= rv_prev.next_period_start
        AND rv_sub.visit_date < rv.visit_date
    )
)
SELECT CUS_ID, visit_date
FROM recursive_visits
ORDER BY CUS_ID, visit_date;

逻辑说明 #

1. ranked_visits：对每个客户的访问记录按日期排序并添加行号，方便定位首次访问。
2. recursive_visits：
   • 初始步骤：取每个客户的第一条访问记录作为第一个31天周期的起始，同时计算该周期的结束日期（起始日期+31天）。
   • 递归步骤：关联上一轮的周期结束日期，找到客户中日期大于等于该结束日期的最早访问记录，作为下一个周期的首次访问，同时更新新的周期结束日期。
3. 最终输出所有筛选出的首次访问记录，按客户和日期排序。

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