如何筛选每位客户间隔至少X天的有效采购记录？

表结构 #

Purchases
---------
Item_ID
Purchase_Date
Customer_ID

需求 #

获取每位客户的采购记录，规则如下：

• 首笔采购始终有效
• 后续记录必须与上一条已选中的有效采购间隔至少X天（示例中X=10）

示例数据 #

Item_ID    PurchaseDate    Customer_ID    
123        07/29/23        1000
123        08/04/23        1000
123        08/16/23        1000
563        07/03/23        7785
563        07/05/23        7785
788        08/17/23        2489

期望结果 #

Item_ID    PurchaseDate    Customer_ID    
123        07/29/23        1000
123        08/16/23        1000
563        07/03/23        7785
788        08/17/23        2489

规则说明 #

• 客户1000：首笔有效，第二笔与首笔间隔仅6天被剔除，第三笔与首笔间隔18天符合要求，保留
• 客户7785：第二笔与首笔间隔2天，仅保留首笔
• 客户2489：仅1笔采购，直接保留

原尝试SQL的问题 #

你给出的初步SQL存在两处问题：

1. 语法错误：LAG函数缺少目标列，正确写法应为LAG(PurchaseDate) OVER(PARTITION BY Customer_ID ORDER BY PurchaseDate) AS prev_purchase
2. 逻辑错误：普通LAG仅能获取物理上的上一条记录，无法追踪上一条已选中的有效记录。比如客户1000的第三笔，原逻辑会和第二笔（已剔除）比，而不是和第一笔（有效记录）比，导致结果不符合预期。

正确解法：递归CTE #

因为需要持续追踪每个客户的上一条有效采购日期，递归CTE是最直接的实现方式，以下是SQL Server环境下的示例代码：

-- 设置间隔天数X
DECLARE @X INT = 10;

WITH CustomerPurchases AS (
    -- 先按客户分组，给每条采购记录按日期排序编号
    SELECT 
        Item_ID,
        PurchaseDate,
        Customer_ID,
        ROW_NUMBER() OVER(PARTITION BY Customer_ID ORDER BY PurchaseDate) AS rn
    FROM Purchases
),
RecursiveFilter AS (
    -- 递归起点：每个客户的首笔采购（必选）
    SELECT 
        Item_ID,
        PurchaseDate,
        Customer_ID,
        PurchaseDate AS LastValidDate  -- 记录当前有效日期，供后续对比
    FROM CustomerPurchases
    WHERE rn = 1

    UNION ALL

    -- 递归逻辑：找到当前客户中，日期晚于上一条有效日期+X天的记录
    SELECT 
        cp.Item_ID,
        cp.PurchaseDate,
        cp.Customer_ID,
        cp.PurchaseDate AS LastValidDate
    FROM CustomerPurchases cp
    JOIN RecursiveFilter rf 
        ON cp.Customer_ID = rf.Customer_ID
    WHERE cp.rn > (SELECT MAX(rn) FROM RecursiveFilter WHERE Customer_ID = cp.Customer_ID)
      AND DATEDIFF(DAY, rf.LastValidDate, cp.PurchaseDate) >= @X
)
-- 输出最终结果
SELECT Item_ID, PurchaseDate, Customer_ID
FROM RecursiveFilter
ORDER BY Customer_ID, PurchaseDate;

如果是其他数据库（如PostgreSQL），仅需调整日期差的计算方式（比如用cp.PurchaseDate - rf.LastValidDate >= INTERVAL '10 days'），核心逻辑一致。

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