如何在BigQuery中将数组字段拆分为多个独立列

固定列数实现（性能最优，推荐优先使用） #

如果提前明确同一handle对应的identifiers最大数量，比如你的场景中最多3个，直接通过数组下标取值即可：

SELECT
  handle,
  identifiers[SAFE_OFFSET(0)] AS identifier_1,
  identifiers[SAFE_OFFSET(1)] AS identifier_2,
  identifiers[SAFE_OFFSET(2)] AS identifier_3
FROM (
  -- 先将同handle的所有identifier聚合为数组
  SELECT
    handle,
    ARRAY_AGG(DISTINCT identifiers) AS identifiers
  FROM `table.fdc`
  GROUP BY handle
)

说明：SAFE_OFFSET是BigQuery提供的安全下标方法，数组长度不足时会自动返回NULL，符合无对应值留空的需求。如果你的原表中identifiers本身就是数组类型，可跳过内层的GROUP BY聚合步骤，直接在外层查询用下标取值。

动态列数实现（适合identifier数量不固定的场景） #

如果无法提前确定最大的identifier数量，可以通过动态SQL自动生成对应列数的查询语句：

DECLARE max_id_count INT64;
DECLARE query_col_list STRING;

-- 计算同handle最多关联的identifier数量，确定生成列数
SET max_id_count = (
  SELECT MAX(identifier_count)
  FROM (
    SELECT handle, COUNT(DISTINCT identifiers) AS identifier_count
    FROM `table.fdc`
    GROUP BY handle
  )
);

-- 生成所有identifier列的查询片段
SET query_col_list = (
  SELECT STRING_AGG(
    CONCAT("identifiers[SAFE_OFFSET(", offset, ")] AS identifier_", offset + 1),
    ", "
  )
  FROM UNNEST(GENERATE_ARRAY(0, max_id_count - 1)) AS offset
);

-- 执行动态拼接的查询语句
EXECUTE IMMEDIATE FORMAT("""
  SELECT handle, %s
  FROM (
    SELECT handle, ARRAY_AGG(DISTINCT identifiers) AS identifiers
    FROM `table.fdc`
    GROUP BY handle
  )
""", query_col_list);

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