如何在BigQuery中计算每行对应的前7天数值累计和

你可以直接使用BigQuery的窗口函数，基于日期范围的滚动窗口实现需求，该方案自动兼容日期不连续的场景，和你给出的样例输出完全匹配。

实现查询语句 #

假设你的原始数据表路径为项目名.数据集名.表名，查询代码如下：

SELECT
  Date,
  Value,
  -- 计算当前日期往前7天（含当前日期）的Value总和
  SUM(Value) OVER (
    ORDER BY UNIX_DATE(Date)
    RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS acc_7
FROM
  `your_project.your_dataset.your_table`
ORDER BY
  Date

逻辑说明 #

• UNIX_DATE(Date) 会将日期类型的字段转换为对应距离1970-01-01的整数天数，方便后续按天数范围做窗口偏移
• RANGE BETWEEN 6 PRECEDING AND CURRENT ROW 指定窗口范围为当前行对应的天数往前数6天，到当前天为止，合计刚好7天的区间，不管区间内有没有缺失的日期，都会正确计算存在的行的Value总和

特殊场景适配 #

如果你的Date字段存储为字符串格式，只需要调整窗口排序的逻辑即可：

SUM(Value) OVER (
  ORDER BY UNIX_DATE(DATE(Date))
  RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
) AS acc_7

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