Lag查询拉取数据错误：过往交易月份计算异常问题排查

问题背景 #

原始表TPS_TABLE_A包含字段：

• BU_Code：门店编码
• contact_key：客户ID
• Bu_key：门店编号
• TXN_MTH：交易月份（格式为YYYYMM，如202101）
• 商品标识列：FRAGRANCE_FLAG、COSMETICS_FLAG、PERSONALCARE_FLAG

需求：生成新表TPS_TABLE_B，统计客户上一次交易月份PRE_TXN_MTH，并按规则划分客户类型：

• 新客：无过往交易记录
• 回头客：近12个月内有交易
• 重激活客：近12个月无交易

执行现有SQL后出现异常：部分客户（如contact_key=1443）的未来交易被误判为过往交易，contact_key=1196则正常。

问题SQL代码 #

CREATE TABLE TPS_TABLE_B AS 
    (
    SELECT 
      B.* 
    , LAG(TXN_MTH) OVER (ORDER BY CONTACT_KEY, TXN_MTH) PRE_TXN_MTH
    --, TXN_MTH - 100
    , CASE 
        WHEN LAG(TXN_MTH) OVER (ORDER BY CONTACT_KEY, TXN_MTH) IS NULL THEN 'NEW'
        WHEN TXN_MTH - 100 <  (LAG(TXN_MTH) OVER (ORDER BY CONTACT_KEY, TXN_MTH)) THEN 'RETURNING'
        WHEN TXN_MTH - 100 >= (LAG(TXN_MTH) OVER (ORDER BY CONTACT_KEY, TXN_MTH)) THEN 'REATIVATED'--REACTIVATED IS NO TRANSACTION IN PAST 12 MONTHS
        ELSE 'OTHER'
      END AS CUST_TYPE     
    FROM 
        (
        SELECT 
            CONTACT_KEY
        ,   BU_CODE
        ,   BU_KEY
        ,   TXN_MTH
        ,   FRAGRANCE_FLAG   
        ,   COSMETICS_FLAG  
        ,   PERSONALCARE_FLAG     
    
        FROM TPS_TABLE_A    
        ) B
    )
;

错误原因分析 #

1. 缺少客户分组：LAG()函数仅用ORDER BY CONTACT_KEY, TXN_MTH排序，未添加PARTITION BY CONTACT_KEY，导致跨客户取数——比如客户A的最后一条记录后紧跟客户B的第一条记录，此时客户B的PRE_TXN_MTH会错误取到客户A的交易月份。
2. CASE条件逻辑颠倒：原代码中判断回头客/重激活客的条件写反，导致间隔周期判断错误。
3. 拼写错误：REATIVATED应为REACTIVATED。

修正后的SQL代码 #

CREATE TABLE TPS_TABLE_B AS 
SELECT 
  B.* 
, LAG(TXN_MTH) OVER (PARTITION BY CONTACT_KEY ORDER BY TXN_MTH) PRE_TXN_MTH
, CASE 
    WHEN LAG(TXN_MTH) OVER (PARTITION BY CONTACT_KEY ORDER BY TXN_MTH) IS NULL THEN 'NEW'
    -- 近12个月内有交易，判定为回头客
    WHEN (TXN_MTH - 100) <= LAG(TXN_MTH) OVER (PARTITION BY CONTACT_KEY ORDER BY TXN_MTH) THEN 'RETURNING'
    -- 间隔超过12个月无交易，判定为重激活客
    WHEN (TXN_MTH - 100) > LAG(TXN_MTH) OVER (PARTITION BY CONTACT_KEY ORDER BY TXN_MTH) THEN 'REACTIVATED'
    ELSE 'OTHER'
  END AS CUST_TYPE     
FROM (
    SELECT 
        CONTACT_KEY
    ,   BU_CODE
    ,   BU_KEY
    ,   TXN_MTH
    ,   FRAGRANCE_FLAG   
    ,   COSMETICS_FLAG  
    ,   PERSONALCARE_FLAG     
    FROM TPS_TABLE_A    
) B;

修正说明 #

• 添加PARTITION BY CONTACT_KEY：确保LAG()仅在同一客户的交易记录中取上一条，避免跨客户数据干扰。
• 修正CASE条件：调整逻辑，正确区分近12个月内/外的交易间隔。
• 修正拼写错误：统一客户类型标识的拼写。

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