如何筛选拥有两次金额<15且间隔≥90天的连续消费的客户

我需要筛选满足以下条件的客户：存在两次连续的消费记录，第一次消费金额小于15，第二次消费金额也小于15，且第二次消费时间比第一次晚90天或更久。当前编写的SQL脚本未能精准定位符合条件的连续消费日期，仅能得到部分结果。

理想结果仅应包含cust1：尽管其2010-01-01的消费不符合规则，但2011-12-24的消费与后续2014-10-06的消费满足金额<15且间隔≥90天的要求；cust2从未有过符合条件的连续消费，不应被纳入；cust3的连续低额消费间隔均不足90天，也不符合要求。

#MyList表 #

当前使用的SQL脚本 #

WITH firstevent AS(
   SELECT * FROM(
   SELECT *, 
   row_number() OVER(PARTITION BY customer ORDER BY purchasedate ASC) rn
   FROM #MyList 
   WHERE amount < 15) A
   WHERE rn = 1
   ), secondevent AS (
   SELECT * FROM(
   SELECT *, 
   ROW_NUMBER() OVER(PARTITION BY customer ORDER BY purchasedate DESC) rn
   FROM #MyList 
   WHERE amount < 15) B
   WHERE rn = 1
   )
   SELECT f.customer, f.amount AS FirstAmount, s.amount AS LastAmount, f.purchasedate AS Date1, 
   s.purchasedate AS Date2 
   FROM firstevent f
   INNER JOIN secondevent s ON (f.customer = s.customer)
   WHERE DATEDIFF(D, f.purchasedate, s.purchasedate) >= 90

问题分析 #

当前脚本的核心缺陷是：仅比较每个客户最早和最晚的低额消费记录，完全忽略了“连续消费记录”的要求。这会导致两个错误：

• 错误纳入不符合条件的客户：比如cust3最早和最晚低额消费间隔远超90天，但它的连续低额消费间隔都不足90天，本不应被选中，却会被脚本纳入。
• 无法准确匹配真正符合条件的连续消费对：比如cust1的2011-12-24和2014-10-06这对符合条件的连续记录，脚本根本不会检查到，只会拿最早的2008-11-01和最晚的2014-10-06比较，虽然结果也会包含cust1，但逻辑完全错误。

正确的SQL脚本 #

方案1：仅获取符合条件的客户列表 #

WITH filtered_purchases AS (
    -- 先筛选所有金额小于15的消费记录，排除高金额干扰
    SELECT customer, purchasedate, amount
    FROM #MyList
    WHERE amount < 15
),
next_purchase AS (
    -- 用LEAD窗口函数获取每条低额消费的下一条连续低额消费记录
    SELECT 
        customer,
        purchasedate AS current_date,
        LEAD(purchasedate) OVER (PARTITION BY customer ORDER BY purchasedate) AS next_date
    FROM filtered_purchases
)
-- 筛选出存在间隔≥90天的连续低额消费的客户，去重避免重复
SELECT DISTINCT customer
FROM next_purchase
WHERE 
    next_date IS NOT NULL
    AND DATEDIFF(day, current_date, next_date) >= 90;

方案2：获取具体的符合条件的连续消费对 #

如果需要查看具体的消费记录对，可以使用以下脚本：

WITH filtered_purchases AS (
    SELECT customer, purchasedate, amount
    FROM #MyList
    WHERE amount < 15
),
next_purchase AS (
    SELECT 
        customer,
        purchasedate AS first_purchase_date,
        amount AS first_amount,
        LEAD(purchasedate) OVER (PARTITION BY customer ORDER BY purchasedate) AS second_purchase_date,
        LEAD(amount) OVER (PARTITION BY customer ORDER BY purchasedate) AS second_amount
    FROM filtered_purchases
)
SELECT 
    customer,
    first_purchase_date,
    first_amount,
    second_purchase_date,
    second_amount,
    DATEDIFF(day, first_purchase_date, second_purchase_date) AS days_between
FROM next_purchase
WHERE 
    second_purchase_date IS NOT NULL
    AND DATEDIFF(day, first_purchase_date, second_purchase_date) >= 90;

脚本说明 #

1. filtered_purchases：先过滤出所有金额小于15的消费记录，排除高金额记录的干扰，只关注符合金额要求的记录。
2. next_purchase：使用LEAD窗口函数，按客户分组、消费日期排序，为每条低额消费记录关联它的下一条连续低额消费记录的日期和金额。
3. 最后筛选出间隔≥90天的记录，DISTINCT确保每个客户只出现一次（如果只需要客户列表），或者直接输出符合条件的消费对。

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