如何用SQL标记不同日期重复购买同一商品的客户

原语句的问题 #

你提供的SQL语句未满足需求，核心问题包括：

• 仅按customer分区，未关联product，导致会把客户购买的所有商品混在一起统计
• COUNT_IF(product = product)逻辑恒成立，实际是统计当前行之前的所有行数，无法区分商品和日期
• 未排除同日购买的情况，同一天多次购买同一商品也会被误标记

正确实现方案 #

我们需要聚焦同一客户+同一商品的购买记录，同时排除同日购买的情况，以下是两种高效的实现方式：

方式一：用LAG函数直接判断上一次购买日期

SELECT
    customer,
    "order date",
    product,
    CASE
        -- 检查同一客户同一商品的上一次购买是否存在且日期不同
        WHEN LAG("order date") OVER (PARTITION BY customer, product ORDER BY "order date") IS NOT NULL 
             AND LAG("order date") OVER (PARTITION BY customer, product ORDER BY "order date") <> "order date" THEN 1
        ELSE 0
    END AS repeat_flag
FROM 
    your_table;

方式二：用COUNT_IF统计不同日期的前置购买记录

SELECT
    customer,
    "order date",
    product,
    -- 统计同一客户同一商品中，日期与当前行不同的前置购买记录数量，大于0则标记为1
    CASE 
        WHEN COUNT_IF("order date" <> current_date) OVER (
            PARTITION BY customer, product 
            ORDER BY "order date" 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) >= 1 THEN 1
        ELSE 0
    END AS repeat_flag
FROM 
    your_table;

关键逻辑说明 #

• PARTITION BY customer, product：限定分析范围为同一个客户对同一个商品的购买记录
• ORDER BY "order date"：按购买时间排序，确保窗口函数能正确获取历史购买记录
• 两种方式都通过日期对比，排除了同日多次购买同一商品的误标记情况，仅当客户在不同日期再次购买同一商品时，repeat_flag才会设为1

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