如何在BigQuery中计算每月重置的排除历史用户的每日累计用户数

你的核心需求是计算每月重置的每日累计去重用户数——即每日累计数仅统计当月首次出现的用户，且每月重新开始计数。原自连接方案在大数据量下性能极差，因为会产生大量笛卡尔积，以下是优化方案：

优化思路 #

1. 压缩数据量：先提取每个用户在当月的首次出现日期，将重复的用户访问记录压缩为「用户-月份-首次日期」的唯一记录，大幅减少后续计算的数据规模。
2. 累计计数：基于首次出现日期，统计当月到当前日期为止，所有首次出现日期≤当前日期的用户总数，用窗口函数实现高效累计。

具体SQL实现 #

完整版本（适配日期不连续场景） #

WITH user_first_seen AS (
  -- 提取每个用户当月首次访问日期
  SELECT
    user_id,
    DATE_TRUNC(date_key, MONTH) AS month_key,
    MIN(date_key) AS first_seen_date
  FROM t1
  GROUP BY user_id, month_key
),
month_dates AS (
  -- 生成时间范围内的所有日期，避免缺失日期导致累计中断
  SELECT
    date_key,
    DATE_TRUNC(date_key, MONTH) AS month_key
  FROM UNNEST(GENERATE_DATE_ARRAY(
      (SELECT MIN(date_key) FROM t1),
      (SELECT MAX(date_key) FROM t1),
      INTERVAL 1 DAY
    )) AS date_key
),
daily_new_users AS (
  -- 统计每日新增的当月首次用户数
  SELECT
    first_seen_date AS date_key,
    COUNT(user_id) AS new_users,
    month_key
  FROM user_first_seen
  GROUP BY first_seen_date, month_key
)
-- 计算每日累计用户数
SELECT
  md.date_key,
  SUM(COALESCE(dnu.new_users, 0)) OVER (
    PARTITION BY md.month_key
    ORDER BY md.date_key ASC
  ) AS total_user
FROM month_dates md
LEFT JOIN daily_new_users dnu
  ON md.date_key = dnu.date_key
  AND md.month_key = dnu.month_key
ORDER BY md.date_key;

简化版本（适配日期连续场景） #

如果你的原表date_key每天都有数据，可省略日期生成步骤，简化为：

WITH user_first_seen AS (
  SELECT
    user_id,
    DATE_TRUNC(date_key, MONTH) AS month_key,
    MIN(date_key) AS first_seen_date
  FROM t1
  GROUP BY user_id, month_key
)
SELECT
  t.date_key,
  COUNT(DISTINCT ufs.user_id) AS total_user
FROM t1 t
LEFT JOIN user_first_seen ufs
  ON DATE_TRUNC(t.date_key, MONTH) = ufs.month_key
  AND ufs.first_seen_date <= t.date_key
GROUP BY t.date_key
ORDER BY t.date_key;

性能优势说明 #

原自连接方案的计算复杂度是O(n²)，会将每条记录与当月所有后续日期的记录配对，数据量呈指数级增长；优化方案先通过GROUP BY将数据压缩为「用户-月份」级别的唯一记录，数据量直接降到原表的1/N（N为用户每月平均访问天数），后续的JOIN和窗口计算均基于极小数据集，性能提升显著。

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