BigQuery高效比较单表两行百+列差异并生成diff_field列

针对你需要对比同id下两行数据、标记差异列的需求，下面是一个无需硬编码所有列的优化方案，尤其适合100+列的大表场景：

WITH ranked_data AS (
  SELECT 
    *,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY time) AS rn,
    -- 打包当前行的业务列（排除id和time）为结构体
    (SELECT AS STRUCT t.* EXCEPT(id, time)) AS current_row
  FROM `your-project.your-dataset.your-table` t
),
prev_row_data AS (
  SELECT 
    *,
    -- 获取同id下上一行的业务列结构体
    LAG(current_row) OVER(PARTITION BY id ORDER BY time) AS prev_row
  FROM ranked_data
),
diff_columns AS (
  SELECT 
    * EXCEPT(rn, current_row, prev_row),
    -- 提取并对比所有列的键值对，收集差异列名
    ARRAY(
      SELECT key
      FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(current_row), r'"([^"]+)":')) AS key WITH OFFSET k1
      JOIN UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(prev_row), r'"([^"]+)":')) AS prev_key WITH OFFSET k2
        ON k1 = k2
      JOIN UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(current_row), r':([^,}]+)')) AS val WITH OFFSET k3
        ON k1 = k3
      JOIN UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(prev_row), r':([^,}]+)')) AS prev_val WITH OFFSET k4
        ON k1 = k4
      WHERE val != prev_val
    ) AS diff_array
  FROM prev_row_data
)
SELECT 
  *,
  -- 第一行无前置数据，diff_field设为null；其余行转成逗号分隔字符串
  IF(rn = 1, NULL, ARRAY_TO_STRING(diff_array, ',')) AS diff_field
FROM diff_columns
ORDER BY id, time;

方案说明 #

1. 动态列处理：通过SELECT AS STRUCT打包业务列，再用TO_JSON_STRING转成JSON格式，无需手动枚举100+列，彻底避免硬编码的维护成本。
2. 窗口函数取数：用ROW_NUMBER()按id和time排序标记行号，LAG()获取同组内上一行的完整业务数据，确保对比的是同id下的前后两行。
3. 差异列提取：通过正则从JSON中拆分列名和对应值，逐列对比后收集差异列名，最后转成逗号分隔的字符串输出。

注意事项 #

• 如果表中包含数组、结构体等复杂类型，TO_JSON_STRING的序列化逻辑需要调整，可针对复杂类型单独处理；
• 若列名包含特殊字符（比如双引号），正则表达式需要做适配，但BigQuery默认列名不会包含这类字符，一般无需担心。

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