BigQuery SQL按日统计各阶段用户数高效实现方案咨询

完全不需要遍历日期逐天查询，直接通过日期表和用户里程碑表做关联后按条件聚合即可，单条SQL就能完成，计算效率远高于逐次查询，尤其适合数据量较大的场景。

实现逻辑说明 #

• 首先明确每个用户的阶段判定规则：
  • phase1：日期 >= milestone1 且 < milestone2
  • phase2：日期 >= milestone2 且（milestone3为null 或 日期 < milestone3）
  • phase3：日期 >= milestone3
• 将用户里程碑表和日期表做全关联，对每个用户+每个日期的组合按上述规则判定所属阶段，最后按日期分组聚合统计各阶段的用户数即可。

BigQuery 实现代码 #

WITH user_milestones AS (
  -- 替换为你的表1实际表名
  SELECT * FROM `your_project.your_dataset.table1`
),
date_range AS (
  -- 替换为你的表2实际表名
  SELECT * FROM `your_project.your_dataset.table2`
)
SELECT
  d.date,
  COUNTIF(d.date >= m.milestone1 AND d.date < m.milestone2) AS phase1,
  COUNTIF(d.date >= m.milestone2 AND (m.milestone3 IS NULL OR d.date < m.milestone3)) AS phase2,
  COUNTIF(m.milestone3 IS NOT NULL AND d.date >= m.milestone3) AS phase3
FROM date_range d
CROSS JOIN user_milestones m
GROUP BY d.date
ORDER BY d.date

性能优化提示 #

如果用户量非常大，还可以做进一步优化：

• 先给用户里程碑表加过滤条件，只保留里程碑日期在日期表时间范围内的用户，减少关联的数据量
• 如果日期范围极大、用户量过亿，可以先把每个用户的阶段时间区间拆出来，再和日期表做区间关联，进一步降低计算量

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