如何在BigQuery中实现按周分组的多时间范围滚动求和

GCP数据库滚动求和指标实现方案 #

以下方案适配GCP主流托管数据库，你可以根据自己实际使用的数据库类型选择对应逻辑：

通用前提 #

假设你的源表名为website_visit_stats，包含字段：

• date：日期类型，存储统计日期
• total_visits：数值类型，存储当日网站访问量

1. 适配BigQuery（GCP原生数仓） #

SELECT
  date,
  total_visits,
  -- 当日之前14天（不含当日）访问量求和，前14天自动返回NULL
  SUM(total_visits) OVER (
    ORDER BY UNIX_DATE(date)
    RANGE BETWEEN 14 PRECEDING AND 1 PRECEDING
  ) AS Column_A,
  -- 前4周至前2周区间（往前28天到往前15天）访问量求和，前28天自动返回NULL
  SUM(total_visits) OVER (
    ORDER BY UNIX_DATE(date)
    RANGE BETWEEN 28 PRECEDING AND 15 PRECEDING
  ) AS Column_B
FROM
  `你的项目ID.你的数据集名.website_visit_stats`
ORDER BY
  date ASC

说明：UNIX_DATE函数会将日期转为距离1970-01-01的整数天数，避免日期类型直接参与窗口RANGE计算的兼容问题。

2. 适配Cloud SQL for PostgreSQL #

SELECT
  date,
  total_visits,
  SUM(total_visits) OVER (
    ORDER BY date
    RANGE BETWEEN INTERVAL '14 days' PRECEDING AND INTERVAL '1 day' PRECEDING
  ) AS Column_A,
  SUM(total_visits) OVER (
    ORDER BY date
    RANGE BETWEEN INTERVAL '28 days' PRECEDING AND INTERVAL '15 days' PRECEDING
  ) AS Column_B
FROM
  website_visit_stats
ORDER BY
  date ASC

3. 适配Cloud SQL for MySQL（8.0及以上版本） #

SELECT
  date,
  total_visits,
  SUM(total_visits) OVER (
    ORDER BY TO_DAYS(date)
    RANGE BETWEEN 14 PRECEDING AND 1 PRECEDING
  ) AS Column_A,
  SUM(total_visits) OVER (
    ORDER BY TO_DAYS(date)
    RANGE BETWEEN 28 PRECEDING AND 15 PRECEDING
  ) AS Column_B
FROM
  website_visit_stats
ORDER BY
  date ASC

内容的提问来源于stack exchange，提问作者Hctor Alonso Hormazbal Vildsol