在BigQuery中基于逗号分隔符展开多列时如何避免重复数据？

原查询语句 #

SELECT colm_A,colm_B, colm_C From Db.Table_A, UNNEST(Split(COLM_B,',')), COLM_B, UNNEST(SPLIT(COLM_C,',')) COLM_C;

原表数据 #

错误输出（出现重复） #

期望输出 #

问题原因 #

原查询对Colm_B和Colm_C的拆分结果做了无关联的交叉连接，产生笛卡尔积。当两个列都包含多个逗号分隔值时，会生成拆分后B的元素数 × 拆分后C的元素数行数据，其中有效对应关系的行被重复输出。

正确查询语句 #

通过WITH OFFSET获取拆分元素的位置索引，关联同一位置的元素，避免笛卡尔积：

SELECT
  colm_A,
  b AS colm_B,
  c AS colm_C
FROM Db.Table_A,
UNNEST(SPLIT(colm_B, ',')) WITH OFFSET AS b_offset
JOIN UNNEST(SPLIT(colm_C, ',')) WITH OFFSET AS c_offset
ON b_offset = c_offset

说明 #

WITH OFFSET会为每个拆分后的元素分配一个从0开始的位置索引，通过ON b_offset = c_offset确保Colm_B和Colm_C中同一位置的元素一一对应，既完成拆分又不会产生重复数据。

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