月活跃用户分析：如何在统计结果中展示无事件发生的空白月份

问题根因 #

你当前的SQL仅能统计用户实际产生过行为的月份，没有行为的月份在原始行为表中无对应记录，自然不会出现在聚合结果里。要补全空白月份的0值记录，需要先生成每个用户从注册月到统计结束月的完整连续月份序列，再关联行为统计结果补值。

实现方案 #

以下是兼容主流数仓（PostgreSQL/Snowflake/SparkSQL等）的可运行实现，逻辑如下：

1. 计算每个用户的注册月（即Cohort月，取首次行为所在月份）
2. 生成覆盖全量统计范围的连续月份序列
3. 构造每个用户从注册月起所有待统计月份的全组合
4. 左关联行为统计结果，空白月份补0即可

WITH 
-- 1. 计算每个用户的注册月和统计截止月
user_cohort AS (
  SELECT 
    `user`,
    MIN(DATE_TRUNC('month', `Date`)) AS cohort_month,
    -- 统计截止月可根据需求替换为当前月：DATE_TRUNC('month', CURRENT_DATE)
    MAX(DATE_TRUNC('month', `Date`)) AS max_stat_month
  FROM `table`
  GROUP BY 1
),
-- 2. 生成连续月份序列，覆盖所有用户的注册月到统计截止月
-- 注：Hive/Spark可改用explode(sequence())生成，也可直接关联提前维护的日期维度表取月份，效率更高
all_months AS (
  SELECT DATE_TRUNC('month', generate_series(
    (SELECT MIN(cohort_month) FROM user_cohort),
    (SELECT MAX(max_stat_month) FROM user_cohort),
    INTERVAL '1 month'
  )) AS stat_month
),
-- 3. 生成每个用户需要统计的所有月份组合
user_month_comb AS (
  SELECT 
    uc.`user`,
    uc.cohort_month,
    am.stat_month
  FROM user_cohort uc
  CROSS JOIN all_months am
  WHERE am.stat_month >= uc.cohort_month
),
-- 4. 统计用户实际有行为月份的次数
action_agg AS (
  SELECT
    `user`,
    DATE_TRUNC('month', `Date`) AS action_month,
    COUNT(*) AS clicks
  FROM `table`
  GROUP BY 1,2
)
-- 5. 左关联补全0值得到最终结果
SELECT 
  umc.`user`,
  umc.cohort_month AS Cohort,
  umc.stat_month AS `Date`,
  COALESCE(aa.clicks, 0) AS Clicks
FROM user_month_comb umc
LEFT JOIN action_agg aa 
  ON umc.`user` = aa.`user` 
  AND umc.stat_month = aa.action_month
ORDER BY 1,3

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