动态实现按ID为数组内指定列从另一表匹配对应值

现有两个数据集，需要为每个ID的重要列数组补充对应数值，且代码必须具备动态适配能力——无需硬编码列名，可自动兼容列名变更或列数增减场景。

数据集定义 #

sample_data_1（含ID及多列数值） #

select 'Alice' AS ID, 2 AS col1, 5 AS col2, 6 AS col3, 0 AS col4
union all
select 'Bob' AS ID, 1 AS col1, 4 AS col2, -2 AS col3, 7 AS col4

sample_data_2（每个ID对应重要列的名称与排名数组） #

select 'Alice' AS ID, [STRUCT('col1' AS column, 1 AS rank), STRUCT('col4' AS column, 2 AS rank), STRUCT('col3' AS column, 3 AS rank)] AS important_columns
union all
select 'Bob' AS ID, [STRUCT('col4' AS column, 1 AS rank), STRUCT('col2' AS column, 2 AS rank), STRUCT('col1' AS column, 3 AS rank)]

动态解决方案（BigQuery SQL） #

要实现无硬编码的动态适配，可通过INFORMATION_SCHEMA自动获取列名，再拼接动态SQL逻辑：

DECLARE column_mapping STRING;

-- 自动生成列名到数值的映射逻辑
SET column_mapping = (
  SELECT STRING_AGG(
    FORMAT("WHEN '%s' THEN %s", column_name, column_name),
    ' '
  )
  FROM `your_project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'sample_data_1'
    AND column_name != 'ID'
);

-- 执行动态关联逻辑
EXECUTE IMMEDIATE FORMAT("""
WITH data_1 AS (SELECT * FROM sample_data_1),
data_2 AS (SELECT * FROM sample_data_2)
SELECT
  d2.ID,
  ARRAY(
    SELECT AS STRUCT
      ic.column,
      ic.rank,
      CASE ic.column %s END AS value
    FROM UNNEST(d2.important_columns) ic
  ) AS important_columns_with_values
FROM data_2 d2
JOIN data_1 d1 ON d2.ID = d1.ID
""", column_mapping);

方案说明 #

1. 动态列映射：通过查询元数据自动获取sample_data_1中除ID外的所有列，生成CASE WHEN映射规则，彻底摆脱硬编码限制。
2. 数组元素补值：将sample_data_2的数组元素展开后，匹配对应列的数值，再重新组合成带数值的新数组。
3. 输出结构：最终输出保留ID，新增的important_columns_with_values数组每个元素包含column（列名）、rank（排名）、value（对应数值）三个字段。

示例输出 #

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