如何用PostgreSQL识别连续三月有购买行为的客户？是否需预处理数据？

要识别连续三个月有购买行为的客户，必须先对当前的宽表结构做预处理，直接在现有宽表上实现不仅代码冗余，而且扩展性极差。

一、数据预处理：宽表转窄表 #

当前表的结构是「每个年月作为单独列」，这种宽表无法高效判断连续时间的购买状态。需要先转成窄表（长表），结构为customer_id、month（YYYY-MM格式）、purchase_flag（1/0）。

用通用SQL实现的话，可以用UNION ALL拼接每个月份的数据：

SELECT customer_id, '2020-03' AS month, 2020_03 AS purchase_flag FROM your_table
UNION ALL
SELECT customer_id, '2020-04' AS month, 2020_04 AS purchase_flag FROM your_table
UNION ALL
SELECT customer_id, '2020-05' AS month, 2020_05 AS purchase_flag FROM your_table
UNION ALL
SELECT customer_id, '2020-06' AS month, 2020_06 AS purchase_flag FROM your_table
UNION ALL
SELECT customer_id, '2020-07' AS month, 2020_07 AS purchase_flag FROM your_table
UNION ALL
SELECT customer_id, '2020-08' AS month, 2020_08 AS purchase_flag FROM your_table

如果是支持UNPIVOT的数据库（比如SQL Server、Oracle），可以用更简洁的UNPIVOT语法替代UNION ALL。

二、识别连续三月购买的客户 #

转成窄表后，有两种常用方法实现需求：

方法1：用LEAD函数直接判断后续两个月的购买状态 #

通过LEAD窗口函数获取当前月份的后1、后2个月的购买标记，只要这三个标记都是1，就说明该客户存在连续三月购买：

WITH unpivoted_data AS (
    -- 这里放入上面的UNION ALL预处理语句
),
check_continuous AS (
    SELECT 
        customer_id,
        purchase_flag,
        LEAD(purchase_flag, 1) OVER (PARTITION BY customer_id ORDER BY month) AS next_1_month,
        LEAD(purchase_flag, 2) OVER (PARTITION BY customer_id ORDER BY month) AS next_2_month
    FROM unpivoted_data
)
SELECT DISTINCT customer_id
FROM check_continuous
WHERE purchase_flag = 1 AND next_1_month = 1 AND next_2_month = 1;

方法2：分组统计连续购买的月份数 #

通过计算连续购买的分组，统计每个分组的月份数量，筛选出分组内月份数≥3的客户：

WITH unpivoted_data AS (
    -- 这里放入上面的UNION ALL预处理语句
),
grouped_data AS (
    SELECT 
        customer_id,
        month,
        -- 生成连续购买的分组ID
        DATE_TRUNC('month', TO_DATE(month, 'YYYY-MM')) - 
        INTERVAL '1 month' * ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY month) AS group_id
    FROM unpivoted_data
    WHERE purchase_flag = 1
)
SELECT DISTINCT customer_id
FROM grouped_data
GROUP BY customer_id, group_id
HAVING COUNT(*) >= 3;

为什么不能直接在宽表上处理？ #

如果强行在宽表上实现，需要写大量的AND+OR组合条件，比如：

SELECT customer_id
FROM your_table
WHERE 
    (2020_03 = 1 AND 2020_04 = 1 AND 2020_05 = 1)
    OR (2020_04 = 1 AND 2020_05 = 1 AND 2020_06 = 1)
    OR (2020_05 = 1 AND 2020_06 = 1 AND 2020_07 = 1)
    OR (2020_06 = 1 AND 2020_07 = 1 AND 2020_08 = 1);

这种方式不仅代码冗长，而且新增月份时必须手动修改SQL，完全不具备扩展性。相比之下，转成窄表后用窗口函数的方案，无论新增多少月份都不需要修改核心逻辑，维护成本低得多。

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