BigQuery技术需求：如何用后续有效值填充code并保持至下次更新

需求说明 #

需要为每条记录的code字段填充后续出现的第一个有效值：

• 若当前记录code为NULL，取后续首次出现的非NULL值填充
• 若当前记录code已有有效值，保持原值，后续NULL值沿用该有效值直至下一个新的有效值出现

原始数据 #

期望输出 #

解决方案 #

可以使用BigQuery的窗口函数LAST_VALUE直接实现反向填充逻辑，代码简洁高效：

SELECT
  timestamp,
  session,
  user_id,
  LAST_VALUE(code IGNORE NULLS) OVER (
    PARTITION BY user_id 
    ORDER BY timestamp DESC
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) AS code
FROM your_table_name
ORDER BY timestamp;

代码解释

• PARTITION BY user_id：按用户维度独立处理数据，避免不同用户的code值混淆
• ORDER BY timestamp DESC：将时间倒序排列，让后续的记录排在当前行前面
• LAST_VALUE(code IGNORE NULLS)：忽略NULL值，提取窗口内最后一个非NULL的code（对应原始顺序中后续第一个有效值）
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：窗口范围包含当前行及所有后续行（倒序后即为原始顺序中当前行之后的所有记录）

如果需要兼容更复杂的分组场景，也可以用分组标记的方式实现：

WITH grouped_data AS (
  SELECT
    *,
    -- 反向累加非NULL code的次数，生成分组ID
    SUM(CASE WHEN code IS NOT NULL THEN 1 ELSE 0 END) OVER (
      PARTITION BY user_id 
      ORDER BY timestamp DESC
    ) AS code_group
  FROM your_table_name
)
SELECT
  timestamp,
  session,
  user_id,
  -- 每个分组内取唯一的非NULL code值
  MAX(code) OVER (PARTITION BY user_id, code_group) AS code
FROM grouped_data
ORDER BY timestamp;

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