求助：BigQuery计算月度回访用户占比时结果远超100000

核心问题分析 #

• 用户月度数据未去重：原monthly_user_counts中，同一个用户在同一个月可能有多条记录（对应多个event_date）。执行LEFT JOIN时会产生笛卡尔积：比如用户A在1月有10条记录、2月有8条记录，JOIN后会生成80条匹配行，导致SUM(CASE...)重复统计，returning_user_count被严重放大。
• 回访用户数计算方式错误：SUM(CASE...)会统计用户的匹配次数而非唯一用户数，而我们需要的是「上月存在且当月也存在的唯一用户数量」，应该用COUNT(DISTINCT)来统计。

修正后的代码 #

WITH monthly_user_counts AS (
  -- 先去重：每个用户每个月仅保留一条记录，只标记用户是否在当月活跃
  SELECT DISTINCT
    EXTRACT(YEAR FROM PARSE_DATE('%Y%m%d', event_date)) AS year,
    EXTRACT(MONTH FROM PARSE_DATE('%Y%m%d', event_date)) AS month,
    user_pseudo_id
  FROM
    `table`
),
returning_users AS (
  SELECT
    curr.year,
    curr.month AS current_month,
    -- 统计同时存在于当月和上月的唯一用户数
    COUNT(DISTINCT CASE WHEN prev.user_pseudo_id IS NOT NULL THEN curr.user_pseudo_id END) AS returning_user_count,
    COUNT(DISTINCT curr.user_pseudo_id) AS total_users
  FROM
    monthly_user_counts curr
  LEFT JOIN
    monthly_user_counts prev
  ON
    -- 修复跨年场景：比如1月的上月是去年12月
    ((curr.year = prev.year AND curr.month - 1 = prev.month)
     OR (curr.month = 1 AND prev.year = curr.year - 1 AND prev.month = 12))
    AND curr.user_pseudo_id = prev.user_pseudo_id
  GROUP BY
    curr.year, current_month
)
SELECT
  year,
  current_month,
  returning_user_count,
  total_users,
  -- 避免当月无用户时除以0的错误
  IF(total_users = 0, 0, (returning_user_count * 100.0 / total_users)) AS return_percentage
FROM
  returning_users
ORDER BY
  year, current_month;

额外优化说明 #

• 新增跨年月份匹配逻辑，覆盖1月用户回访去年12月的场景；
• 增加IF(total_users = 0, 0, ...)处理空数据除以0的异常。

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