如何在PieCloudDB中拆分逗号分隔的商品数据为多行

我有一张名为Goods的表，存储商品的id、batch（批次）和type（类型）信息，同一批次下多个id对应不同类型的商品被合并记录。

原表数据 #

期望结果 #

我需要将逗号分隔的数据拆分为多行，得到每个id与对应批次下所有type的组合：

尝试的错误SQL #

我试过以下SQL，但结果不符合预期：

SELECT 
    unnest(string_to_array(g.id, ',')) AS id,
    g.batch,
    unnest(string_to_array(g.type, ',')) AS type
FROM 
    Goods g
JOIN LATERAL unnest(string_to_array(g.id, ',')) AS t(id) ON true
ORDER BY
    g.id;

错误结果 #

解决方法 #

你之前的问题在于同时在SELECT子句中使用两个unnest会让它们并行展开，而不是生成笛卡尔积。要实现每个id对应所有type的组合，需要用两次LATERAL JOIN分别拆分id和type，再做交叉关联：

SELECT 
    split_id.id,
    g.batch,
    split_type.type
FROM 
    Goods g
-- 先拆分id列
JOIN LATERAL unnest(string_to_array(g.id, ',')) AS split_id(id) ON true
-- 再拆分type列，与拆分后的id做笛卡尔积
JOIN LATERAL unnest(string_to_array(g.type, ',')) AS split_type(type) ON true
ORDER BY 
    g.batch, split_id.id, split_type.type;

原理说明 #

1. 第一个LATERAL JOIN将每个行的id字符串拆分为单独的id行；
2. 第二个LATERAL JOIN针对每个原始行的type字符串拆分为单独的type行，并且与已经拆分的id行进行交叉连接，从而得到每个id对应所有type的组合；
3. 最后按批次、id、type排序，得到和预期一致的结果。

另外，针对type只有单个值的情况（比如batch=2的行），unnest只会生成一行，所以每个id都会对应这个唯一的type，符合需求。

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