Redshift SQL统计类列表字符串列中的重复值对问题

核心思路 #

通过Redshift原生的数组拆分与聚合能力，替代冗余的CASE WHEN逻辑，实现可扩展的重复值对统计：

1. 将逗号分隔字符串拆分为独立元素行
2. 过滤掉NA值
3. 按行分组统计每个元素的出现次数
4. 用组合数公式n*(n-1)/2计算单个重复元素的配对数，最终求和得到每行的总重复对数量

示例SQL代码 #

假设你的表名为target_table，存储逗号列表的字段为string_list，每行的唯一标识为row_id：

WITH split_elements AS (
    -- 拆分字符串为独立元素，清理元素前后空格
    SELECT
        row_id,
        TRIM(item) AS clean_item
    FROM target_table
    CROSS JOIN UNNEST(split_to_array(string_list, ',')) AS t(item)
    -- 过滤NA值
    WHERE TRIM(item) <> 'NA'
),
item_counts AS (
    -- 统计每行内各元素的出现次数，仅保留重复出现的项
    SELECT
        row_id,
        clean_item,
        COUNT(*) AS occurrence
    FROM split_elements
    GROUP BY row_id, clean_item
    HAVING COUNT(*) > 1
)
-- 计算每行的总重复值对数量
SELECT
    row_id,
    SUM(occurrence * (occurrence - 1) / 2) AS total_duplicate_pairs
FROM item_counts
GROUP BY row_id
-- 补充无重复项的行，显示0
UNION ALL
SELECT
    row_id,
    0 AS total_duplicate_pairs
FROM target_table
WHERE row_id NOT IN (SELECT DISTINCT row_id FROM item_counts)
ORDER BY row_id;

方案优势 #

• 高扩展性：无论列表包含15个还是更多值，无需修改代码即可适配
• 代码简洁易维护：避免了大量重复的CASE WHEN逻辑，可读性更强
• 性能更优：利用Redshift原生的split_to_array和unnest函数，执行效率优于手工判断逻辑

注意事项 #

• 如果表没有唯一主键row_id，可以用ROW_NUMBER() OVER ()生成临时行标识，或基于所有其他字段分组
• 若数据中NA存在大小写变体（如na、Na），可将过滤条件改为LOWER(TRIM(item)) <> 'na'
• 若列表元素包含逗号（特殊场景），需提前处理转义逻辑，但当前问题未提及此情况，暂不考虑

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