时间戳

首页 > 解决方案 > 对于每个保修索赔，如何进行有条件的最大退货以从客户那里退回最后一次购买？

sql - 对于每个保修索赔，如何进行有条件的最大退货以从客户那里退回最后一次购买？

问题描述

嗨，我有一个看起来像这样的数据表

订单编号 日期 产品名称 客户ID
476070 2021年2月24日 保修索赔 ABC123
476069 2021年2月22日 产品C ABC123
476068 2021年2月10日 保修索赔 ABC123
476067 2021年2月1日 保修索赔 ABC123
476064 2021年1月23日 产品B ABC123
476051 2021年1月10日 保修索赔 ABC123
475023 2021 年 1 月 1 日 产品A ABC123

我想要实现的是对于每个保修索赔，我想获得该客户的最后一次产品购买。

所以意思是我想要实现的输出是这样的

订单编号 日期 产品名称 客户ID 最后产品购买
476070 2021年2月24日 保修索赔 ABC123 产品C
476069 2021年2月22日 产品C ABC123 空值
476068 2021年2月10日 保修索赔 ABC123 产品B
476067 2021年2月1日 保修索赔 ABC123 产品B
476064 2021年1月23日 产品B ABC123 空值
476051 2021年1月10日 保修索赔 ABC123 产品A
475023 2021 年 1 月 1 日 产品A ABC123 空值

我已经尝试过 LAG 和 MAXIF，但它会返回该特定客户的先前订单或最新订单。我需要该客户的最后一次产品购买，并且仅适用于保修索赔订单。

我正在为 CTE 解决方案获取此输出

在此处输入图像描述

这是我运行的查询：

WITH cte AS (
    SELECT *, MAX(CASE WHEN productName <> 'Warranty Service' OR productName <> 'Warranty Collect' OR productName <> 'Warranty Inspection' OR productName <> 'Delivery' THEN id END) OVER
                  (PARTITION BY vpn ORDER BY ordercompleted_at) MaxOrderID
    FROM baterikudatawarehouse.bateriku_production.BaterikuOrderTrafficJune2020
    where orderStatus = 'completed' and vpn = 'QMY70' order by ordercompleted_at DESC
)

SELECT t1.id ,t1.ordercompleted_at, t1.productName, t1.vpn,
       CASE WHEN t1.productName = 'Warranty Service' OR t1.productName = 'Warranty Collect' OR t1.productName = 'Delivery' OR t1.productName = 'Warranty Inspection'
            THEN t2.productName END AS LASTPRODUCTPURCHASE
FROM cte t1

INNER JOIN baterikudatawarehouse.bateriku_production.BaterikuOrderTrafficJune2020 t2
    ON t2.vpn = t1.vpn AND
       t2.id = t1.MaxOrderId ORDER BY t1.ordercompleted_at DESC;

标签： sqlpostgresqlgoogle-bigquery

解决方案

一种方法，首先在每个时间点找到每个客户的最新订单：

WITH cte AS (
    SELECT *, MAX(CASE WHEN ProductName <> 'WARRANTY CLAIM' THEN OrderID END) OVER
                  (PARTITION BY CustomerID ORDER BY Date) MaxOrderID
    FROM yourTable
)

SELECT t1.OrderID, t1.Date, t1.ProductName, t1.CustomerID,
       CASE WHEN t1.ProductName = 'Warranty Claim'
            THEN t2.ProductName END AS LASTPRODUCTPURCHASE
FROM cte t1
INNER JOIN yourTable t2
    ON t2.CustomerID = t1.CustomerID AND
       t2.OrderID = t1.MaxOrderId;

请注意，返回原始表的连接会从最近的非保修事务中提取最新的产品名称。

推荐阅读