如何补全客户交易前后月度数据并生成is_active状态列（SQL）

基础表结构与数据 #

create table example
(cust_id VARCHAR, product VARCHAR, price float, datetime varchar);

insert into example (cust_id, product, price, datetime)
VALUES
('1', 'scooter', 2000, '2022-01-10'),
('1', 'skateboard', 1500, '2022-01-20'),
('1', 'beefmeat', 300, '2022-06-08'),
('2', 'wallet', 200, '2022-02-25'),
('2', 'hairdryer', 250, '2022-04-28'),
('3', 'skateboard', 1600, '2022-03-29')

需求说明 #

• 为每个客户补全交易前后的月度行，结果包含cust_id、total_price、date、is_active字段
• 标记规则：
  • 客户首次交易的月份标记为active，该月份之前的所有月份标记为inactive
  • 若连续超过2个月无交易，后续月份标记为inactive，直到有新交易时恢复active

完整解决方案代码 #

以下基于PostgreSQL语法实现（兼容你初步代码中使用的date_part、age等函数）：

-- 生成覆盖所有客户交易前后的月份范围（可自行调整前后扩展的月数）
WITH date_range AS (
    SELECT generate_series(
        (SELECT date_trunc('month', min(to_date(datetime, 'YYYY-MM-DD'))) - interval '12 month' FROM example),
        (SELECT date_trunc('month', max(to_date(datetime, 'YYYY-MM-DD'))) + interval '12 month' FROM example),
        interval '1 month'
    ) AS month_date
),
-- 获取所有唯一客户
customers AS (
    SELECT DISTINCT cust_id FROM example
),
-- 生成每个客户的全量月度行
customer_months AS (
    SELECT 
        c.cust_id,
        to_char(d.month_date, 'YYYY-MM') AS date
    FROM customers c
    CROSS JOIN date_range d
),
-- 按月聚合客户交易总额
customer_monthly_sales AS (
    SELECT 
        cust_id,
        to_char(to_date(datetime, 'YYYY-MM-DD'), 'YYYY-MM') AS date,
        sum(price) AS total_price
    FROM example
    GROUP BY cust_id, date
),
-- 关联数据并计算关键日期指标
customer_monthly_data AS (
    SELECT 
        cm.cust_id,
        cm.date,
        COALESCE(cms.total_price, 0) AS total_price,
        -- 客户首次交易的月份
        MIN(CASE WHEN cms.total_price > 0 THEN cm.date END) OVER (PARTITION BY cm.cust_id) AS first_trans_month,
        -- 上一次有交易的月份
        LAG(CASE WHEN cms.total_price > 0 THEN cm.date END) OVER (PARTITION BY cm.cust_id ORDER BY cm.date) AS last_active_month,
        -- 下一次有交易的月份
        LEAD(CASE WHEN cms.total_price > 0 THEN cm.date END) OVER (PARTITION BY cm.cust_id ORDER BY cm.date) AS next_active_month
    FROM customer_months cm
    LEFT JOIN customer_monthly_sales cms ON cm.cust_id = cms.cust_id AND cm.date = cms.date
),
-- 计算最终活跃状态
final_result AS (
    SELECT 
        cust_id,
        total_price,
        date,
        CASE
            -- 首次交易前的月份直接标记为inactive
            WHEN date < first_trans_month THEN 'inactive'
            -- 当前月份有交易标记为active
            WHEN total_price > 0 THEN 'active'
            -- 无交易月份：检查与上一次交易间隔≤2个月，且与下一次交易间隔≤2个月（避免连续断档超2个月）
            ELSE CASE
                WHEN date_part('month', age(to_date(date, 'YYYY-MM'), to_date(last_active_month, 'YYYY-MM'))) <= 2 
                     AND (next_active_month IS NULL OR date_part('month', age(to_date(next_active_month, 'YYYY-MM'), to_date(date, 'YYYY-MM'))) <= 2)
                THEN 'active'
                ELSE 'inactive'
            END
        END AS is_active
    FROM customer_monthly_data
)
SELECT * FROM final_result ORDER BY cust_id, date;

关键步骤说明 #

1. 全量月份生成：用generate_series生成足够范围的月份，确保覆盖客户交易前后的所有需要补全的月份。
2. 客户-月份笛卡尔积：为每个客户匹配所有月份，得到完整的月度行框架。
3. 交易数据聚合：和你初步实现的逻辑一致，按月计算每个客户的交易额。
4. 关键日期计算：通过窗口函数MIN()、LAG()、LEAD()获取首次交易月、上/下一次交易月，为活跃状态判断提供依据。
5. 活跃状态判断：
   • 首次交易前的月份直接标记inactive
   • 有交易的月份直接标记active
   • 无交易月份通过计算与前后交易月的间隔，判断是否符合连续断档不超过2个月的规则，进而标记状态

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