如何用SQL中的GROUP BY实现用户流失率阶段性统计方法

先说结论：直接用 GROUP BY 算不出真正的“阶段性流失率”——它最多能告诉你“某时间点有多少人已流失”，但完全无法表达“这批人在过去 N 天内从活跃变成流失”的动态过程。你需要先定义清楚“阶段”，再借助窗口函数或状态标记辅助分组，否则结果只是一张静态快照，与业务所说的“流失率”根本不是一回事。

为什么 GROUP BY 无法直接计算阶段性流失率

GROUP BY 本身不具备时间上下文，它仅对当前行集合执行聚合操作。例如，当你执行 SELECT status, COUNT(*) FROM users GROUP BY status 时，得到的 'churned' 只是当前被标记为流失的用户总数，与“上月仍活跃、本月转为流失”的动态变化毫无关联。

• 常见误区是按 MONTH(event_time) 分组后统计 COUNT(*)，实际上统计的是“当月有行为记录的流失用户”，而非“当月新增的流失用户”——两者含义截然不同
• 真正需要识别的是“最后一次活跃发生在前一周期、当前周期无任何行为”的用户，这要求跨周期比对，GROUP BY 单独无法胜任
• 如果数据表中没有明确的 churn_date 字段，仅靠 GROUP BY 配合 MAX(login_time) 会遗漏中间经历回归又再次流失的用户（例如活跃→沉默→回归→再沉默），边界情况一旦增多，统计结果就会出现偏差

利用 GROUP BY 结合子查询识别“当期新增流失用户”

核心逻辑非常清晰：首先定位每个用户的“最后活跃日期”，然后判断该日期是否落在前一个周期内，同时确保当前周期没有任何新行为记录。此时 GROUP BY 才真正发挥作用——用于按周期对已识别出的流失用户进行归类汇总。

• 先通过子查询或 CTE 计算每个用户的 last_active_date：MAX(login_time) OVER (PARTITION BY user_id)
• 外层查询利用 WHERE 条件过滤：例如 last_active_date <= '2026-05-31' AND last_active_date >= '2026-05-01'，表示最后活跃时间落在 5 月
• 同时确保该用户在 6 月（当前周期）没有任何 login_time 记录 —— 这一步必须使用 NOT EXISTS 或左连接配合 IS NULL，仅靠 GROUP BY 无法实现
• 最后通过 GROUP BY YEAR(last_active_date), MONTH(last_active_date) 统计各阶段的新增流失用户数量

下面以月为阶段举例说明：

SELECT  
  YEAR(last_active), MONTH(last_active) AS churn_month,
  COUNT(*) AS new_churn_count
FROM (
  SELECT 
    user_id,
    MAX(login_time) AS last_active
  FROM user_logins
  GROUP BY user_id
  HA VING MAX(login_time) <= '2026-05-31' 
     AND MAX(login_time) >= '2026-05-01'
) t
WHERE NOT EXISTS (
  SELECT 1 FROM user_logins u2 
  WHERE u2.user_id = t.user_id 
    AND u2.login_time >= '2026-06-01'
)
GROUP BY YEAR(last_active), MONTH(last_active);

补零显示缺失阶段时不要直接依赖 GROUP BY

如果某个月份没有新增流失用户，上述查询根本不会返回该行数据——GROUP BY 只会输出包含实际数据的组。然而业务报表通常要求“0 值也要展示”，这时不能指望修改 GROUP BY 解决，而应主动构造阶段维度再执行左连接。

• 建立一个包含所有目标月份的临时表或 VALUES 列表，例如 (2026,4), (2026,5), (2026,6)
• 使用 LEFT JOIN 连接前面得到的流失统计结果，然后通过 COALESCE(count, 0) 补零
• 切勿尝试用 GROUP BY 加 WITH ROLLUP 或 UNION ALL 拼接空行——这种做法逻辑混乱且难以维护
• 注意时区问题：所有日期比较必须统一转换，例如使用 login_time AT TIME ZONE 'Asia/Shanghai'，否则跨月边界可能出现错位

归根结底，真正的难点不在于编写 GROUP BY 语句，而在于清晰定义“阶段”和“流失”的业务含义。例如，“30 天未登录视为流失”就需要明确这 30 天从哪一天开始计算、是否排除节假日、按自然日还是滚动 24 小时计算——这些业务逻辑必须在进入 GROUP BY 之前就固定下来，否则分组结果将失去业务意义。技术只是实现手段，业务理解才是真正的衡量标准。