SQL Server月度用户流失率批量计算方法咨询

需求说明 #

原SQL脚本仅能计算单个指定月份的用户流失率，现需改造为无需手动指定月份，自动输出每个月的以下指标：

• churn_rate：月度流失率（当月流失用户数/月初订阅用户数×100）
• retention_rate：月度留存率（1 - 流失率）
• n_start：月初订阅用户数
• n_churn：当月流失用户数

改造后的SQL脚本 #

WITH 
-- 生成所有需要统计的月度日期区间（示例为2022年1月至当前年月，可按需调整范围）
date_ranges AS (
    SELECT 
        DATEADD(month, number, '2022-01-01') AS start_date,
        EOMONTH(DATEADD(month, number, '2022-01-01')) AS end_date
    FROM master..spt_values
    WHERE type = 'P'
        AND DATEADD(month, number, '2022-01-01') <= GETDATE()
),
-- 按月份计算月初订阅用户
start_accounts AS (
    SELECT 
        dr.start_date,
        dr.end_date,
        s.ProductContractId
    FROM HD s 
    INNER JOIN date_ranges dr 
        ON s.FirstInvoiceDate <= dr.start_date
        AND (s.ItemRejectDate > dr.start_date OR s.ItemRejectDate IS NULL)
    GROUP BY dr.start_date, dr.end_date, s.ProductContractId
),
-- 按月份计算月末订阅用户
end_accounts AS (
    SELECT 
        dr.start_date,
        dr.end_date,
        s.ProductContractId
    FROM HD s 
    INNER JOIN date_ranges dr 
        ON s.FirstInvoiceDate <= dr.end_date
        AND (s.ItemRejectDate > dr.end_date OR s.ItemRejectDate IS NULL)
    GROUP BY dr.start_date, dr.end_date, s.ProductContractId
),
-- 按月份计算流失用户（月初有订阅、月末无订阅的用户）
churned_accounts AS (
    SELECT 
        sa.start_date,
        sa.end_date,
        sa.ProductContractId
    FROM start_accounts sa
    LEFT JOIN end_accounts ea 
        ON sa.ProductContractId = ea.ProductContractId
        AND sa.start_date = ea.start_date
    WHERE ea.ProductContractId IS NULL
),
-- 按月份聚合月初用户数
start_counts AS (
    SELECT 
        start_date,
        end_date,
        COUNT(*) AS n_start
    FROM start_accounts
    GROUP BY start_date, end_date
),
-- 按月份聚合流失用户数
churn_counts AS (
    SELECT 
        start_date,
        end_date,
        COUNT(*) AS n_churn
    FROM churned_accounts
    GROUP BY start_date, end_date
)
-- 计算最终指标并按月份排序
SELECT 
    FORMAT(sc.start_date, 'yyyy-MM') AS month,
    CONVERT(NUMERIC(10,4), (cc.n_churn * 1.0 / sc.n_start)) * 100 AS churn_rate,
    CONVERT(NUMERIC(10,4), (1 - cc.n_churn * 1.0 / sc.n_start)) * 100 AS retention_rate,
    sc.n_start,
    cc.n_churn
FROM start_counts sc
LEFT JOIN churn_counts cc 
    ON sc.start_date = cc.start_date
ORDER BY sc.start_date

关键改动说明 #

• 动态生成月度区间：用date_ranges CTE自动生成指定范围内的所有月份起止日期，无需手动修改日期参数；调整统计范围仅需修改起始年月和结束条件。
• 按月份分组计算：所有中间步骤关联月度区间并按月份分组，确保每个月份的指标独立计算。
• 修正留存率公式：原脚本留存率公式存在逻辑错误，已修正为标准的(1 - 流失率)×100，避免出现负数结果。
• 新增月份标识：输出yyyy-MM格式的month字段，适配折线图的时间轴展示需求。

跨数据库适配提示 #

若使用非SQL Server数据库，需调整date_ranges的生成方式：

• MySQL：用递归CTE或日期函数生成月度序列
• PostgreSQL：用generate_series函数生成月度区间

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