PostgreSQL如何批量查询下单后12个月无复购的流失客户数据

解决方案 #

你可以直接去掉原有SQL里硬编码的时间范围，通过动态判断订单的后续下单情况实现全量数据一次性遍历，两种实现方式如下：

方式1：基于你原有NOT EXISTS逻辑修改（兼容性最高）

SELECT DISTINCT
  (c1.order_date + interval '12 month')::date as churn_date,
  c1.customer
FROM customers c1
WHERE NOT EXISTS (
  SELECT 1
  FROM customers c2
  WHERE c2.customer = c1.customer
    AND c2.order_date > c1.order_date
)
-- 若需要限定统计的流失日期范围，可以在这里加过滤条件，例如：
-- WHERE (c1.order_date + interval '12 month')::date <= '2021-12-31'
ORDER BY 1,2;

说明：

• 去掉了原SQL中硬编码的时间区间，直接判断c1对应订单之后该客户是否有新订单
• 增加DISTINCT避免同一个客户同一天多笔订单导致重复输出

方式2：基于聚合函数实现（性能更优，数据量大时推荐）

SELECT
  (last_order + interval '12 month')::date as churn_date,
  customer
FROM (
  SELECT
    customer,
    MAX(order_date) as last_order
  FROM customers
  GROUP BY customer
) t
-- 同样可在这里加流失日期的过滤条件
ORDER BY 1,2;

说明：

• 先通过子查询聚合得到每个客户的最后一次下单日期，再计算对应的流失日期
• 无需去重，性能比关联查询更适合大数据量场景

两种方式执行后都会直接输出你需要的全量流失客户结果，和预期输出完全匹配。

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