BigQuery中拆分字段后ID未重复的问题排查与正确实现

原表（TABLE A） #

期望输出 #

错误的尝试SQL #

WITH T_LIST AS (
select id, status, split(status_code, ',') as code_array
from TABLE A 
where status is not null
and status = 'ABS'
)
SELECT TL.id, TL.code_array
FROM T_LIST TL 
CROSS JOIN UNNEST(TL.code_array) as INFO

错误结果 #

正确解法 #

问题出在SELECT了原数组字段TL.code_array，而非UNNEST展开后的列，导致数组元素和主表行的关联逻辑异常，出现ID为空的情况。

通用正确写法（以BigQuery为例） #

WITH T_LIST AS (
    SELECT id, status, SPLIT(status_code, ',') AS code_array
    FROM `TABLE A` 
    WHERE status IS NOT NULL
      AND status = 'ABS'
)
SELECT TL.id, INFO AS `STATUS Code`
FROM T_LIST TL 
CROSS JOIN UNNEST(TL.code_array) AS INFO

其他SQL方言适配（比如PostgreSQL） #

如果使用PostgreSQL，拆分字符串的函数为STRING_TO_ARRAY，写法调整为：

WITH T_LIST AS (
    SELECT id, status, STRING_TO_ARRAY(status_code, ',') AS code_array
    FROM "TABLE A" 
    WHERE status IS NOT NULL
      AND status = 'ABS'
)
SELECT TL.id, INFO AS "STATUS Code"
FROM T_LIST TL 
CROSS JOIN UNNEST(TL.code_array) AS INFO

核心逻辑：通过CROSS JOIN UNNEST展开数组时，必须引用UNNEST生成的别名列（示例中为INFO），而非原数组字段，这样才能保证每个展开后的元素都正确关联到对应的主表ID。

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