如何在BigQuery表中用下一个非空值填充指定列空值

首先明确：BigQuery完全支持LAG()、LEAD()及各类标准窗口函数，你的需求可以通过窗口函数组合实现。

你的需求是：按case_id分区、reply_date排序，将next_reply_target的空值替换为该行之后第一个非空值，无后续非空值则保持null。以下是精准实现的SQL代码：

方案代码 #

WITH target_groups AS (
  SELECT
    *,
    -- 为每个非空的next_reply_target分配唯一组编号
    CASE WHEN next_reply_target IS NOT NULL THEN ROW_NUMBER() OVER (PARTITION BY case_id ORDER BY reply_date) END AS group_id
  FROM `your_project.your_dataset.your_table` -- 替换为你的表路径
),
group_mapped AS (
  SELECT
    *,
    -- 向后填充组编号：让空行关联到后续第一个非空行的组
    LAST_VALUE(group_id IGNORE NULLS) OVER (
      PARTITION BY case_id
      ORDER BY reply_date
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS mapped_group
  FROM target_groups
)
SELECT
  case_id,
  reply_date,
  -- 同组内取唯一非空的next_reply_target值填充
  MAX(next_reply_target) OVER (PARTITION BY case_id, mapped_group) AS filled_next_reply_target,
  comment_type
FROM group_mapped
ORDER BY case_id, reply_date;

代码说明 #

1. target_groups CTE：给每个非空的next_reply_target行生成递增的组编号，空行组编号为null。
2. group_mapped CTE：通过LAST_VALUE(IGNORE NULLS)，让每个空行获取后续第一个非空行的组编号，实现“空行关联到最近的下一个非空行”。
3. 最终查询：按case_id和关联的组编号分组，取组内唯一的非空next_reply_target值，完成空值填充。

验证结果 #

针对你提供的样本数据，执行后会得到：

• 第1-5行的filled_next_reply_target为2024-07-11T18:17:21.000Z
• 第7行的filled_next_reply_target为2024-07-11T21:26:39.000Z
• 第9-10行的filled_next_reply_target为2024-07-12T19:50:12.000Z
• 第12行的filled_next_reply_target为2024-07-12T21:12:06.000Z
• 第14-15行无后续非空值，保持null

内容的提问来源于stack exchange，提问作者Mr.Mahajan