BigQuery中按逗号拆分多列转成行并保持1:1匹配的SQL实现

你的问题核心是当前SQL会对两个拆分后的数组产生笛卡尔积，导致tag_id和tag_name的对应关系错乱。以下是两种可行的解决方案，同时满足你用EXCEPT保留其他列的需求：

问题回顾 #

原表结构：

id    tag_id    tag_name
    1   1, 2, 4   a, b, d
    2   3         c
    3   1, 4      a, d

期望输出：

id    tag_id    tag_name
1          1    a
1          2    b
1          4    d
2          3    c
3          1    a
3          4    d

方案一：通过偏移量匹配对应元素 #

利用WITH OFFSET获取每个元素在数组中的位置，只保留位置相同的元素对，避免笛卡尔积：

SELECT 
  * EXCEPT(t, tn, offset_t, offset_tn) 
  REPLACE(t AS tag_id, tn AS tag_name)
FROM `your_table`,
UNNEST(SPLIT(tag_id, ', ')) t WITH OFFSET offset_t,
UNNEST(SPLIT(tag_name, ', ')) tn WITH OFFSET offset_tn
WHERE offset_t = offset_tn

方案二：打包STRUCT后拆分（更高效） #

先将同一位置的tag_id和tag_name打包成结构体，再拆分，从根源上保证对应关系，同时避免不必要的笛卡尔积计算：

SELECT 
  * EXCEPT(tag_pair) 
  REPLACE(tag_pair.tag_id AS tag_id, tag_pair.tag_name AS tag_name)
FROM `your_table`,
UNNEST(
  TRANSFORM(
    SPLIT(tag_id, ', '),
    (val, idx) => STRUCT(
      val AS tag_id,
      SPLIT(tag_name, ', ')[OFFSET(idx)] AS tag_name
    )
  )
) AS tag_pair

注意事项 #

• 确保每行的tag_id和tag_name拆分后的数组长度一致，否则方案二中的OFFSET会触发越界错误。如果存在长度不一致的异常行，可以：
  1. 先过滤异常行：添加WHERE ARRAY_LENGTH(SPLIT(tag_id, ', ')) = ARRAY_LENGTH(SPLIT(tag_name, ', '))
  2. 用SAFE_OFFSET替代OFFSET，索引超出时返回NULL：SPLIT(tag_name, ', ')[SAFE_OFFSET(idx)]

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