BigQuery中编写SQL查询获取含历史累计的每日注册用户总数
BigQuery 累计注册用户规模统计实现
BigQuery完全可以通过原生SQL实现目标统计效果,完全匹配给定的样例数据与输出规则。
实现逻辑
核心计算分为两步:
- 先按注册自然日聚合,统计当日的新增注册用户数(做去重避免脏数据干扰)
- 对每日新增数按日期顺序做累计求和,即可得到截至当日的历史总注册用户规模
BigQuery的窗口聚合在指定ORDER BY子句时,默认的计算范围为「从分区起始行到当前行」,刚好匹配累计统计的规则,相比传统的多表自连接实现,在百万级以上数据量下性能优势非常明显。
参考SQL
WITH daily_new_regist AS ( -- 按注册日聚合同日新增用户,替换为实际业务表路径 SELECT DATE(date_registered) AS register_date, COUNT(DISTINCT userID) AS daily_new_cnt FROM `your-project.your-dataset.user_registry_table` GROUP BY register_date ) -- 计算跨日累计值,输出匹配要求的结果结构 SELECT SUM(daily_new_cnt) OVER (ORDER BY register_date) AS total_user, register_date AS date FROM daily_new_regist ORDER BY register_date
结果验证
用题目给出的样例数据运行上述SQL,输出结果和预期完全一致:
| total_user | date |
|---|---|
| 2 | 2021-03-01 |
| 3 | 2021-03-04 |
| 6 | 2021-03-05 |
| 7 | 2021-03-06 |
| 8 | 2021-03-09 |
注意事项
- 如果确认业务表中每个userID仅对应1条注册记录,可将
COUNT(DISTINCT userID)替换为COUNT(1),查询性能会有明显提升 - 上述逻辑仅在存在注册行为的自然日生成统计结果,和题目给出的输出规则一致;如果需要补充无注册的空日期、保持累计值不变,可搭配BigQuery的日期生成函数(如
GENERATE_DATE_ARRAY)构造连续日期维度后再做关联计算。
内容的提问来源于stack exchange,提问作者Jayson O.
相关产品推荐
相关产品推荐

