BigQuery实现单查询周度同期pageviews、users、sessions对比及2021年预测
BigQuery单查询实现同期周度指标对比+粗略预测方案
实现核心逻辑
因为你现有数据是天粒度,不需要拆分多次查询,单条SQL即可完成所有计算,核心处理点:
- 对齐同期对比基准:不使用BigQuery默认的自然周切分规则,直接以每年2月1日为起点,把14天的时间范围切分为2个固定长度的周区间(前7天为第一周、后7天为第二周),避免不同年份2月起始日的周历错位问题——这是跨年份同期对比最容易踩的坑
- 单次扫描天粒度原始表,直接筛选2019、2020年2月1日-14日的全量数据,按对齐后的周区间聚合
pageviews、users、sessions三个指标 - 2021年的粗略预测直接取两年同周指标的算术平均值,逻辑简单计算效率高,适配“粗略预测”的需求
- 最终通过行转列的方式,把三年的指标值在同一结果行横向展示,直接可以做对比查看
可直接复用的BigQuery语法SQL
WITH raw_daily_metrics AS ( -- 替换为你实际的天粒度指标表,字段需匹配:日期字段、pageviews、users、sessions SELECT event_date AS date, pageviews, active_users AS users, sessions FROM `your_project.your_dataset.your_daily_data_table` -- 单次拉取所有需要覆盖的时间范围,避免重复扫表 WHERE event_date BETWEEN '2019-02-01' AND '2020-02-14' ), aligned_weekly_segment AS ( SELECT EXTRACT(YEAR FROM date) AS data_year, -- 按同期规则切分周段,完全不受自然周历偏移影响 CASE WHEN DATE_DIFF(date, DATE(EXTRACT(YEAR FROM date), 2, 1), DAY) < 7 THEN '第一周(2.1-2.7)' ELSE '第二周(2.8-2.14)' END AS compare_week, pageviews, users, sessions FROM raw_daily_metrics ), aggregated_actual_value AS ( -- 聚合两个年份的实际周度指标 SELECT compare_week, SUM(IF(data_year=2019, pageviews, 0)) AS pv_2019, SUM(IF(data_year=2019, users, 0)) AS users_2019, SUM(IF(data_year=2019, sessions, 0)) AS sessions_2019, SUM(IF(data_year=2020, pageviews, 0)) AS pv_2020, SUM(IF(data_year=2020, users, 0)) AS users_2020, SUM(IF(data_year=2020, sessions, 0)) AS sessions_2020 FROM aligned_weekly_segment GROUP BY compare_week ) SELECT compare_week AS 对比周维度, pv_2019 AS `2019年pageviews`, pv_2020 AS `2020年pageviews`, ROUND((pv_2019 + pv_2020)/2, 0) AS `2021年pageviews_预测值`, users_2019 AS `2019年users`, users_2020 AS `2020年users`, ROUND((users_2019 + users_2020)/2, 0) AS `2021年users_预测值`, sessions_2019 AS `2019年sessions`, sessions_2020 AS `2020年sessions`, ROUND((sessions_2019 + sessions_2020)/2, 0) AS `2021年sessions_预测值` FROM aggregated_actual_value -- 保证结果按周次顺序排列,不会出现排序混乱 ORDER BY IF(compare_week='第一周(2.1-2.7)', 1, 2)
跑数注意事项
- 绝对不要直接用BigQuery自带的
EXTRACT(WEEK FROM date)函数切周,不同年份2月1日对应的周序虽然数字接近,但对应的实际日期偏移可达1-2天,切出来的周区间完全没法做同期对比 - 如果需要让2021年的预测值更贴近实际,可以直接在最后一层的计算里,把两年均值的逻辑替换成“2020年同期值乘过往一年的周均同比增速”,不需要调整整体查询结构
- 如果原始天粒度数据存在单日数据缺失的情况,可以在
raw_daily_metrics层把缺失日期的指标值补为0,避免聚合后的周度值比真实值偏低
内容的提问来源于stack exchange,提问作者Divyang Jain
相关产品推荐
相关产品推荐

