如何在Amazon Redshift中生成列值任意长度组合并统计去重计数

原始数据表 #

+------+--------+
| Type | Name   |
+------+--------+
| A    | Tom    |
| A    | Ben    |
| B    | Ben    |
| B    | Justin |
| C    | Ben    |
+------+--------+

需求说明 #

生成Type列的所有非空任意长度组合，并统计每个组合对应的Name列去重数量，预期输出如下：

+-------------+-------+
| Combination | Count |
+-------------+-------+
| A           | 2     |
| B           | 2     |
| C           | 1     |
| AB          | 3     |
| BC          | 2     |
| AC          | 2     |
| ABC         | 3     |
+-------------+-------+

Redshift具体实现SQL #

WITH unique_types AS (
    -- 提取所有唯一Type并分配行号，用于递归时避免重复组合
    SELECT DISTINCT Type, ROW_NUMBER() OVER () AS rn
    FROM your_table_name  -- 替换为实际表名
),
recursive_combinations AS (
    -- 递归起始：单个Type的组合
    SELECT Type AS combination, rn, Type AS single_type
    FROM unique_types
    UNION ALL
    -- 递归生成更长的组合：仅与行号更大的Type拼接，确保组合唯一有序
    SELECT rc.combination || ut.Type, ut.rn, ut.Type
    FROM recursive_combinations rc
    JOIN unique_types ut ON ut.rn > rc.rn
)
-- 统计每个组合对应的唯一Name数量
SELECT 
    combination,
    COUNT(DISTINCT t.Name) AS Count
FROM recursive_combinations rc
JOIN your_table_name t ON t.Type = rc.single_type
GROUP BY combination
ORDER BY LENGTH(combination), combination;

代码解释 #

1. unique_types CTE：先获取表中所有不重复的Type值，同时给每个Type分配一个递增的行号。这个行号是关键，确保后续递归时只会和行号更大的Type拼接，避免生成重复组合（比如不会同时出现AB和BA）。
2. recursive_combinations CTE：通过递归生成所有非空组合：
   • 起始分支：先生成所有单个Type的组合（如A、B、C）。
   • 递归分支：将已有的组合和后续的Type拼接，生成更长的组合（比如用A和B生成AB，用AB和C生成ABC等）。
3. 最终统计：将每个组合拆分为对应的单个Type，关联原表获取所有匹配的Name，然后按组合分组，统计去重后的Name数量，最后按组合长度和组合本身排序，得到预期结果。

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