基于品牌判定客户首次/复购状态：多商品订单日期计算问题

问题说明 #

需求是按品牌判定客户的购买类型：首次下单，或者距上次同品牌下单超过12个月，算「首次购买」；否则为「复购」。
但现有实现中，单个Order ID对应多个Item ID时，用LAG()取历史订单日期会拿到同订单内其他商品的日期，导致同一订单里第2到第N个商品的日期差为0，判定逻辑完全错误（比如截图里的行2-4）。试过排名函数、CASE嵌套LAG()都没解决，核心需求是要拿到客户对应品牌的上一个不同订单的日期。

解决思路 #

问题出在直接在商品(Item)级数据上用LAG()——同订单的商品日期相同，LAG()会取到同订单的前一条商品记录的日期，而非上一个订单的日期。所以必须先把数据聚合到「客户+品牌+订单」的层级，确保每个订单只出现一次，再在这个层级上用LAG()获取上一个订单的日期，最后关联回原商品数据。

修正后的SQL代码 #

-- 第一步：聚合到订单级，每个订单只保留一条日期记录
WITH order_summary AS (
    SELECT
        customer_id,
        brand,
        order_id,
        order_date  -- 同一订单日期一致，直接取即可，也可用MAX(order_date)确保唯一
    FROM your_table
    GROUP BY customer_id, brand, order_id, order_date
),
-- 第二步：在订单级获取上一个同品牌订单的日期
order_with_prev_date AS (
    SELECT
        *,
        LAG(order_date) OVER (
            PARTITION BY customer_id, brand 
            ORDER BY order_date, order_id  -- 加order_id确保排序稳定，避免日期相同的订单乱序
        ) AS last_brand_order_date
    FROM order_summary
)
-- 第三步：关联回原表，计算并判定购买类型
SELECT
    t.*,
    CASE
        -- 没有历史订单，或者距上次同品牌下单超过12个月，判定为首次购买
        WHEN last_brand_order_date IS NULL 
             OR DATEDIFF(month, last_brand_order_date, t.order_date) > 12 THEN '首次购买'
        ELSE '复购'
    END AS purchase_category
FROM your_table t
JOIN order_with_prev_date o
    ON t.customer_id = o.customer_id
    AND t.brand = o.brand
    AND t.order_id = o.order_id
ORDER BY t.customer_id, t.order_date, t.item_id;

关键细节 #

1. 订单级聚合：通过GROUP BY customer_id, brand, order_id, order_date确保每个订单只生成一条记录，消除同订单多商品的干扰。
2. LAG()的分区与排序：按customer_id+brand分区，保证只取同一客户同一品牌的历史订单；按order_date+order_id排序，避免同一日期多个订单时出现排序混乱。
3. 关联回原表：把订单级的历史日期关联到每个商品记录，确保每个商品都能拿到正确的上一个订单日期，从而准确计算日期差。

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