SQL统计留存率的核心是对比初始行为用户与后续某天仍活跃的同一用户，需准确定义活跃行为和首日、统一时间粒度、去重构造基准用户群，并通过JOIN或窗口函数计算各日留存人数及比率。
SQL统计留存率的核心是对比“初始行为用户”和“后续某天仍活跃的同一用户”，关键在准确圈定基准人群、识别回访行为、按时间维度对齐计算。
明确留存定义与时间粒度
留存率 = （第N日仍活跃的首日用户数 ÷ 首日活跃用户总数）× 100%。常见有次日留存、7日留存、30日留存。必须先约定“活跃行为”是什么（如登录、下单、访问首页），以及“首日”如何定义（如首次注册日、首次下单日、某活动参与日）。
- 若以“注册日”为起点，需确保表中有 user_id 和 register_date
- 若以“首次登录日”为起点，需先用窗口函数算出每个用户的 first_login_date
- 时间粒度统一用日期（DATE(event_time)），避免时分秒干扰分组
构造首日用户集合
用子查询或CTE提取所有在基准日（如2025-12-01）发生目标行为的用户，作为分母基础。例如：
WITH cohort AS (
SELECT DISTINCT user_id
FROM event_log
WHERE DATE(event_time) = '2025-12-01'
AND event_type = 'login'
)
这一步必须去重，避免同一用户多次行为重复计入分母。
关联后续行为并计算留存
将首日用户集合与后续日期的行为表左连接（或内连接，视是否只统计有回访者），按天聚合回访人数。例如统计次日留存：
SELECT
COUNT(DISTINCT c.user_id) AS cohort_size,
COUNT(DISTINCT e.user_id) AS retained_day1,
ROUND(COUNT(DISTINCT e.user_id) * 100.0 / COUNT(DISTINCT c.user_id), 2) AS retention_day1
FROM cohort c
LEFT JOIN event_log e
ON c.user_id = e.user_id
AND DATE(e.event_time) = '2025-12-02'
AND e.event_type = 'login';
- LEFT JOIN 保证分母完整；若用 INNER JOIN，则只保留有回访的用户，无法直接算比率
- 多日留存可扩展：用 DATE_DIFF（ClickHouse/StarRocks）或 DATEDIFF（MySQL）配合 GROUP BY 实现批量计算
- 高效率场景建议在数仓中预计算用户每日活跃宽表，再做JOIN，避免实时扫描大日志表
用窗口函数支持滚动留存（进阶）
如果要一次性输出每个注册日对应的7日留存曲线，可用窗口函数标记每个用户的首次行为日，再自连接判断间隔：
WITH first_act AS (
SELECT user_id, MIN(DATE(event_time)) AS first_date
FROM event_log WHERE event_type = 'login'
GROUP BY user_id
),
retention_days AS (
SELECT
fa.first_date,
DATEDIFF('day', fa.first_date, DATE(e.event_time)) AS diff_day
FROM first_act fa
INNER JOIN event_log e ON fa.user_id = e.user_id
WHERE DATE(e.event_time) >= fa.first_date
)
SELECT
first_date,
COUNT(DISTINCT CASE WHEN diff_day = 0 THEN user_id END) AS day0,
COUNT(DISTINCT CASE WHEN diff_day = 1 THEN user_id END) AS day1,
COUNT(DISTINCT CASE WHEN diff_day = 7 THEN user_id END) AS day7
FROM retention_days
GROUP BY first_date;
这种方法适合做趋势分析，但要注意数据量大时需加日期分区过滤，否则性能易下降。
