在BigQuery中将事件行合并为单一行最新快照

要实现将增量事件行合并为单条最新数据快照（包括字段被设为NULL的场景），核心是精准定位每个字段最后一次被修改的行，无论修改后的值是否为NULL。以下是可行的SQL方案：

方案思路 #

1. 按事件发生的增量顺序（比如event_sequence或时间戳）对每行排序，确保能追踪到最新的修改记录。
2. 利用SPLIT将changedfields拆分为数组，方便判断当前行是否修改了目标字段。
3. 对每个字段，单独查询最后一次修改它的行并取值；若字段从未被修改，则取初始值（最早事件行的值）。

具体SQL实现 #

假设你的表结构包含id（唯一标识）、first_name__c、last_name__c、changedfields（逗号分隔的修改字段名）、event_sequence（事件增量序号）：

WITH ordered_events AS (
  SELECT
    id,
    first_name__c,
    last_name__c,
    changedfields,
    event_sequence,
    -- 将修改字段转为数组，方便后续判断
    SPLIT(changedfields, ',') AS changed_fields_arr
  FROM `your-project.your-dataset.your-table`
)
SELECT
  id,
  -- 获取first_name__c的最新值：优先取最后一次修改的值（含NULL），无修改则取初始值
  COALESCE(
    (SELECT first_name__c
     FROM ordered_events e2
     WHERE e2.id = e.id AND 'first_name__c' IN UNNEST(e2.changed_fields_arr)
     ORDER BY e2.event_sequence DESC LIMIT 1),
    (SELECT first_name__c
     FROM ordered_events e2
     WHERE e2.id = e.id
     ORDER BY e2.event_sequence ASC LIMIT 1)
  ) AS first_name__c,
  -- 获取last_name__c的最新值：处理最后一次被设为NULL的场景
  COALESCE(
    (SELECT last_name__c
     FROM ordered_events e2
     WHERE e2.id = e.id AND 'last_name__c' IN UNNEST(e2.changed_fields_arr)
     ORDER BY e2.event_sequence DESC LIMIT 1),
    (SELECT last_name__c
     FROM ordered_events e2
     WHERE e2.id = e.id
     ORDER BY e2.event_sequence ASC LIMIT 1)
  ) AS last_name__c
FROM ordered_events e
GROUP BY id;

关键说明 #

• 针对你提到的last_name__c最后被设为NULL的场景，子查询会精准定位到那一行并返回NULL，不会被之前的非NULL值覆盖。
• 避免使用FIRST_VALUE/LAST_VALUE ... IGNORE NULLS：这类语法会跳过NULL值，导致无法捕获字段被主动设为NULL的修改。
• 如果有更多字段，只需复制对应字段的子查询逻辑即可扩展。

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