如何在BigQuery中实现类似Pandas bfill的全量Null值回填？

你遇到的问题太常见了——last_value()配合IGNORE NULLS确实能搞定中间和结尾的Null回填，但对第一个有效值出现之前的开头Null完全没辙。根本不用删这些行，咱们可以通过组合两个窗口函数来实现完整的bfill效果，和Pandas里的行为一致。

针对你示例的解决方案 #

先直接上能得到你期望输出的查询：

WITH table_path AS (
  SELECT 1 AS time, NULL AS sn_6 UNION ALL
  SELECT 2, 1 UNION ALL
  SELECT 3, NULL UNION ALL
  SELECT 4, NULL UNION ALL
  SELECT 5, NULL UNION ALL
  SELECT 6, 0 UNION ALL
  SELECT 7, NULL UNION ALL
  SELECT 8, NULL
)
SELECT
  time,
  sn_6,
  -- 核心逻辑：先常规回填，再用全局第一个有效值补开头的空
  COALESCE(
    last_value(sn_6 IGNORE NULLS) OVER (ORDER BY time),
    FIRST_VALUE(sn_6 IGNORE NULLS) OVER (ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  ) AS filled_sn_6
FROM table_path;

这个查询的逻辑拆解：

1. 常规回填部分：last_value(sn_6 IGNORE NULLS) OVER (ORDER BY time)就是你原来的逻辑，负责把中间、结尾的Null用最近的前一个有效值填充。
2. 开头Null补全部分：FIRST_VALUE(sn_6 IGNORE NULLS) OVER (ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)是关键——它会扫描整个数据集，找到第一个非Null的值。这里特意指定了窗口范围为全局，确保不管当前行在什么位置，都能拿到整个数据集里的第一个有效值。
3. 合并结果：用COALESCE()先取常规回填的结果，如果结果是Null（也就是开头那些在第一个有效值之前的行），就自动替换成全局第一个有效值。

适配你真实数据的扩展方案 #

你的真实数据有时间戳列+6个浮点列，直接把每个列套这个逻辑就行，示例如下：

WITH your_real_data AS (
  -- 替换成你的真实表或数据源查询
  SELECT timestamp_col, col1, col2, col3, col4, col5, col6 FROM your_table
)
SELECT
  timestamp_col,
  -- 保留原列，同时生成回填后的列
  col1,
  COALESCE(
    last_value(col1 IGNORE NULLS) OVER (ORDER BY timestamp_col),
    FIRST_VALUE(col1 IGNORE NULLS) OVER (ORDER BY timestamp_col ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  ) AS filled_col1,
  col2,
  COALESCE(
    last_value(col2 IGNORE NULLS) OVER (ORDER BY timestamp_col),
    FIRST_VALUE(col2 IGNORE NULLS) OVER (ORDER BY timestamp_col ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  ) AS filled_col2,
  col3,
  COALESCE(
    last_value(col3 IGNORE NULLS) OVER (ORDER BY timestamp_col),
    FIRST_VALUE(col3 IGNORE NULLS) OVER (ORDER BY timestamp_col ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  ) AS filled_col3,
  col4,
  COALESCE(
    last_value(col4 IGNORE NULLS) OVER (ORDER BY timestamp_col),
    FIRST_VALUE(col4 IGNORE NULLS) OVER (ORDER BY timestamp_col ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  ) AS filled_col4,
  col5,
  COALESCE(
    last_value(col5 IGNORE NULLS) OVER (ORDER BY timestamp_col),
    FIRST_VALUE(col5 IGNORE NULLS) OVER (ORDER BY timestamp_col ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  ) AS filled_col5,
  col6,
  COALESCE(
    last_value(col6 IGNORE NULLS) OVER (ORDER BY timestamp_col),
    FIRST_VALUE(col6 IGNORE NULLS) OVER (ORDER BY timestamp_col ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  ) AS filled_col6
FROM your_real_data
ORDER BY timestamp_col;

额外提示 #

如果你的数据是按某个字段分区的（比如按日期分区），记得在窗口函数里加上PARTITION BY子句，这样每个分区内的第一个有效值只会在分区内取，不会跨分区干扰，比如：

last_value(col1 IGNORE NULLS) OVER (PARTITION BY date_col ORDER BY timestamp_col)

内容的提问来源于stack exchange，提问作者Pedro Pablo Severin Honorato