如何关联订单表与事件表统计商户月度订单及过期订单指标

问题原因排查 #

• 字段引用错误：原SQL使用o.order_amount取值，但Orders表的金额字段实际为amount，会导致金额统计错误
• 过滤逻辑错误：将event = 'EXPIRY'加入WHERE子句时，会过滤掉左连接后未匹配到过期事件的订单行，相当于把左连接转为内连接，没有过期订单的分组会被直接剔除，甚至出现无返回结果的情况
• 统计逻辑无区分：原统计逻辑未判断订单是否对应过期事件，导致全量订单指标和过期订单指标数值完全一致

正确实现SQL #

SELECT 
  o.merchant_id,
  EXTRACT(MONTH FROM o.order_date) AS order_month,
  COUNT(DISTINCT o.order_id) AS order_count,
  SUM(o.amount) AS order_sum,
  COUNT(DISTINCT CASE WHEN e.event = 'EXPIRY' THEN o.order_id END) AS expiry_count,
  SUM(CASE WHEN e.event = 'EXPIRY' THEN o.amount ELSE 0 END) AS expiry_sum
FROM Orders o
LEFT JOIN Events e 
  ON o.merchant_id = e.merchant_id 
  AND o.order_id = e.order_id
WHERE o.merchant_id = '111111'
  AND o.order_date >= '2021-07-01'
GROUP BY o.merchant_id, order_month
ORDER BY o.merchant_id, order_month

如果需要按Events表存储的过期金额统计，将SUM(CASE WHEN e.event = 'EXPIRY' THEN o.amount ELSE 0 END)中的o.amount替换为e.amount即可。

实现说明 #

1. 关联条件增加merchant_id匹配，避免跨商户出现相同订单ID时匹配错误
2. 采用条件聚合统计过期指标：将过期事件的判断放到聚合函数内部，不需要在WHERE子句过滤事件类型，不会影响全量订单的统计逻辑
3. 统计订单数时增加DISTINCT，避免单个订单对应多条事件记录时出现计数重复

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