按customer_id分组判断是否存在失败后成功交易的查询方法

场景说明 #

我们需要基于交易表，按客户分组判断是否存在时间更早的失败交易+时间更晚的成功交易的情况，输出每个客户的标识结果。

假定表结构 #

我们将原始交易表命名为transactions，包含以下字段：

• customer_id：客户唯一ID
• transaction_status：交易状态，枚举值为Failed（失败）/Complete（成功）
• txn_timestamp：交易发生的时间戳，用于排序时序

实现方案 #

方案1：聚合统计法（性能最优，适合大表） #

逻辑核心：只要客户的首次失败交易时间早于末次成功交易时间，就满足条件，否则不满足。

SELECT
  customer_id,
  CASE
    WHEN MIN(CASE WHEN transaction_status = 'Failed' THEN txn_timestamp END) 
        < MAX(CASE WHEN transaction_status = 'Complete' THEN txn_timestamp END)
    THEN TRUE
    ELSE FALSE
  END AS returning_success
FROM transactions
GROUP BY customer_id
ORDER BY customer_id;

方案2：自连接法（逻辑更直观，适合小表） #

逻辑核心：关联同一个客户的失败交易和成功交易，只要存在失败交易早于成功交易的匹配记录，就返回True。

SELECT
  t1.customer_id,
  CASE WHEN COUNT(t2.customer_id) > 0 THEN TRUE ELSE FALSE END AS returning_success
FROM transactions t1
LEFT JOIN transactions t2
  ON t1.customer_id = t2.customer_id
  AND t1.transaction_status = 'Failed'
  AND t2.transaction_status = 'Complete'
  AND t1.txn_timestamp < t2.txn_timestamp
GROUP BY t1.customer_id
ORDER BY t1.customer_id;

结果验证 #

两种方案执行后，输出结果和需求预期完全一致：

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