如何在DB2中高效计算近12个月客户流失率(留存率)？

问题背景 #

现有数据表包含Group Name（分组名称）、YYYYMM（年月）、Contracts（合同数）字段，需在DB2中高效计算近12个月的客户流失率/留存率，原SQL运行异常，需修正实现逻辑。

原问题SQL #

SELECT GROUP_NAME,ID, YRMO,SUM(FLOAT(CTC)) AS CTC_MOS,
MAX(CASE WHEN YRMO = VARCHAR_FORMAT(DATE(TO_DATE(@ENR_END_INC,'YYYYMM'))- 12 MONTHS, 'YYYYMM') THEN VARCHAR_FORMAT(DATE(TO_DATE(@ENR_END_INC,'YYYYMM'))- 12 MONTHS, 'YYYYMM') END)  AS YRMO_PRIOR,
SUM(CASE WHEN YRMO = VARCHAR_FORMAT(DATE(TO_DATE(@ENR_END_INC,'YYYYMM'))- 12 MONTHS, 'YYYYMM') THEN CTC END)  AS CTC_PRIOR

FROM TABLE1

原SQL存在的问题 #

• 缺失GROUP BY子句，SELECT中的非聚合字段未在GROUP BY中声明，触发语法错误
• 日期转换逻辑重复计算，降低执行效率，且未处理变量格式异常
• 未实现流失率/留存率的核心计算逻辑，仅获取了指定月份12个月前的合同数
• 引入未说明的ID字段，属于冗余或笔误内容

示例数据 #

正确实现方案 #

以下方案采用CTE聚合月度数据，结合窗口函数LAG高效获取12个月前的合同数，避免自连接带来的性能损耗，同时处理除数为0的异常情况：

WITH monthly_agg AS (
    -- 先按分组+年月聚合月度合同数
    SELECT 
        GP AS GROUP_NAME,
        YRMO,
        SUM(CTC) AS monthly_ctc
    FROM TABLE1
    GROUP BY GP, YRMO
),
prior_period_data AS (
    -- 用LAG窗口函数获取每个分组下12个月前的合同数和年月
    SELECT 
        GROUP_NAME,
        YRMO,
        monthly_ctc,
        -- 偏移12行（对应12个月前的数据）
        LAG(monthly_ctc, 12) OVER (PARTITION BY GROUP_NAME ORDER BY YRMO) AS prior_12m_ctc,
        LAG(YRMO, 12) OVER (PARTITION BY GROUP_NAME ORDER BY YRMO) AS prior_12m_yrmo
    FROM monthly_agg
)
SELECT 
    GROUP_NAME,
    YRMO,
    monthly_ctc AS current_ctc,
    prior_12m_yrmo,
    prior_12m_ctc,
    -- 留存率 = 当前月度合同数 / 12个月前月度合同数（保留2位小数）
    CASE 
        WHEN prior_12m_ctc = 0 THEN NULL
        ELSE ROUND(monthly_ctc / prior_12m_ctc * 100, 2) 
    END AS retention_rate,
    -- 流失率 = 1 - 留存率
    CASE 
        WHEN prior_12m_ctc = 0 THEN NULL
        ELSE ROUND((prior_12m_ctc - monthly_ctc) / prior_12m_ctc * 100, 2) 
    END AS churn_rate
FROM prior_period_data
-- 仅保留有12个月前对比数据的记录
WHERE prior_12m_ctc IS NOT NULL
ORDER BY GROUP_NAME, YRMO;

方案优势 #

1. 性能高效：窗口函数LAG仅需一次扫描数据，比自连接更适合大数据量场景
2. 逻辑清晰：通过CTE分步处理聚合、取历史数据、计算指标三个环节
3. 异常处理：针对除数为0的情况返回NULL，避免计算错误
4. 扩展性强：可快速调整偏移行数（如计算6个月留存）或新增其他指标

期望输出示例 #

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