Google Firebase与BigQuery场景：如何在SQL中将数组拆分为多列

固定元素数量拆分（性能最优，推荐优先使用） #

如果提前知道目标参数拆分后的最大元素数量，直接通过数组下标取值即可。BigQuery数组下标从0开始，使用[OFFSET(下标)]访问元素，当实际元素数量不足时，对应列会返回NULL，不会触发报错。
修改后的查询语句如下：

SELECT
    event_name,
    PARSE_DATE("%Y%m%d", event_date) as date, 
    TIMESTAMP_MICROS(event_timestamp) as timestamp,
    split_items[OFFSET(0)] AS item0,
    split_items[OFFSET(1)] AS item1,
    split_items[OFFSET(2)] AS item2
    -- 有更多拆分项时按规则继续添加对应OFFSET行即可
FROM (
    SELECT 
        *,
        (
         SELECT SPLIT(params.value.string_value, ',')
         FROM UNNEST(event_params) as params
         WHERE key = 'event_param-key_to_filter'
        ) AS split_items
    FROM `firebase.analytics.table`
)

先把数组拆分逻辑放到子查询中生成统一的split_items字段，外层直接按下标取对应元素命名，所有拆分后的值都会和原有字段处于同一行。

动态元素数量拆分 #

如果拆分后的元素数量不固定，无法提前确定列数，可以使用BigQuery动态SQL语法EXECUTE_IMMEDIATE自动生成查询语句：

DECLARE max_item_count INT64;
-- 统计所有行拆分后的最大元素数量
SET max_item_count = (
    SELECT MAX(ARRAY_LENGTH(SPLIT(params.value.string_value, ',')))
    FROM `firebase.analytics.table`,
    UNNEST(event_params) as params
    WHERE key = 'event_param-key_to_filter'
);

-- 自动生成对应列数的查询语句并执行
EXECUTE_IMMEDIATE format("""
SELECT
    event_name,
    PARSE_DATE("%%Y%%m%%d", event_date) as date, 
    TIMESTAMP_MICROS(event_timestamp) as timestamp,
    %s
FROM (
    SELECT 
        *,
        (
         SELECT SPLIT(params.value.string_value, ',')
         FROM UNNEST(event_params) as params
         WHERE key = 'event_param-key_to_filter'
        ) AS split_items
    FROM `firebase.analytics.table`
)
""", (SELECT STRING_AGG(format('split_items[OFFSET(%d)] AS item%d', i, i), ', ')
      FROM UNNEST(GENERATE_ARRAY(0, max_item_count - 1)) AS i)
);

以上逻辑会先自动统计拆分后的最大元素数，再自动生成对应数量的查询列，无需手动调整列数。

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