在Presto SQL中动态生成数组值的所有组合元组

问题场景 #

现有如下Presto数据集：

WITH A (name, distinct_values) AS (
  VALUES 
    ('color', ARRAY['red', 'yellow']),
    ('shape', ARRAY['triangle', 'square', 'circle']),
    ('size', ARRAY['big', 'small'])
)
select * from A

数据集展示：

需要动态生成所有属性（name的取值）对应distinct_values的笛卡尔积组合，每行输出一个包含所有属性键值对的Map，示例输出：

tuples
{'color': 'red', 'shape': 'triangle', 'size': 'big'}
{'color': 'red', 'shape': 'triangle', 'size': 'small'}
...
{'color': 'yellow', 'shape': 'circle', 'size': 'small'}

注：无法提前知晓name的数量或distinct_values的元素数量。

解决方案 #

可以通过递归CTE结合Map函数实现动态笛卡尔积组合，具体SQL如下：

WITH A (name, distinct_values) AS (
  VALUES 
    ('color', ARRAY['red', 'yellow']),
    ('shape', ARRAY['triangle', 'square', 'circle']),
    ('size', ARRAY['big', 'small'])
),
-- 拆分所有属性的键值对
key_values AS (
  SELECT name, value
  FROM A
  CROSS JOIN UNNEST(distinct_values) AS t(value)
),
-- 递归构建笛卡尔积与组合Map
recursive_combinations AS (
  -- 初始步骤：取第一个属性的所有键值对生成初始Map
  SELECT 
    ARRAY[name] AS used_keys,
    MAP(ARRAY[name], ARRAY[value]) AS result_map
  FROM key_values
  WHERE name = (SELECT MIN(name) FROM A)
  
  UNION ALL
  
  -- 递归合并后续属性的键值对
  SELECT 
    rc.used_keys || kv.name,
    MAP_CONCAT(rc.result_map, MAP(ARRAY[kv.name], ARRAY[kv.value]))
  FROM recursive_combinations rc
  CROSS JOIN key_values kv
  WHERE kv.name NOT IN UNNEST(rc.used_keys)
    -- 确保每次仅加入下一个未使用的属性，避免重复组合
    AND kv.name = (
      SELECT MIN(name) 
      FROM A 
      WHERE name NOT IN UNNEST(rc.used_keys)
    )
)
-- 筛选包含所有属性的最终组合
SELECT result_map AS tuples
FROM recursive_combinations
WHERE CARDINALITY(used_keys) = (SELECT COUNT(DISTINCT name) FROM A);

逻辑说明 #

1. key_values阶段：将每个属性的数组拆分为单独的键值对行，为后续笛卡尔积做准备。
2. 递归组合阶段：
   • 初始步骤选取第一个属性（按name排序后的最小值）的所有键值对，生成仅包含该属性的Map。
   • 递归步骤将未使用的下一个属性的所有键值对，与已生成的Map做笛卡尔积，通过MAP_CONCAT合并为包含更多属性的新Map。
3. 最终筛选：只保留包含所有属性的Map（通过判断used_keys的长度等于总属性数实现），得到所有可能的组合。

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