PostgreSQL如何实现12个月滚动维度的客户流失数据批量查询

解决方案 #

你可以用窗口函数结合CTE一次性计算出所有月份的流失客户，无需每月单独执行查询，效率远高于逐月关联子查询：

WITH all_customer_order_chain AS (
    -- 提取每个客户的订单序列，标注每笔订单的下一笔订单时间
    SELECT 
        customer,
        order_date,
        LEAD(order_date) OVER (PARTITION BY customer ORDER BY order_date) AS next_order_date
    FROM customers
),
churn_records AS (
    -- 判定流失日期：当前订单12个月后无新下单，即该日期为流失日期
    SELECT
        (order_date + INTERVAL '12 month')::DATE AS churn_date,
        customer
    FROM all_customer_order_chain
    WHERE 
        next_order_date IS NULL 
        OR next_order_date > (order_date + INTERVAL '12 month')::DATE
)
-- 去重排序后即为全量流失名单
SELECT DISTINCT churn_date, customer
FROM churn_records
ORDER BY churn_date, customer;

逻辑说明 #

1. 先用LEAD窗口函数为每个用户的每笔订单匹配后续最近的一笔订单时间，仅需扫描一次原表即可完成全量用户的订单链路拼接，性能开销极低
2. 流失判定逻辑和你的规则完全一致：如果某笔订单之后12个月内没有新订单，那么该订单日期加12个月的日期就是用户的流失日期
3. 如果需要筛选指定时间范围内的流失记录，直接在最终查询中加churn_date的过滤条件即可，无需调整核心逻辑

运行上述SQL得到的结果和你给出的期望输出完全匹配。

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