如何用SQL/PySpark检测客户5天窗口期内3次非连续购买？

核心逻辑 #

对于每个客户，先按购买日期对其所有购买记录排序，再检查是否存在某一笔购买记录，与它之后的第2笔购买记录的日期差不超过5天。如果存在，就说明这3笔购买落在一个5天的窗口期内（从最早的那笔日期开始，到最早日期+5天的区间内，包含这3笔），符合检测要求。

SQL实现（以MySQL为例） #

-- 第一步：为每个客户的购买记录按日期排序，生成行号
WITH ranked_purchases AS (
    SELECT
        cust_id,
        purchase_date,
        ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY purchase_date) AS rn
    FROM customer_purchases
    -- 若需确保“非连续”指不同日期的购买（排除同一天多次购买），可添加以下分组条件
    -- GROUP BY cust_id, purchase_date
)
-- 第二步：判断每个客户是否存在符合条件的3次购买
SELECT DISTINCT
    cust_id,
    CASE WHEN EXISTS (
        SELECT 1
        FROM ranked_purchases rp1
        JOIN ranked_purchases rp3 ON rp1.cust_id = rp3.cust_id AND rp3.rn = rp1.rn + 2
        WHERE rp1.cust_id = ranked_purchases.cust_id
          AND DATEDIFF(rp3.purchase_date, rp1.purchase_date) <= 5
    ) THEN '符合条件' ELSE '不符合条件' END AS status
FROM ranked_purchases;

逻辑解释 #

1. 排序生成行号：通过窗口函数ROW_NUMBER()按客户分组，对每条购买记录按日期从小到大排序并分配序号。如果需要排除同一天的重复购买（即严格保证“非连续”是不同日期的购买行为），可在这一步添加GROUP BY cust_id, purchase_date，合并同一天的多条购买记录。
2. 检测5天窗口的3次购买：将每条记录和它之后的第2条记录（间隔1条，共3条）关联，计算两者的日期差。若日期差≤5天，说明这3条记录都落在以第一条记录日期为起点的5天窗口内（中间的第2条记录日期必然在首尾两者之间）。
3. 输出客户检测状态：通过EXISTS子查询判断每个客户是否存在符合条件的记录，最终输出每个客户的检测结果。

示例数据验证 #

• 客户11的购买记录排序后，第4条是2023-11-21，第6条是2023-11-24，两者日期差为3天≤5，因此标记为“符合条件”。
• 客户12的任意3条购买记录中，最早与最晚日期的差值均超过5天（比如2023-11-21、2023-11-25、2023-12-01，日期差为10天），因此标记为“不符合条件”。

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