SQL统计留存率怎么做_用户行为分析SQL思路【指导】
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/Star
Rocks）或 DATEDIFF（MySQL）配合 GROUP BY 实现批量计算 - 高效率场景建议在数仓中预计算用户每日活跃宽表，再做JOIN，避免实时扫描大日志表
Rocks）或 DATEDIFF（MySQL）配合 GROUP BY 实现批量计算用窗口函数支持滚动留存（进阶）
如果要一次性输出每个注册日对应的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;
这种方法适合做趋势分析，但要注意数据量大时需加日期分区过滤，否则性能易下降。
上一篇 : Android优雅的方式解决软键盘遮挡按钮问题
下一篇 : 苹果手机怎么截图三种方法_苹果手机截屏三种方式详解
-
SEO外包最佳选择国内专业的白帽SEO机构，熟知搜索算法，各行业企业站优化策略！
SEO公司
-
可定制SEO优化套餐基于整站优化与品牌搜索展现，定制个性化营销推广方案！
SEO套餐
-
SEO入门教程多年积累SEO实战案例，从新手到专家，从入门到精通，海量的SEO学习资料！
SEO教程
-
SEO项目资源高质量SEO项目资源，稀缺性外链，优质文案代写，老域名提权，云主机相关配置折扣！
SEO资源
-
SEO快速建站快速搭建符合搜索引擎友好的企业网站，协助备案，域名选择，服务器配置等相关服务！
SEO建站
-
快速搜索引擎优化建议没有任何SEO机构，可以承诺搜索引擎排名的具体位置，如果有，那么请您多注意！专业的SEO机构，一般情况下只能确保目标关键词进入到首页或者前几页，如果您有相关问题，欢迎咨询！
