留存率是跨日期用户重合度指标，如次日留存指注册当日用户中次日仍活跃的比例；因需关联多日行为，必须用JOIN或子查询而非GROUP BY。
什么是留存率，为什么不能直接用 GROUP BY 算
留存率不是单日行为统计，而是跨日期的用户重合度：比如「次日留存」指注册当天登录的用户中，第二天还回来的人占比。这意味着你必须把「第一天的行为」和「后续某天的行为」关联起来，而 GROUP BY 只能聚合同一行内的字段，无法跨行比对用户是否“又出现了”。子查询（或更优的 JOIN）是必须的路径。
用子查询匹配用户多日行为的典型写法
核心思路是：外层查「首日用户集合」，内层查「他们在目标日期是否存在行为」，再用 COUNT 和 CASE WHEN 统计比例。假设表为 user_event（含 user_id, event_date），计算每个用户的「次日留存」（以首次登录为起点）：
SELECT first_day.user_id, COUNT(second_day.user_id) * 1.0 / COUNT(*) AS retention_next_day FROM ( SELECT user_id, MIN(event_date) AS first_date FROM user_event GROUP BY user_id ) AS first_day LEFT JOIN user_event AS second_day ON first_day.user_id = second_day.user_id AND second_day.event_date = first_day.first_date + INTERVAL '1 day' GROUP BY first_day.user_id;
注意：INTERVAL '1 day' 是 PostgreSQL 写法；MySQL 用 DATE_ADD(first_date, INTERVAL 1 DAY)；SQLite 用 date(first_date, '+1 day') —— 不统一的日期函数是第一个易错点。
子查询嵌套太深？改用 CTE 更可读且避免重复计算
如果要同时算次日、7日、30日留存，用三层子查询会极难维护。CTE 能清晰拆分逻辑，且多数数据库（PostgreSQL、SQL Server、MySQL 8.0+）都支持：
WITH first_login AS (
SELECT user_id, MIN(event_date) AS first_date
FROM user_event GROUP BY user_id
),
retention_days AS (
SELECT
f.user_id,
f.first_date,
CASE WHEN u1.user_id IS NOT NULL THEN 1 ELSE 0 END AS retained_d1,
CASE WHEN u7.user_id IS NOT NULL THEN 1 ELSE 0 END AS retained_d7,
CASE WHEN u30.user_id IS NOT NULL THEN 1 ELSE 0 END AS retained_d30
FROM first_login f
LEFT JOIN user_event u1 ON f.user_id = u1.user_id AND u1.event_date = f.first_date + INTERVAL '1 day'
LEFT JOIN user_event u7 ON f.user_id = u7.user_id AND u7.event_date = f.first_date + INTERVAL '7 days'
LEFT JOIN user_event u30 ON f.user_id = u30.user_id AND u30.event_date = f.first_date + INTERVAL '30 days'
)
SELECT
AVG(retained_d1) AS d1_retention,
AVG(retained_d7) AS d7_retention,
AVG(retained_d30) AS d30_retention
FROM retention_days;关键提醒：LEFT JOIN 必须用在日期条件上，而不是先 JOIN 再 WHERE 过滤，否则会把未留存的用户直接丢掉，导致分母变小、结果虚高。
真实场景中容易被忽略的边界问题
实际跑出来的数字偏高或偏低，大概率栽在这几个细节上：
-
event_date字段是否带时分秒？若只取DATE(event_date)做比较，但原始数据是TIMESTAMP，跨天计算可能因时区或精度丢失出错 - 用户可能在同一天多次登录 ——
MIN(event_date)没问题，但后续匹配时若没去重，会导致同一用户在second_day中被计多次，影响分子 - 冷启动问题：新用户注册后第二天还没到，
first_date + 1 day可能超出当前数据时间范围，这类用户应保留在分母中（靠LEFT JOIN实现），但有些误写成INNER JOIN就悄悄过滤掉了 - 业务定义差异：「活跃」到底指什么？是登录？下单？还是页面浏览？子查询里的
user_event表必须和业务口径严格一致，混用不同行为表会导致指标失真
留存量子查询不难写，难的是让每一行数据都承载准确的业务含义——日期对齐、用户去重、行为定义、空值处理，四个点漏一个，结果就不可信。
