BigQuery高效实现前向填充及查询执行资源超限问题解决

不带PARTITION BY的ORDER BY窗口函数会要求所有数据在同一个计算节点上排序并维护窗口状态，当表行数较多时就会触发内存不足报错；你之前按天分区的方案解决了资源问题，但无法处理跨天的NULL填充（比如前一天最后一个有效值无法传递到次日开头的NULL行），所以仍有残留NULL。

方案1：简易窗口帧优化（改代码量最小） #

BigQuery对ROWS模式窗口的内存优化远好于默认的RANGE模式，先尝试给窗口显式指定行范围，大部分场景下可以直接解决资源不足问题：

SELECT time,
LAST_VALUE(number IGNORE NULLS) OVER(ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS number
FROM t

方案2：两阶段跨天填充（资源占用极低，稳妥方案） #

如果方案1仍报错，采用分阶段处理逻辑：先按天分区做内部填充，再处理天粒度的跨天值传递，全程只有天级别的极小数据量用到全局窗口，完全不会触发资源限制：

WITH daily_internal_fill AS (
  SELECT 
    time,
    number AS original_number,
    -- 单天内部前向填充
    LAST_VALUE(number IGNORE NULLS) OVER(PARTITION BY DATE(time) ORDER BY time) AS daily_filled_number,
    DATE(time) AS dt
  FROM t
),
-- 提取每天最后一个非空值
daily_last_val AS (
  SELECT 
    dt,
    LAST_VALUE(original_number IGNORE NULLS) OVER(
      PARTITION BY dt ORDER BY time 
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_val_of_day
  FROM daily_internal_fill
  QUALIFY ROW_NUMBER() OVER(PARTITION BY dt ORDER BY time DESC) = 1
),
-- 计算每天可以从前一天继承的有效值
daily_carry_over AS (
  SELECT 
    dt,
    LAST_VALUE(last_val_of_day IGNORE NULLS) OVER(
      ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS carry_over_val
  FROM daily_last_val
)
-- 合并结果：优先用单天填充值，空值用继承值填充
SELECT 
  a.time,
  COALESCE(a.daily_filled_number, b.carry_over_val) AS number
FROM daily_internal_fill a
LEFT JOIN daily_carry_over b ON a.dt = b.dt
ORDER BY time

这个方案返回结果和你最初的全局窗口逻辑完全一致，且支持任意量级的数据处理。

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