You need to enable JavaScript to run this app. BigQuery实现单查询周度同期pageviews、users、sessions对比及2021年预测
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.10 07:36:08