如何在BigQuery中计算当前行对应同一工作日的滚动平均值？（Legacy SQL）

刚好碰到过类似需求！你之前的写法是取连续21天的滚动平均，但现在要精准定位同一工作日的前3个同工作日，核心思路是先给每个ID的每个工作日的记录按日期排序，再基于这个排序取前3条的平均值。

直接上可运行的Legacy SQL代码：

SELECT
  id,
  date,
  weekday,
  sales_total,
  -- 计算当前行对应工作日的前3个同工作日销售平均值
  AVG(sales_total) OVER (
    PARTITION BY id, weekday
    ORDER BY weekday_row_num
    ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
  ) AS avg_last_3_same_weekday
FROM (
  SELECT
    id,
    date,
    weekday,
    sales_total,
    -- 给每个ID+工作日的组合按日期递增排序，生成行号
    ROW_NUMBER() OVER (
      PARTITION BY id, weekday
      ORDER BY date
    ) AS weekday_row_num
  FROM 数据表A
) AS ranked_data

关键逻辑解释： #

• 子查询排序：用ROW_NUMBER()给每个id和weekday的组合按日期从小到大编号，比如某ID的第1个周一、第2个周一...这样就能精准区分同工作日的先后顺序。
• 外层滚动平均：基于id和weekday分区，按生成的行号排序，用ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING锁定当前行的前3条到前1条同工作日记录，计算它们的sales_total平均值。

边界情况说明： #

• 如果某ID的某工作日只有1-2条历史记录（比如第1、2个周一），这个函数会自动计算现有历史记录的平均值（第2个周一取第1个的数值，第1个周一返回NULL）；
• 如果你希望不足3条时返回NULL，可以在外层加个CASE判断：

  CASE WHEN weekday_row_num >= 4 THEN
    AVG(sales_total) OVER (...)
  ELSE NULL END AS avg_last_3_same_weekday
  

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