如何简化BigQuery中基于用户首末次交互的52周用户参与度计算？

核心思路 #

不用手动编写52个周统计字段，而是通过生成周偏移量序列结合窗口函数，动态计算每个用户在首次交互后各周的活跃情况，大幅简化查询结构并降低维护成本。

具体实现 #

WITH user_cohorts AS (
  -- 提取每个用户的首次交互周（Cohort分组）及所有交互记录
  SELECT
    user_id,
    DATE_TRUNC(MIN(interaction_time), WEEK) AS cohort_week,  -- 按周划分Cohort组
    interaction_time
  FROM
    `your-project.your-dataset.your-interaction-table`
  WHERE
    interaction_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 52 WEEK)  -- 限定过去52周数据
  GROUP BY
    user_id, interaction_time
),
week_offsets AS (
  -- 生成0到51的周偏移量（对应首次交互后的第0周到第51周）
  SELECT
    offset
  FROM
    UNNEST(GENERATE_ARRAY(0, 51)) AS offset
)
-- 关联数据并统计各Cohort每周的活跃用户数
SELECT
  cohort_week,
  offset AS weeks_since_cohort,
  COUNT(DISTINCT user_id) AS active_users
FROM
  user_cohorts
CROSS JOIN
  week_offsets
WHERE
  -- 判断交互时间是否属于首次交互后的对应周
  DATE_DIFF(DATE_TRUNC(interaction_time, WEEK), cohort_week, WEEK) = offset
GROUP BY
  cohort_week, offset
ORDER BY
  cohort_week, weeks_since_cohort;

关键说明 #

• user_cohorts子句：一次性获取所有用户的Cohort分组和交互记录，避免重复查询原始表。
• week_offsets子句：用GENERATE_ARRAY自动生成周偏移序列，无需手动编写52行重复代码。
• 关联逻辑：通过DATE_DIFF匹配用户交互时间对应的周偏移，再按Cohort和偏移量聚合统计。

扩展：计算留存率 #

如果需要统计各周活跃用户占Cohort总用户的比例，可加入Cohort规模计算：

WITH user_cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC(MIN(interaction_time), WEEK) AS cohort_week,
    interaction_time
  FROM
    `your-project.your-dataset.your-interaction-table`
  WHERE
    interaction_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 52 WEEK)
  GROUP BY
    user_id, interaction_time
),
cohort_sizes AS (
  -- 计算每个Cohort的总用户数
  SELECT
    cohort_week,
    COUNT(DISTINCT user_id) AS total_cohort_users
  FROM
    user_cohorts
  GROUP BY
    cohort_week
),
week_offsets AS (
  SELECT
    offset
  FROM
    UNNEST(GENERATE_ARRAY(0, 51)) AS offset
)
SELECT
  cs.cohort_week,
  wo.offset AS weeks_since_cohort,
  COUNT(DISTINCT uc.user_id) AS active_users,
  ROUND(COUNT(DISTINCT uc.user_id) / cs.total_cohort_users, 4) AS retention_rate
FROM
  user_cohorts uc
CROSS JOIN
  week_offsets wo
JOIN
  cohort_sizes cs ON uc.cohort_week = cs.cohort_week
WHERE
  DATE_DIFF(DATE_TRUNC(uc.interaction_time, WEEK), uc.cohort_week, WEEK) = wo.offset
GROUP BY
  cs.cohort_week, wo.offset, cs.total_cohort_users
ORDER BY
  cs.cohort_week, wo.offset;

优势对比 #

• 代码量固定，调整统计周期只需修改GENERATE_ARRAY的参数（比如改成0,103就是2年数据）。
• 避免手动复制粘贴导致的错误，维护成本极低。
• 利用BigQuery数组和交叉连接特性，性能优于多次重复子查询。

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