如何在SQL中使用GROUP BY统计留存率与流失用户数？-SQL-PHP中文网

留存率计算必须先明确定义“留存”的业务逻辑，如首日注册用户在第2、7、30天仍有活跃行为才算留存；需识别每个用户的first_login_date及后续login_date，不可直接按login_date分组计算，否则得到的是DAU/MAU而非留存率。

如何在sql中使用group by统计留存率与流失用户数？

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) <p>这个查询的关键是 <code>HAVING</code>：它在 <code>GROUP BY user_id</code> 后过滤每个用户的生命周期，而不是在原始行上过滤。漏掉 <code>HAVING</code> 或错用 <code>WHERE</code> 就会漏掉大量本该算作流失的用户。</p><h3>性能与兼容性：GROUP BY 前先过滤，别让大表硬扛</h3><p>用户行为表动辄上亿行，<code>GROUP BY user_id</code> 前不做时间范围限制，查询可能跑不出结果或被 OOM 杀掉。</p><p>务必做到：</p>

所有子查询都加 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 就得嵌套两层，而且日期处理容易跨月出错。