BigQuery中无连接键的嵌套数组PIVOT转置问题

问题背景 #

需求是在BigQuery中对无连接键的嵌套数组执行PIVOT转置：将keys数组的每个元素转为独立列，对应values数组同位置的值作为行数据。现有数据集如下：

with raw as (
      select 
        ['a','b','c','d'] as keys,
        [1,2,3,4] values 
      union all 
      select 
        ['a','b','c','d']  ,
        [5,6,7,8]
    )

select * from raw

尝试的代码因未正确使用offset关联数组元素陷入瓶颈：

with raw as (
      select 
        ['a','b','c','d'] as keys,
        [1,2,3,4] values 
      union all 
      select 
        ['a','b','c','d']  ,
        [5,6,7,8]
    )
    
select *
from (
  select *
  from raw,
  unnest(keys) as un_keys,
  unnest(values) as un_values
)
pivot(max(un_values) for un_keys in ('a','b'))

核心问题 #

同时unnest两个数组时未通过索引关联，导致生成笛卡尔积，无法匹配keys和values对应位置的元素。

正确实现 #

通过with offset获取数组元素的索引位置，以此关联两个数组的对应元素，再执行PIVOT：

with raw as (
      select 
        ['a','b','c','d'] as keys,
        [1,2,3,4] values 
      union all 
      select 
        ['a','b','c','d']  ,
        [5,6,7,8]
    ),
    matched_data as (
      select
        -- 生成唯一行ID，保证原始每行数据独立转置
        generate_uuid() as row_id,
        key,
        value
      from raw,
        unnest(keys) as key with offset k_idx,
        unnest(values) as value with offset v_idx
      where k_idx = v_idx -- 用索引匹配对应位置的key和value
    )
select *
from matched_data
pivot(
  max(value) for key in ('a','b','c','d')
)

关键说明 #

• with offset：为数组每个元素生成索引，通过k_idx = v_idx确保keys和values同位置元素一一对应，避免笛卡尔积。
• row_id：作为原始每行数据的唯一标识，确保转置后每行对应原始数据的一行，防止数据被错误聚合。
• pivot子句：需列出所有要转为列的keys元素，最终结果会将每个key转为列，对应values的值填充到对应行。

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