如何在Google BigQuery计算上周列总和？周数滚动查询异常排查

我给你两种实用的实现方式，按需选择就行：

方式1：先聚合周数据，再关联取上周值 #

如果需要每行都对应上周整周的总和，这种方式最稳妥，还能避免跨年周数重复的坑：

WITH weekly_sales AS (
  SELECT
    id,
    EXTRACT(WEEK FROM date) AS weeknumber,
    EXTRACT(YEAR FROM date) AS year, -- 必须加年份！不然2023和2024的第1周会混在一起
    SUM(sales_total) AS weekly_total
  FROM `mydataset.mytable`
  GROUP BY id, year, weeknumber
)
SELECT
  t.id,
  t.date,
  t.sales_total,
  ws.weekly_total AS last_week_total
FROM `mydataset.mytable` t
LEFT JOIN weekly_sales ws
ON t.id = ws.id
AND EXTRACT(YEAR FROM t.date) = ws.year
AND EXTRACT(WEEK FROM t.date) = ws.weeknumber + 1

方式2：用窗口函数直接算过去7天总和 #

要是你要的是“当前日期往前推7天到前1天”的总和（也就是自然上周的区间），用范围窗口更直接：

SELECT
  id,
  date,
  sales_total,
  SUM(sales_total) OVER(
    PARTITION BY id
    ORDER BY date
    RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND INTERVAL 1 DAY PRECEDING
  ) AS last_week_total
FROM `mydataset.mytable`

你踩的这个坑我太熟了——同一周的7天共享同一个weeknumber，用它当排序或分区键的话，窗口函数根本没法区分相邻周的边界！结合Mikhail那篇同工作日滚动平均的思路，咱们换个方式标识周就行：

修正后的查询（以过去4周滚动平均为例） #

先把数据按周聚合，用每周起始日期来唯一标识周（比weeknumber靠谱多了），再做滚动计算：

WITH weekly_agg AS (
  SELECT
    id,
    DATE_TRUNC(date, WEEK) AS week_start, -- BigQuery默认周一是周起始，可按需调整
    EXTRACT(WEEK FROM date) AS weeknumber,
    SUM(sales_total) AS weekly_sales
  FROM `mydataset.mytable`
  GROUP BY id, week_start, weeknumber
)
SELECT
  id,
  week_start,
  weeknumber,
  weekly_sales,
  -- 计算过去3周+当前周的滚动平均（共4周），想调整周数改数字就行
  AVG(weekly_sales) OVER(
    PARTITION BY id
    ORDER BY week_start
    ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
  ) AS rolling_4week_avg
FROM weekly_agg
ORDER BY id, week_start

如果你的需求是每年同一周数的滚动平均（比如所有年份的第10周数据的平均），那得按weeknumber分区，按年份排序：

SELECT
  id,
  date,
  weeknumber,
  sales_total,
  AVG(sales_total) OVER(
    PARTITION BY id, weeknumber
    ORDER BY EXTRACT(YEAR FROM date)
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS same_week_rolling_avg
FROM `mydataset.mytable`

为啥原查询不行？ #

你说周数连续7行相同，说明原窗口可能是按date排序但依赖weeknumber来划分范围，结果同一周内的行挤在一起，窗口函数没法正确计算跨周的滚动值。换成DATE_TRUNC(date, WEEK)当周的唯一标识后，每个周只有一个值，窗口就能按时间顺序准确计算啦。

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