如何将BigQuery的Array of Structs展平为单行并自定义列名？

完全可以实现这个需求，核心思路是先将数组拆分为多行，再通过PIVOT将行转换为指定列名的列，以下分两种场景给出具体实现方案：

场景1：已知所有可能的market + wait组合 #

如果提前明确所有需要生成的列名，可直接使用静态PIVOT语句：

-- 替换为你的项目、数据集、表名及实际字段
WITH flattened_rows AS (
  SELECT
    -- 保留原表的唯一标识字段（如id、uuid等），用于聚合单行数据
    primary_key,
    -- 生成目标列名
    CONCAT(p.market, ' ', p.wait) AS target_column,
    p.price
  FROM
    `your_project.your_dataset.your_table`,
    -- 拆分prices数组为多行
    UNNEST(prices) p
)
SELECT
  primary_key,
  -- 列出所有已知的目标列名（注意用反引号包裹含空格的列名）
  `MarketA 10min`,
  `MarketB 30min`,
  `MarketC 60min`
FROM
  flattened_rows
PIVOT(
  -- 使用聚合函数处理同一主键下的重复值，根据需求选MAX/AVG/FIRST_VALUE等
  MAX(price) FOR target_column IN (`MarketA 10min`, `MarketB 30min`, `MarketC 60min`)
);

场景2：未知所有market + wait组合（动态生成列） #

如果无法提前确定所有可能的列名，可用动态SQL自动生成PIVOT的列列表：

DECLARE pivot_columns STRING;

-- 自动提取所有唯一的target_column值，拼接成PIVOT需要的格式
SET pivot_columns = (
  SELECT STRING_AGG(DISTINCT CONCAT('`', CONCAT(p.market, ' ', p.wait), '`'), ', ')
  FROM `your_project.your_dataset.your_table`,
  UNNEST(prices) p
);

-- 构建并执行动态SQL
EXECUTE IMMEDIATE FORMAT("""
WITH flattened_rows AS (
  SELECT
    primary_key,
    CONCAT(p.market, ' ', p.wait) AS target_column,
    p.price
  FROM
    `your_project.your_dataset.your_table`,
    UNNEST(prices) p
)
SELECT *
FROM flattened_rows
PIVOT(
  MAX(price) FOR target_column IN (%s)
)
""", pivot_columns);

注意事项 #

• 替换代码中的primary_key为你表中的唯一标识字段（确保每个原始行可以被正确聚合为单行）
• 聚合函数（如MAX）用于处理同一主键下出现重复market + wait组合的情况，可根据实际需求替换为AVG、FIRST_VALUE等
• 含空格的列名必须用反引号（`）包裹，避免语法错误

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