如何在BigQuery中用split和unnest将多列拆分成行？

要实现你需要的拆分对齐效果，可以通过将列转换为数组、生成行索引、再按索引取值的方式完成，核心用到SPLIT、UNNEST和SAFE_OFFSET函数，具体步骤如下：

实现SQL #

假设你的源表名为your_table，执行以下查询：

WITH row_arrays AS (
  SELECT
    No,
    -- 将逗号分隔列转为数组，单个值列转为单元素数组
    IF(REGEXP_CONTAINS(Records, ','), SPLIT(Records, ','), [Records]) AS records_arr,
    IF(REGEXP_CONTAINS(value1, ','), SPLIT(value1, ','), [value1]) AS value1_arr,
    IF(REGEXP_CONTAINS(value2, ','), SPLIT(value2, ','), [value2]) AS value2_arr,
    IF(REGEXP_CONTAINS(value3, ','), SPLIT(value3, ','), [value3]) AS value3_arr,
    IF(REGEXP_CONTAINS(value4, ','), SPLIT(value4, ','), [value4]) AS value4_arr,
    IF(REGEXP_CONTAINS(value5, ','), SPLIT(value5, ','), [value5]) AS value5_arr,
    -- 计算当前行所有数组的最大长度，确定需要生成的行数
    GREATEST(
      ARRAY_LENGTH(IF(REGEXP_CONTAINS(Records, ','), SPLIT(Records, ','), [Records])),
      ARRAY_LENGTH(IF(REGEXP_CONTAINS(value1, ','), SPLIT(value1, ','), [value1])),
      ARRAY_LENGTH(IF(REGEXP_CONTAINS(value2, ','), SPLIT(value2, ','), [value2])),
      ARRAY_LENGTH(IF(REGEXP_CONTAINS(value3, ','), SPLIT(value3, ','), [value3])),
      ARRAY_LENGTH(IF(REGEXP_CONTAINS(value4, ','), SPLIT(value4, ','), [value4])),
      ARRAY_LENGTH(IF(REGEXP_CONTAINS(value5, ','), SPLIT(value5, ','), [value5]))
    ) AS total_rows
  FROM your_table
),
expanded_rows AS (
  SELECT
    No,
    records_arr,
    value1_arr,
    value2_arr,
    value3_arr,
    value4_arr,
    value5_arr,
    -- 生成从0到total_rows-1的索引，拆分出对应行数
    idx
  FROM row_arrays,
  UNNEST(GENERATE_ARRAY(0, total_rows - 1)) AS idx
)
SELECT
  No,
  -- 按索引取数组元素，超出数组长度自动返回null
  records_arr[SAFE_OFFSET(idx)] AS Records,
  value1_arr[SAFE_OFFSET(idx)] AS value1,
  value2_arr[SAFE_OFFSET(idx)] AS value2,
  value3_arr[SAFE_OFFSET(idx)] AS value3,
  value4_arr[SAFE_OFFSET(idx)] AS value4,
  value5_arr[SAFE_OFFSET(idx)] AS value5
FROM expanded_rows
ORDER BY No, idx;

逻辑说明 #

1. 转换为数组：用IF(REGEXP_CONTAINS(列名, ','), SPLIT(...), [列名])判断列值是否包含逗号，包含则拆分为数组，不包含则转为单元素数组。
2. 计算总行数：用GREATEST和ARRAY_LENGTH获取当前行所有数组的最大长度，确定需要拆分出多少行。
3. 生成行索引：通过UNNEST(GENERATE_ARRAY(0, total_rows - 1))生成从0开始的连续索引，将一行拆分为多行。
4. 按索引取值：用SAFE_OFFSET(idx)从数组中取对应位置的元素，索引超出数组长度时会返回null，正好实现对齐后补空的效果。

匹配示例输出的细节调整 #

如果你的Noval需要特殊处理（比如仅在有对应拆分元素时保留，否则返回null），可以在转换数组时将Noval替换为null，比如：

-- 以value3为例，其他列同理
IF(REGEXP_CONTAINS(value3, ','), SPLIT(value3, ','), IF(value3 = 'Noval', [NULL], [value3])) AS value3_arr

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