BigQuery中使用LAST_VALUE填充ARRAY类型缺失数据的问题

尝试使用LAST_VALUE函数填充ARRAY类型字段的缺失数据，示例代码如下：

SELECT
  id_field,
  timestamp_field,
  LAST_VALUE(array_field) OVER(
    PARTITION BY id_field ORDER BY timestamp_field
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS array_field_carried
FROM table;

代码可执行，但无法正确携带数组中的所有值。

当前结果 #

期望结果 #

方法1：使用LAST_VALUE + IGNORE NULLS（推荐） #

多数现代SQL引擎（如BigQuery、PostgreSQL 13+）支持IGNORE NULLS参数，让LAST_VALUE跳过NULL值，直接取窗口内最近的非空数组。先完成数组填充，再展开数组即可得到预期结果：

WITH filled_data AS (
  SELECT
    id_field,
    timestamp_field,
    -- 填充最近的非空数组
    LAST_VALUE(array_field IGNORE NULLS) OVER(
      PARTITION BY id_field 
      ORDER BY timestamp_field
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS filled_array
  FROM your_table
)
-- 展开填充后的数组
SELECT
  id_field,
  timestamp_field,
  element AS array_field_carried
FROM filled_data, UNNEST(filled_array) AS element
UNION ALL
-- 保留原数组为NULL的行
SELECT
  id_field,
  timestamp_field,
  NULL AS array_field_carried
FROM filled_data
WHERE filled_array IS NULL
ORDER BY id_field, timestamp_field;

方法2：兼容不支持IGNORE NULLS的引擎 #

如果你的SQL引擎不支持IGNORE NULLS，可以用MAX()窗口函数替代（MAX自动忽略NULL值，取窗口内最后一个非空数组，效果与LAST_VALUE IGNORE NULLS一致）：

WITH filled_data AS (
  SELECT
    id_field,
    timestamp_field,
    MAX(array_field) OVER(
      PARTITION BY id_field 
      ORDER BY timestamp_field
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS filled_array
  FROM your_table
)
SELECT
  id_field,
  timestamp_field,
  element AS array_field_carried
FROM filled_data, UNNEST(filled_array) AS element
UNION ALL
SELECT
  id_field,
  timestamp_field,
  NULL AS array_field_carried
FROM filled_data
WHERE filled_array IS NULL
ORDER BY id_field, timestamp_field;

核心逻辑说明 #

• 必须先在未展开数组的层级完成填充，确保每一行都能获取到最近的非空数组，再执行数组展开操作。
• UNION ALL用于保留原数组为NULL的行，保证结果行数与原表完全匹配。

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