如何在SQL Server中按30天交易规则设置客户重复购买标记？

原始数据表 #

以下是原始交易数据的结构及内容：

标记规则 #

为每笔交易添加Flag字段，规则如下：

• 若同一Customer_ID的某笔交易，在其Transaction_date的30天范围内（包含前后30天）存在相同Category、Subcategory1、Subcategory2的其他交易，则Flag设为N；
• 其余情况Flag设为Y。

预期结果 #

执行查询后应得到如下结果：

SQL 查询语句 #

针对SQL Server，可使用EXISTS子查询结合日期函数实现需求。注意原始数据中Transaction_date为字符串格式（dd/mm/yyyy），需先转换为日期类型再计算天数差：

SELECT 
    t1.Customer_ID,
    t1.Category,
    t1.Subcategory1,
    t1.Subcategory2,
    t1.Transaction_date,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM your_table t2
            WHERE t2.Customer_ID = t1.Customer_ID
              AND t2.Category = t1.Category
              AND t2.Subcategory1 = t1.Subcategory1
              AND t2.Subcategory2 = t1.Subcategory2
              AND t2.Transaction_date <> t1.Transaction_date
              AND DATEDIFF(day, CONVERT(date, t1.Transaction_date, 103), CONVERT(date, t2.Transaction_date, 103)) BETWEEN -30 AND 30
        ) THEN 'N'
        ELSE 'Y'
    END AS Flag
FROM your_table t1
ORDER BY t1.Customer_ID, CONVERT(date, t1.Transaction_date, 103) DESC;

代码说明 #

1. CONVERT(date, Transaction_date, 103)：将字符串格式的日期（dd/mm/yyyy）转换为SQL Server的date类型，确保日期计算准确；
2. EXISTS子查询：检查当前交易是否存在符合条件的其他交易——同一客户、相同分类层级，交易日期在当前日期±30天内且不是同一笔交易；
3. CASE表达式：根据子查询结果设置Flag值，存在符合条件的交易则设为N，否则为Y；
4. ORDER BY：按客户ID和交易日期降序排列，与预期结果的顺序一致。

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