如何通过条件最大值为每条保修索赔匹配客户的最近产品购买

业务规则与预期效果 #

• 基础订单表核心字段包含：OrderID（订单ID）、Date（订单日期）、Product Name（产品名称）、CustomerID（客户ID）
• 匹配规则：
  • 保修索赔类订单：匹配该客户在当前订单发生时间之前最近一次实体产品购买的产品名称，写入新增字段LASTPRODUCTPURCHASE
  • 实体产品购买订单：LASTPRODUCTPURCHASE字段强制返回NULL
• 客户ABC123的测试用例验证标准：
  客户按时间正序排列的全量订单依次为：2021-01-01 PRODUCT A购买、2021-01-10 保修索赔、2021-01-23 PRODUCT B购买、2021-02-01 保修索赔、2021-02-10 保修索赔、2021-02-22 PRODUCT C购买、2021-02-24 保修索赔
  对应输出校验点：
  • 2021-02-24保修单匹配最近购买为PRODUCT C
  • 2021-02-01、2021-02-10保修单匹配最近购买为PRODUCT B
  • 2021-01-10保修单匹配最近购买为PRODUCT A
  • 所有实体购买单的匹配值为NULL

原有SQL逻辑的问题定位 #

• 实体记录判断逻辑错误：原CTE的CASE条件用OR连接多个保修类产品名，判断逻辑等价于“只要不是任意一类保修服务就属于实体购买”，完全不符合排除所有服务类产品的预期，过滤范围严重失真
• 窗口函数计算顺序混乱：原CTE内部嵌套了倒序排序语句，打乱了窗口函数按时间升序计算的逻辑，最终取到的是客户全局偏移的实体订单ID，而非当前时间点之前的最近一条
• 关联逻辑缺陷：使用内连接关联原表取产品名时，未过滤“关联实体订单时间小于当前订单时间”的边界条件，当某条保修单之前无实体购买记录时会直接丢失该行数据，无法保留NULL值
• 结果填充逻辑缺失：未对实体购买订单强制置NULL的规则做约束，会导致实体订单行也填充上匹配值，不符合业务要求

修正后的可复用SQL实现 #

WITH base_order AS (
    SELECT 
        OrderID,
        Date,
        ProductName,
        CustomerID,
        -- 标记实体购买记录，非实体行置NULL
        CASE 
            WHEN ProductName NOT IN ('Warranty Service','Warranty Collect','Warranty Inspection','Delivery') 
            THEN ProductName 
            ELSE NULL 
        END AS valid_purchase_name
    FROM baterikudatawarehouse.bateriku_production.BaterikuOrderTrafficJune2020
    WHERE orderStatus = 'completed'
),
order_calc AS (
    SELECT 
        *,
        -- 按客户分区、订单时间升序，从分区起始行到当前行回溯，忽略NULL值取最近的实体购买名
        LAST_VALUE(valid_purchase_name) IGNORE NULLS OVER (
            PARTITION BY CustomerID 
            ORDER BY Date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS last_calc_purchase
    FROM base_order
)
SELECT 
    OrderID,
    Date,
    ProductName,
    CustomerID,
    -- 仅对保修类订单填充匹配值，实体订单返回NULL
    CASE 
        WHEN ProductName IN ('Warranty Service','Warranty Collect','Warranty Inspection','Delivery') 
        THEN last_calc_purchase 
        ELSE NULL 
    END AS LASTPRODUCTPURCHASE
FROM order_calc

实现逻辑说明 #

• 所有判断逻辑基于明确的IN/NOT IN集合匹配，避免AND/OR逻辑混淆导致的过滤错误
• 窗口函数计算范围固定为“从客户第一条订单到当前行”，自动跳过保修单的NULL值，精准锁定当前时间点之前的最近实体购买记录，不需要额外关联原表，不会出现数据丢失
• 最终字段填充严格匹配业务规则，实体订单强制返回NULL，无需额外做结果过滤

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