Snowflake+DBT：解决Select非关联子查询报错及产品名称转换

你遇到的Unsupported subquery type cannot be evaluated报错，是因为Snowflake不支持在IN子查询中嵌套使用SPLIT_TO_TABLE这类表函数。正确的做法是先拆分产品ID列表为行记录，关联产品表获取名称后再聚合，以下是可行的SQL写法：

写法一：使用CTE拆分聚合 #

WITH split_user_products AS (
    SELECT
        ua.date,
        ua.user_id,
        u.name,
        ua.product_list,
        p.product_name
    FROM user_attributes ua
    LEFT JOIN users u ON ua.user_id = u.user_id
    -- 拆分逗号分隔的产品ID为单行记录
    LEFT JOIN TABLE(SPLIT_TO_TABLE(ua.product_list, ',')) AS split_prod
    -- 关联产品表匹配名称
    LEFT JOIN products p ON TRIM(split_prod.value) = p.product_id
)
SELECT
    date,
    user_id,
    name,
    product_list,
    -- 将产品名称聚合为逗号分隔的列表
    LISTAGG(product_name, ',') WITHIN GROUP (ORDER BY product_name) AS product_names
FROM split_user_products
GROUP BY date, user_id, name, product_list;

写法二：直接关联拆分表（更简洁） #

SELECT
    ua.date,
    ua.user_id,
    u.name,
    ua.product_list,
    LISTAGG(p.product_name, ',') WITHIN GROUP (ORDER BY p.product_name) AS product_names
FROM user_attributes ua
LEFT JOIN users u ON ua.user_id = u.user_id
LEFT JOIN TABLE(SPLIT_TO_TABLE(ua.product_list, ',')) AS split_prod
LEFT JOIN products p ON TRIM(split_prod.value) = p.product_id
GROUP BY ua.date, ua.user_id, u.name, ua.product_list;

注意事项 #

如果product_list中的产品ID包含空格，需要用TRIM(split_prod.value)去除前后空格后再和products.product_id匹配，避免出现匹配失败的情况。

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