如何在BigQuery中将表内空值替换为上一个非空值

核心通过带IGNORE NULLS参数的LAST_VALUE()窗口函数实现，操作如下：

前提注意事项 #

• 必须指定明确的排序字段（如自增ID、时间戳等），否则“上一个”没有逻辑依据，结果不可预期
• 如果需要按维度（如用户ID、品类）分别补全，在窗口函数中加PARTITION BY 分组字段即可
• 若字段开头几行本身就是空值，补全后仍为空，可搭配IFNULL()设置默认值处理该场景

示例代码 #

1. 全表排序补全单列 #

假设你的表为your_project.your_dataset.your_table，排序字段为order_id，需要补全的字段为target_col：

SELECT
  * EXCEPT(target_col),
  LAST_VALUE(target_col IGNORE NULLS) OVER (
    ORDER BY order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS target_col
FROM `your_project.your_dataset.your_table`

2. 按分组补全多列 #

比如按user_id分组，补全col1、col2、col3三个字段：

SELECT
  * EXCEPT(col1, col2, col3),
  LAST_VALUE(col1 IGNORE NULLS) OVER (
    PARTITION BY user_id
    ORDER BY order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS col1,
  LAST_VALUE(col2 IGNORE NULLS) OVER (
    PARTITION BY user_id
    ORDER BY order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS col2,
  LAST_VALUE(col3 IGNORE NULLS) OVER (
    PARTITION BY user_id
    ORDER BY order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS col3
FROM `your_project.your_dataset.your_table`

3. 处理开头空值场景 #

如果需要把字段开头的空值替换为指定默认值（比如0），搭配IFNULL()即可：

SELECT
  * EXCEPT(target_col),
  IFNULL(
    LAST_VALUE(target_col IGNORE NULLS) OVER (ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
    0
  ) AS target_col
FROM `your_project.your_dataset.your_table`

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