Snowflake中实现类似Python itertools.combinations()的JSON组合方案

针对你提出的需求，推荐使用Snowflake内置的COMBINATION函数实现，无需多次JOIN，扩展性极强，只需修改参数即可适配不同的k值。

实现步骤 #

1. 提取JSON数组中的key1值：通过FLATTEN展开JSON数组，提取每个元素的key1值。
2. 聚合每行的key1值为数组：按原始行分组，将该行所有key1值聚合为一个数组。
3. 生成指定k大小的组合：使用COMBINATION函数直接生成数组的k元素无序组合，再通过FLATTEN展开组合结果。
4. 聚合组合结果为最终数组：将同一行的所有组合聚合为数组，与原始行对应。

完整SQL示例 #

-- 模拟测试数据
WITH test_data AS (
    SELECT PARSE_JSON('[{"key1": 1}, {"key1": 2}, {"key1": 3}, {"key1": 4}]') AS col1
    UNION ALL
    SELECT PARSE_JSON('[{"key1": 12}, {"key1": 22}, {"key1": 33}, {"key1": 44}]') AS col1
),
-- 为每行生成唯一标识（若表有主键可直接用主键替代）
row_marker AS (
    SELECT col1, ROW_NUMBER() OVER () AS row_id
    FROM test_data
),
-- 展开JSON数组并提取key1的值
flattened_keys AS (
    SELECT 
        row_id,
        value:key1::INT AS key_value
    FROM row_marker,
    LATERAL FLATTEN(input => col1)
),
-- 聚合每行的key值为数组，并生成k大小的组合
combination_gen AS (
    SELECT 
        row_id,
        combo
    FROM (
        SELECT 
            row_id,
            -- 若key1存在重复值，可添加DISTINCT去重：ARRAY_AGG(DISTINCT key_value)
            ARRAY_AGG(key_value) AS key_array
        FROM flattened_keys
        GROUP BY row_id
    ),
    -- 修改第二个参数即可切换k值（2/3/4）
    LATERAL FLATTEN(input => COMBINATION(key_array, 2))
)
-- 关联原始数据，聚合组合为最终结果数组
SELECT 
    td.col1,
    ARRAY_AGG(combo) AS result
FROM test_data td
JOIN row_marker rm ON td.col1 = rm.col1
JOIN combination_gen cg ON rm.row_id = cg.row_id
GROUP BY td.col1;

关键优势 #

• 扩展性强：只需修改COMBINATION函数的第二个参数，即可生成任意k大小的组合，无需调整JOIN逻辑。
• 性能更优：避免了多次JOIN带来的性能损耗，利用内置函数直接处理组合生成。
• 逻辑清晰：步骤模块化，易于理解和维护。

适配不同k值示例 #

• 当k=3时，将COMBINATION(key_array, 2)改为COMBINATION(key_array, 3)，即可生成3元素组合。
• 当k=4时，改为COMBINATION(key_array, 4)，自动生成唯一的4元素组合。

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