BigQuery中如何提取同组两行数据的差异及对应差异字段名？

核心实现逻辑 #

整体拆解为两个可复用的步骤，完全匹配你需要的Name、Field、Before、After结构输出：

• 第一步：对同一Name下的所有记录按规则排序配对，把相邻的两条记录拼接到同一行，拿到前后两个版本的全字段值
• 第二步：逐字段判断两个版本的取值是否存在差异，将有差异的字段转换为长表结构，每条差异单独输出一行

完整实现代码 #

假设源表为source_table，已包含Name、Phone Number、Address三个核心字段，补充record_time字段作为记录排序依据（无该字段时可替换为你能确定顺序的字段，保证配对的记录符合“连续两行”的要求）

WITH ranked_records AS (
  SELECT
    Name,
    `Phone Number`,
    Address,
    -- 按Name分组，按记录时间排序生成连续行号
    ROW_NUMBER() OVER (PARTITION BY Name ORDER BY record_time) AS record_seq
  FROM `你的项目ID.数据集名称.source_table`
),
record_pairs AS (
  SELECT
    curr.Name,
    curr.`Phone Number` AS before_phone,
    next.`Phone Number` AS after_phone,
    curr.Address AS before_address,
    next.Address AS after_address
  FROM ranked_records curr
  -- 自连接配对同Name下相邻的两条记录
  LEFT JOIN ranked_records next
    ON curr.Name = next.Name
    AND curr.record_seq = next.record_seq - 1
  -- 过滤掉无法配对下一条记录的孤立行
  WHERE next.Name IS NOT NULL
)
-- 逐字段筛选存在差异的记录，输出长表结构
SELECT
  Name,
  'Phone Number' AS Field,
  before_phone AS Before,
  after_phone AS After
FROM record_pairs
-- 空值兼容判断：避免两个空值被误判为存在差异
WHERE before_phone != after_phone
  AND NOT (before_phone IS NULL AND after_phone IS NULL)

UNION ALL

SELECT
  Name,
  'Address' AS Field,
  before_address AS Before,
  after_address AS After
FROM record_pairs
WHERE before_address != after_address
  AND NOT (before_address IS NULL AND after_address IS NULL)

输出效果验证 #

对照你给出的示例数据，最终输出的行记录完全匹配预期：

• John：仅输出1行Phone Number字段的对比记录，Address字段因两值完全一致被过滤
• Mary：仅输出1行Address字段的对比记录，Phone Number字段因两值完全一致被过滤
• Amy：输出2行记录，分别对应Phone Number、Address两个字段的取值对比

注意事项 #

• 空值兼容：BigQuery中NULL和任意值的对比都会返回未知结果，必须加上空值判断逻辑，否则会出现差异漏判
• 排序稳定性：如果缺少明确的排序字段，窗口函数生成的行号是随机的，会导致前后配对的记录顺序颠倒，Before和After的取值完全错位
• 多记录适配：如果某个Name下存在3条及以上的记录，这套逻辑可以自动完成相邻记录的逐次对比，不需要额外修改代码
• 扩展成本：后续如果需要新增对比字段，只需要在UNION ALL逻辑块中添加对应字段的对比分支即可

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