如何将T-SQL中CROSS APPLY实现的列组合逻辑转为Snowflake SQL

问题说明 #

现有表Dataset包含State、Pens、Pencils、Papers四列，后三列取值为0或1。样例数据如下：

需要实现每个State对应所有值为1的列名组合（例如NY需返回Pens、Papers、Pens,Papers三条结果）。原T-SQL使用CROSS APPLY的方案在Snowflake中报错Unsupported subquery type cannot be evaluated，以下是几种等价实现方案：

方案一：枚举式LATERAL JOIN（适合列数较少场景） #

由于仅3个目标列，直接枚举所有非空组合的判断条件，通过CROSS JOIN LATERAL生成结果，简单直观且无兼容性问题：

SELECT 
    d.State,
    c.combination
FROM Dataset d
CROSS JOIN LATERAL (
    SELECT 'Pens' AS combination WHERE d.Pens = 1
    UNION ALL
    SELECT 'Pencils' WHERE d.Pencils = 1
    UNION ALL
    SELECT 'Papers' WHERE d.Papers = 1
    UNION ALL
    SELECT 'Pens,Pencils' WHERE d.Pens = 1 AND d.Pencils = 1
    UNION ALL
    SELECT 'Pens,Papers' WHERE d.Pens = 1 AND d.Papers = 1
    UNION ALL
    SELECT 'Pencils,Papers' WHERE d.Pencils = 1 AND d.Papers = 1
    UNION ALL
    SELECT 'Pens,Pencils,Papers' WHERE d.Pens = 1 AND d.Pencils = 1 AND d.Papers = 1
) c
ORDER BY d.State, LENGTH(c.combination), c.combination;

方案二：UNPIVOT + 递归CTE（适合列数较多场景） #

先将列转成行结构，再通过递归CTE生成所有不重复的组合，扩展性强，新增列时只需修改UNPIVOT部分：

WITH unpivoted AS (
    SELECT 
        State,
        Item
    FROM Dataset
    UNPIVOT (
        value FOR Item IN (Pens, Pencils, Papers)
    )
    WHERE value = 1
),
recursive_combinations AS (
    -- 基础项：单个列名
    SELECT 
        State,
        Item AS combination,
        Item AS last_item,
        1 AS combination_length
    FROM unpivoted
    UNION ALL
    -- 递归生成多列组合，通过last_item避免重复（如Pens,Papers和Papers,Pens视为同一组合）
    SELECT 
        rc.State,
        CONCAT(rc.combination, ',', u.Item) AS combination,
        u.Item AS last_item,
        rc.combination_length + 1 AS combination_length
    FROM recursive_combinations rc
    JOIN unpivoted u 
        ON rc.State = u.State 
        AND u.Item > rc.last_item
)
SELECT 
    State,
    combination
FROM recursive_combinations
ORDER BY State, combination_length, combination;

方案三：数组函数 + 表生成函数（兼顾扩展性与简洁性） #

利用Snowflake的数组收集有效列名，再通过位运算生成所有非空子集，自动适配列数变化：

WITH state_valid_items AS (
    SELECT 
        State,
        -- 收集当前State下值为1的列名，自动过滤空值
        ARRAY_CONSTRUCT_COMPACT(
            IFF(Pens=1, 'Pens', NULL),
            IFF(Pencils=1, 'Pencils', NULL),
            IFF(Papers=1, 'Papers', NULL)
        ) AS valid_items
    FROM Dataset
    WHERE ARRAY_SIZE(valid_items) > 0 -- 过滤无有效列的State
)
SELECT 
    s.State,
    ARRAY_TO_STRING(ARRAY_AGG(f.value) WITHIN GROUP (ORDER BY f.value), ',') AS combination
FROM state_valid_items s
-- 生成对应子集数量的行（2^N -1，排除空集）
JOIN TABLE(GENERATOR(ROWCOUNT => 8)) gen -- 8=2^3，对应3列的所有子集数
    ON gen.value > 0
-- 匹配当前子集对应的列名
JOIN TABLE(FLATTEN(s.valid_items)) f
    ON BITAND(gen.value, POWER(2, f.index)) > 0
GROUP BY s.State, gen.value
ORDER BY s.State, LENGTH(combination), combination;

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