SQL中如何借助GROUP BY完成用户流失率的阶段性统计

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_countFROM (  SELECT     user_id,    MAX(login_time) AS last_active  FROM user_logins  GROUP BY user_id  HAVING MAX(login_time) <= '2026-05-31'      AND MAX(login_time) >= '2026-05-01') tWHERE 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 之前就固化好，否则分组结果毫无业务意义。