留存率计算必须先明确定义“留存”的业务逻辑，如首日注册用户在第2、7、30天仍有活跃行为才算留存；需识别每个用户的first_login_date及后续login_date，不可直接按login_date分组计算，否则得到的是DAU/MAU而非留存率。
GROUP BY 统计留存率时，必须先定义「留存」的业务逻辑
留存率不是数据库自动算出来的指标，它依赖你对「用户在某天是否算留存」的明确定义。比如：首日注册用户，在第2天、第7天、第30天仍有活跃行为才算留存。这意味着你至少要能识别出每个用户的 first_login_date 和后续每天的 login_date，否则 GROUP BY 再多也无从下手。
常见错误是直接对 login_date 分组后算「当日登录人数 / 首日注册人数」——这算的是渗透率或 DAU/MAU，不是留存率。
实操建议：
- 先用窗口函数（如
MIN(login_date) OVER (PARTITION BY user_id)）为每个用户打上first_login_date - 再计算每个用户每次登录距首登的天数：
DATEDIFF(login_date, first_login_date) - 之后才能按「首登日期」和「间隔天数」双维度
GROUP BY，例如统计「2024-01-01首登的用户中，有多少人在第1天（即次日）又登录了」
用 GROUP BY + CASE WHEN 算次日留存率（最常用场景）
次日留存率 = 首登次日仍活跃的用户数 / 首登总用户数。关键在于不能只用一个 GROUP BY login_date，而要按「首登日」分组，并在组内判断是否存在「首登+1天」的行为。
示例（MySQL 8.0+ 或 PostgreSQL）：
SELECT
first_day,
COUNT(DISTINCT user_id) AS cohort_size,
COUNT(DISTINCT CASE WHEN diff_days = 1 THEN user_id END) AS retained_next_day,
ROUND(
COUNT(DISTINCT CASE WHEN diff_days = 1 THEN user_id END) * 100.0 /
COUNT(DISTINCT user_id), 2
) AS retention_rate_1d
FROM (
SELECT
user_id,
MIN(login_date) AS first_day,
login_date,
DATEDIFF(login_date, MIN(login_date) OVER (PARTITION BY user_id)) AS diff_days
FROM user_activity
WHERE login_date >= '2024-01-01'
GROUP BY user_id, login_date
) t
GROUP BY first_day;注意点：
-
GROUP BY user_id, login_date是为了去重单日多次登录；若原始表已去重，可省略 -
MIN(login_date) OVER (...)必须在子查询里算，不能直接在外部GROUP BY中引用窗口函数结果 -
DATEDIFF在不同数据库写法不同：PostgreSQL 用login_date - first_day，SQL Server 用DATEDIFF(day, first_day, login_date)
流失用户数要避开「未登录 ≠ 流失」的认知陷阱
直接统计「某天之后再没登录的用户数」不等于流失用户数——可能是新用户还没到活跃周期，也可能是数据上报延迟。真正可落地的流失定义通常是：「过去 N 天内无任何行为，且首次行为发生在 M 天前」。
例如定义「7天未活跃且首登 ≥ 14 天前」为流失，那么流失用户数就得这样算：
SELECT
first_day,
COUNT(DISTINCT user_id) AS churned_users
FROM (
SELECT
user_id,
MIN(login_date) AS first_day,
MAX(login_date) AS last_active
FROM user_activity
GROUP BY user_id
HAVING MAX(login_date) <= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND MIN(login_date) <= DATE_SUB(CURDATE(), INTERVAL 14 DAY)
) t
GROUP BY first_day;这个查询的关键是 HAVING：它在 GROUP BY user_id 后过滤每个用户的生命周期，而不是在原始行上过滤。漏掉 HAVING 或错用 WHERE 就会漏掉大量本该算作流失的用户。
性能与兼容性：GROUP BY 前先过滤，别让大表硬扛
用户行为表动辄上亿行，GROUP BY user_id 前不做时间范围限制，查询可能跑不出结果或被 OOM 杀掉。
务必做到：
- 所有子查询都加
WHERE login_date BETWEEN ... AND ...，覆盖你要分析的首登区间和行为观察期 - 确保
user_id和login_date上有联合索引，例如INDEX idx_user_date (user_id, login_date) - MySQL 5.7 不支持在子查询中直接用窗口函数，得用自连接或变量模拟，复杂度陡增；如果用的是旧版本，优先考虑把中间结果物化到临时表
真正难的从来不是写对 GROUP BY，而是把业务定义翻译成可执行的 SQL 路径——尤其当产品说「我们要看自然月留存」时，得立刻意识到他们其实在问「按首登所在自然月分组，再看各月用户在后续每月的留存情况」，这时候 GROUP BY 就得嵌套两层，而且日期处理容易跨月出错。
