SQL实现月度用户报表：统计加入/离开人数及累计用户数

原SQL的问题分析 #

1. 分组逻辑错误：按year, month, event分组会导致每个月份生成两行记录（分别对应“加入”“离开”事件），不符合报表一行对应一个月份的要求。
2. 窗口排序逻辑错误：窗口函数ORDER BY event是按事件类型排序，而非时间顺序，无法正确计算跨月份的累计用户数。
3. 未区分事件计数：没有分别统计“加入”和“离开”的数量，而是将所有事件的计数混在一起。

正确SQL实现（以BigQuery为例） #

WITH monthly_stats AS (
  SELECT
    EXTRACT(YEAR FROM ts) AS 年份,
    LPAD(EXTRACT(MONTH FROM ts), 2, '0') AS 月份, -- 确保月份为两位数字格式
    COUNT(CASE WHEN event = '加入' THEN 1 END) AS `加入人数（Joiners）`,
    COUNT(CASE WHEN event = '离开' THEN 1 END) AS `离开人数（Leavers）`,
    -- 计算当月净增人数：加入数减离开数
    COUNT(CASE WHEN event = '加入' THEN 1 END) - COUNT(CASE WHEN event = '离开' THEN 1 END) AS 当月净增
  FROM `data.events`
  GROUP BY 年份, 月份
  ORDER BY 年份 ASC, 月份 ASC
)
SELECT
  年份,
  月份,
  `加入人数（Joiners）`,
  `离开人数（Leavers）`,
  -- 按时间顺序累计净增人数，得到累计用户数
  SUM(当月净增) OVER(ORDER BY 年份, 月份) AS `累计用户数（Total）`
FROM monthly_stats
ORDER BY 年份 ASC, 月份 ASC;

逻辑说明 #

1. 月度统计CTE：通过CASE WHEN条件聚合，分别统计每个月份的加入、离开人数，并计算当月净增人数。同时用LPAD确保月份显示为两位数字（如01而非1），匹配期望报表格式。
2. 累计用户数计算：使用窗口函数SUM(当月净增) OVER(ORDER BY 年份, 月份)，按时间顺序累计每个月的净增人数，得到截至当月的累计用户数。

示例数据测试结果 #

针对你提供的样本数据，运行后会得到如下结果：

内容的提问来源于stack exchange，提问作者digibake