如何在BigQuery中使用PIVOT将数组展开为列以适配AutoML训练

完全可以通过BigQuery原生ELT方式实现需求，不需要客户端二次处理数据，以下是两种可落地的实现方案：

方案1：直接下标取值（推荐，性能最优） #

你的三个数组均为固定6个元素的定长数组，直接通过数组OFFSET下标取值即可，不需要行展开或透视操作，大数据量下执行效率最高，输出列完全符合要求：

SELECT
  -- 展开vRMSMin数组
  vRMSMin[OFFSET(0)] AS vRMSMin_0,
  vRMSMin[OFFSET(1)] AS vRMSMin_1,
  vRMSMin[OFFSET(2)] AS vRMSMin_2,
  vRMSMin[OFFSET(3)] AS vRMSMin_3,
  vRMSMin[OFFSET(4)] AS vRMSMin_4,
  vRMSMin[OFFSET(5)] AS vRMSMin_5,
  -- 展开vRMSMax数组
  vRMSMax[OFFSET(0)] AS vRMSMax_0,
  vRMSMax[OFFSET(1)] AS vRMSMax_1,
  vRMSMax[OFFSET(2)] AS vRMSMax_2,
  vRMSMax[OFFSET(3)] AS vRMSMax_3,
  vRMSMax[OFFSET(4)] AS vRMSMax_4,
  vRMSMax[OFFSET(5)] AS vRMSMax_5,
  -- 展开eReactiveNegativeKwh数组
  eReactiveNegativeKwh[OFFSET(0)] AS eReactiveNegativeKwh_0,
  eReactiveNegativeKwh[OFFSET(1)] AS eReactiveNegativeKwh_1,
  eReactiveNegativeKwh[OFFSET(2)] AS eReactiveNegativeKwh_2,
  eReactiveNegativeKwh[OFFSET(3)] AS eReactiveNegativeKwh_3,
  eReactiveNegativeKwh[OFFSET(4)] AS eReactiveNegativeKwh_4,
  eReactiveNegativeKwh[OFFSET(5)] AS eReactiveNegativeKwh_5,
  timestamp
FROM `mydata.data`

方案优势：

• 无中间行膨胀，不会产生冗余数据，查询速度远高于UNNEST+CROSS JOIN方案
• 列名完全匹配字段名_下标的要求，输出结果可直接用于AutoML训练

方案2：PIVOT函数实现 #

如果需要用PIVOT逻辑实现（比如后续数组长度可能变化的场景），可以先将数组的下标、值拆为行数据再做透视：

SELECT * EXCEPT(row_id)
FROM (
  SELECT
    GENERATE_UUID() AS row_id,
    timestamp,
    arr_name,
    offset,
    value
  FROM `mydata.data` t,
  -- 合并三个数组为统一结构
  UNNEST([
    STRUCT('vRMSMin' AS arr_name, vRMSMin AS arr),
    STRUCT('vRMSMax' AS arr_name, vRMSMax AS arr),
    STRUCT('eReactiveNegativeKwh' AS arr_name, eReactiveNegativeKwh AS arr)
  ]),
  -- 拆分每个数组的元素和对应下标
  UNNEST(arr) AS value WITH OFFSET
)
PIVOT(
  MAX(value) FOR CONCAT(arr_name, '_', offset) IN (
    'vRMSMin_0', 'vRMSMin_1', 'vRMSMin_2', 'vRMSMin_3', 'vRMSMin_4', 'vRMSMin_5',
    'vRMSMax_0', 'vRMSMax_1', 'vRMSMax_2', 'vRMSMax_3', 'vRMSMax_4', 'vRMSMax_5',
    'eReactiveNegativeKwh_0', 'eReactiveNegativeKwh_1', 'eReactiveNegativeKwh_2', 'eReactiveNegativeKwh_3', 'eReactiveNegativeKwh_4', 'eReactiveNegativeKwh_5'
  )
)

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