基于Amazon Redshift计算客户连续订阅年限（允许4个月断约）

核心思路 #

基于客户月度活跃数据，通过以下步骤计算连续订阅年限：

1. 预处理日期格式，将字符串类型的month-year转换为可计算的日期类型
2. 识别客户的订阅周期：当客户断约后重新活跃的间隔超过4个月时，标记为新订阅周期的开始
3. 基于每个周期的起始年份，计算当前月份属于该周期的第N年（断约不超4个月的月份仍归属原周期）

方案一：仅计算活跃月份的订阅年限 #

适用于只需要关注客户活跃月份的场景，假设原始表名为customer_monthly_active，字段为account_id、month_year（格式如YYYY-MM）、active（1表示活跃，0表示非活跃）：

WITH active_months AS (
    -- 过滤出所有活跃月份，转换日期格式并提取年份
    SELECT 
        account_id,
        TO_DATE(month_year, 'YYYY-MM') AS active_date,
        EXTRACT(YEAR FROM TO_DATE(month_year, 'YYYY-MM')) AS active_year
    FROM customer_monthly_active
    WHERE active = 1
    ORDER BY account_id, active_date
),
active_gaps AS (
    -- 计算相邻活跃月份的间隔，标记新周期起点
    SELECT 
        account_id,
        active_date,
        active_year,
        -- 计算与上一次活跃的间隔月份数
        DATE_PART('month', active_date - LAG(active_date) OVER (PARTITION BY account_id ORDER BY active_date)) 
            + 12 * DATE_PART('year', active_date - LAG(active_date) OVER (PARTITION BY account_id ORDER BY active_date)) AS months_since_last_active,
        -- 首次活跃或间隔超4个月时标记为新周期
        CASE 
            WHEN LAG(active_date) OVER (PARTITION BY account_id ORDER BY active_date) IS NULL THEN 1
            WHEN DATE_PART('month', active_date - LAG(active_date) OVER (PARTITION BY account_id ORDER BY active_date)) 
                + 12 * DATE_PART('year', active_date - LAG(active_date) OVER (PARTITION BY account_id ORDER BY active_date)) > 4 THEN 1
            ELSE 0
        END AS new_cycle_flag
    FROM active_months
),
cycle_counts AS (
    -- 累计每个客户的订阅周期数
    SELECT 
        account_id,
        active_date,
        active_year,
        SUM(new_cycle_flag) OVER (PARTITION BY account_id ORDER BY active_date) AS subscription_cycle
    FROM active_gaps
),
final_result AS (
    -- 计算每个活跃月份对应的订阅年限
    SELECT 
        account_id,
        TO_CHAR(active_date, 'YYYY-MM') AS month_year,
        -- 当前年份与周期起始年份的差值+1，得到第N年
        (EXTRACT(YEAR FROM active_date) - EXTRACT(YEAR FROM MIN(active_date) OVER (PARTITION BY account_id, subscription_cycle))) + 1 AS subscription_year
    FROM cycle_counts
)
SELECT * FROM final_result ORDER BY account_id, active_date;

方案二：包含所有月份的订阅年限（含非活跃月份） #

如果需要展示客户每个月的订阅状态（包括断约不超4个月的非活跃月份），可使用以下查询：

WITH all_customer_months AS (
    -- 生成所有客户的全量月份序列
    SELECT 
        a.account_id,
        generate_series(
            (SELECT MIN(TO_DATE(month_year, 'YYYY-MM')) FROM customer_monthly_active),
            (SELECT MAX(TO_DATE(month_year, 'YYYY-MM')) FROM customer_monthly_active),
            INTERVAL '1 month'
        ) AS month_date
    FROM (SELECT DISTINCT account_id FROM customer_monthly_active) a
),
customer_monthly_data AS (
    -- 关联原始活跃数据，补全非活跃月份的active标记
    SELECT 
        acm.account_id,
        TO_CHAR(acm.month_date, 'YYYY-MM') AS month_year,
        COALESCE(cma.active, 0) AS active
    FROM all_customer_months acm
    LEFT JOIN customer_monthly_active cma 
        ON acm.account_id = cma.account_id 
        AND acm.month_date = TO_DATE(cma.month_year, 'YYYY-MM')
),
active_tracking AS (
    -- 追踪每个月份的最近/下一次活跃日期
    SELECT 
        account_id,
        month_year,
        month_date,
        active,
        LAST_VALUE(CASE WHEN active = 1 THEN month_date END IGNORE NULLS) OVER (
            PARTITION BY account_id ORDER BY month_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS last_active_date,
        FIRST_VALUE(CASE WHEN active = 1 THEN month_date END IGNORE NULLS) OVER (
            PARTITION BY account_id ORDER BY month_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS next_active_date
    FROM customer_monthly_data
),
cycle_detection AS (
    -- 计算断约时长，判断是否属于原周期
    SELECT 
        account_id,
        month_year,
        month_date,
        active,
        CASE 
            WHEN active = 1 THEN 0
            ELSE DATE_PART('month', month_date - last_active_date) + 12 * DATE_PART('year', month_date - last_active_date)
        END AS months_since_last_active,
        CASE 
            WHEN active = 1 THEN 0
            ELSE DATE_PART('month', next_active_date - month_date) + 12 * DATE_PART('year', next_active_date - month_date)
        END AS months_until_next_active
    FROM active_tracking
),
cycle_assignment AS (
    -- 为每个月份分配所属的订阅周期
    SELECT 
        account_id,
        month_year,
        month_date,
        active,
        CASE 
            WHEN active = 1 OR months_until_next_active <=4 THEN 
                MIN(CASE WHEN active = 1 THEN month_date END) OVER (
                    PARTITION BY account_id, 
                        SUM(CASE WHEN active = 1 AND (months_since_last_active >4 OR last_active_date IS NULL) THEN 1 ELSE 0 END) OVER (
                            PARTITION BY account_id ORDER BY month_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                        )
                )
            ELSE NULL
        END AS cycle_start_date
    FROM cycle_detection
),
final_result AS (
    -- 计算最终的订阅年限
    SELECT 
        account_id,
        month_year,
        active,
        CASE 
            WHEN cycle_start_date IS NULL THEN NULL
            ELSE (EXTRACT(YEAR FROM month_date) - EXTRACT(YEAR FROM cycle_start_date)) +1 
        END AS subscription_year
    FROM cycle_assignment
)
SELECT * FROM final_result ORDER BY account_id, month_date;

注意事项 #

• 若month_year格式为MM-YYYY，需将TO_DATE函数的格式参数改为'MM-YYYY'
• Redshift中generate_series生成日期序列时，若版本不支持直接使用日期类型，可改用DATEADD循环生成
• 断约超过4个月的非活跃月份，subscription_year会标记为NULL，表示不属于任何有效订阅周期

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