在sql中按月份动态计算客户留存率,核心做法是利用窗口函数或自连接将当月活跃用户与上月活跃用户进行匹配,通过统计重合用户数除以当月总活跃用户数得出比率。具体实现时,需先按月聚合出每个用户的活跃状态,再使用lag()获取上月状态或直接关联上月数据表,最后用case when判断是否留存并求平均值。若某月无上月数据(如首月),通常将该月留存率视为100%或null,视业务定义而定。

如何构建按月活跃用户的基础数据集
基础数据集的构建是计算留存率的前提,必须确保每个用户在每个月只有一条记录,且明确标识其是否活跃。通常做法是从原始订单或登录表中提取用户ID和交易/登录日期,使用DATE_TRUNC或YEAR/MONTH函数将时间粒度压缩到月,并通过GROUP BY去重。这一步骤能消除同一用户同月多次行为带来的重复计数,为后续跨月对比提供干净的数据源。若原始数据存在脏数据或测试账号,建议在此阶段加入过滤条件排除非真实用户。

WITH monthly_active AS (
SELECT
user_id,
DATE_TRUNC('month', created_at) AS active_month
FROM orders
WHERE status = 'completed'
GROUP BY user_id, DATE_TRUNC('month', created_at)
)
SELECT * FROM monthly_active ORDER BY active_month, user_id;
上述代码从orders表中筛选已完成订单,按用户和月份分组,生成包含user_id和active_month两列的结果集。运行后每行代表一个用户在某个月份至少有一次完成订单的行为,例如2023-01-01至2023-01-31期间有购买行为的用户会被归入2023-01这一行。该数据集不包含任何留存逻辑,仅作为后续计算的输入基础。
如何通过自连接实现跨月留存匹配
跨月留存匹配的关键在于将当前月的用户集合与上一月的用户集合进行JOIN操作,从而识别出连续两个月都活跃的用户。具体做法是将基础数据集自连接,左表为当前月数据,右表为上月数据,连接条件为用户ID相同且右表的月份等于左表月份减一个月。这种写法直观易懂,适用于大多数支持标准SQL的数据库系统。需要注意的是,若使用MySQL等不支持DATE_SUB直接处理月份的版本,需用INTERVAL表达式替代。此外,对于没有上月数据的用户(即首月用户),LEFT JOIN会导致右表字段为空,需在最终计算时用COALESCE处理以避免除法错误。

WITH monthly_active AS (
SELECT
user_id,
DATE_TRUNC('month', created_at) AS active_month
FROM orders
WHERE status = 'completed'
GROUP BY user_id, DATE_TRUNC('month', created_at)
),
retention_calc AS (
SELECT
curr.active_month AS current_month,
COUNT(DISTINCT curr.user_id) AS total_users,
COUNT(DISTINCT prev.user_id) AS retained_users
FROM monthly_active curr
LEFT JOIN monthly_active prev
ON curr.user_id = prev.user_id
AND prev.active_month = curr.active_month - INTERVAL '1 month'
GROUP BY curr.active_month
)
SELECT
current_month,
total_users,
retained_users,
CASE
WHEN total_users = 0 THEN NULL
ELSE ROUND(retained_users::float / total_users, 4)
END AS retention_rate
FROM retention_calc
ORDER BY current_month;
该程序首先复用monthly_active CTE生成月度活跃用户表,然后在retention_calc中执行自连接:左表curr取当前月用户,右表prev取上月用户,通过user_id相等且月份相差一月建立关联。COUNT(DISTINCT curr.user_id)统计当月总活跃用户,COUNT(DISTINCT prev.user_id)统计同时出现在上月和本月的用户数。最终查询计算留存率时,若当月无用户则返回NULL,否则保留四位小数。以示例数据为例,若2023-02月有100个活跃用户,其中60个也在2023-01月活跃,则留存率为0.6000。此方法不依赖特定数据库的高级函数,兼容性强,适合生产环境部署。
当某月无历史数据时留存率应如何处理
当某月无历史数据时,意味着该月为首月或数据缺失,此时无法计算传统意义上的“相对于上月的留存率”。在实际业务中,通常有两种处理方式:一是将该月留存率设为100%,因为所有当月用户都是“新”用户,无需验证连续性;二是设为NULL,表示该指标不适用。选择哪种方式取决于报表需求——若用于趋势分析,NULL更准确反映数据断层;若用于KPI考核,100%可避免拉低整体均值。在代码层面,可通过在retention_calc中加入条件判断:若prev.active_month IS NULL,则标记为特殊值。例如,在最终SELECT中使用CASE WHEN prev.active_month IS NULL THEN 1.0 ELSE ... END来强制赋值为1。但需注意,这种做法会扭曲长期留存曲线,建议在可视化层面对首月做单独标注而非数值填充。










