BigQuery多列UNNEST按序列匹配对应值 避免交叉连接方案

问题描述 #

在BigQuery中对多列数组执行UNNEST操作时，默认的多UNNEST逗号关联会生成笛卡尔交叉连接结果，无法实现「数组第N位的值仅和其他数组第N位的值匹配」的效果，无法还原数组聚合前的原始表结构。

示例错误写法（生成笛卡尔积）：

with table1 as (
    select 'F1' field, 'm1' m_id, 1 m_unit, 5 m_cost union all
    select 'F1' field,'m2' m_id, 2 m_unit, 3 m_cost  union all
    select 'F1' field, 'm3' m_id, 2 m_unit, 2 m_cost
)

, table2 AS (
    SELECT
        field
        , ARRAY_AGG(m_id IGNORE NULLS) AS m_id
        , ARRAY_AGG(m_unit IGNORE NULLS) AS m_unit
        , ARRAY_AGG(m_cost IGNORE NULLS) AS m_cost
    FROM table1
    GROUP BY 1
)

SELECT * EXCEPT (m_id, m_unit, m_cost)
FROM table2,
UNNEST (m_id) as m_id_unnested,
UNNEST (m_unit) as m_unit_unnested,
UNNEST (m_cost) as m_cost_unnested

上述写法会返回333=27条交叉连接结果，和原始table1仅3条的输出完全不符。

解决方法 #

使用WITH OFFSET语法获取每个数组元素对应的位置偏移量，通过偏移量等值关联匹配同位置的数组元素，即可避免笛卡尔积，还原聚合前的表结构：

with table1 as (
    select 'F1' field, 'm1' m_id, 1 m_unit, 5 m_cost union all
    select 'F1' field,'m2' m_id, 2 m_unit, 3 m_cost  union all
    select 'F1' field, 'm3' m_id, 2 m_unit, 2 m_cost
)

, table2 AS (
    SELECT
        field
        , ARRAY_AGG(m_id IGNORE NULLS) AS m_id
        , ARRAY_AGG(m_unit IGNORE NULLS) AS m_unit
        , ARRAY_AGG(m_cost IGNORE NULLS) AS m_cost
    FROM table1
    GROUP BY 1
)

SELECT field, m_id_unnested, m_unit_unnested, m_cost_unnested
FROM table2 LEFT JOIN
UNNEST(m_id) m_id_unnested WITH OFFSET pos1
ON 1=1 LEFT JOIN
UNNEST(m_unit) m_unit_unnested WITH OFFSET pos2
ON pos1 = pos2 LEFT JOIN
UNNEST(m_cost) m_cost_unnested WITH OFFSET pos3
ON pos1 = pos3
;

注意事项 #

• 第一个数组的UNNEST通过ON 1=1和主表左连接，后续所有数组的UNNEST都通过偏移量和第一个数组的偏移量做等值匹配即可
• 同一次分组、相同排序逻辑下生成的多列ARRAY_AGG结果长度天然一致，不会出现匹配错位问题
• 若待匹配数组长度不一致，该写法会保留最长数组的所有位置，短数组对应位置返回NULL，符合左连接的常规逻辑

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