Snowflake SQL关联3表时避免重复值的解决方案

问题场景 #

在Snowflake SQL中基于Color列关联3张表时，因数据并非一对一映射，导致LISTAGG聚合后出现大量重复值。且不能直接使用DISTINCT——不同水果可能成本相同，去重会丢失合法的多条记录。

表结构 #

Main表

Fruits表

Vegetable表

原查询语句 #

select 
 A.ID,
 A.Color,
 LISTAGG(B."Column B",', ') "All Fruits",
 LISTAGG(B."Cost",', ') "Fruits Cost",
 LISTAGG(C."Column C",', ') "All Vegetables"
FROM
 "Main" A
 INNER JOIN "Fruits" B ON A."Color" = B."Color"
 INNER JOIN "Vegetables" C ON A."Color" = C."Color"
GROUP BY
 A.ID,
 A.Color

当前错误输出 #

期望输出 #

解决方案 #

问题根源是多表直接关联时产生了笛卡尔积（比如Red对应的3条Fruits记录和2条Vegetable记录关联后，会生成6条中间数据），直接聚合就会重复。正确思路是先对子表按Color单独聚合，再和主表关联：

WITH agg_fruits AS (
    SELECT 
        Color,
        LISTAGG("Column B", ', ') AS "All Fruits",
        LISTAGG(Cost, ', ') AS "Fruits Cost"
    FROM Fruits
    GROUP BY Color
),
agg_vegetables AS (
    SELECT 
        Color,
        LISTAGG("Column C", ', ') AS "All Vegetables"
    FROM Vegetable
    GROUP BY Color
)
SELECT 
    A.ID,
    A.Color,
    F."All Fruits",
    F."Fruits Cost",
    V."All Vegetables"
FROM Main A
INNER JOIN agg_fruits F ON A.Color = F.Color
INNER JOIN agg_vegetables V ON A.Color = V.Color;

这种方式先分别得到Fruits和Vegetable按Color聚合后的唯一结果，再和Main表关联，既避免了重复值，又保留了成本相同的不同水果记录（比如Red的Strawberry和Cherry成本都是5.0，依然会被正常展示）。

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