Snowflake中拆分多列数据并保持值对应关系的实现方案

你的问题核心是要拆分B、C列的逗号分隔值，同时保持同一行中B和C的元素位置对应，避免产生笛卡尔积。之前的语句因为没有关联拆分后元素的位置标识，所以生成了所有元素的组合。

正确解法1：利用split_to_table的INDEX字段 #

split_to_table函数返回的结果集中自带INDEX字段，代表拆分后元素在原字符串中的位置（从1开始计数）。通过关联该字段，就能保证B和C列的对应元素匹配：

SELECT 
    t.A,
    b.value AS B,
    c.value AS C
FROM TEST t
LATERAL split_to_table(t.B, ',') b
JOIN LATERAL split_to_table(t.C, ',') c
    ON b.index = c.index;

正确解法2：split_to_array+flatten带序号 #

先将字符串转为数组，再用flatten的WITHORDINALITY参数获取元素序号，通过序号关联对应元素：

SELECT
    t.A,
    f_b.value::VARCHAR AS B,
    f_c.value::VARCHAR AS C
FROM TEST t,
LATERAL flatten(input => split_to_array(t.B, ','), outer => true, WITHORDINALITY) f_b,
LATERAL flatten(input => split_to_array(t.C, ','), outer => true, WITHORDINALITY) f_c
WHERE f_b.ordinality = f_c.ordinality;

原理说明 #

两种方法都是通过元素的位置序号来关联拆分后的B、C列结果，确保同一位置的元素被配对，避免了无关联导致的笛卡尔积。如果B、C列的分隔元素数量不一致，outer => true可以保留所有元素（数量少的列会补NULL），不需要的话可以去掉该参数。

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