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
  HAVING 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 前固化，否则分组结果缺乏业务意义。技术只是工具，业务理解才是准绳。