SQL优化求助：计算role列唯一组合的同refid匹配次数

问题背景 #

需要计算role列唯一条目的所有两两组合，统计每个组合共享相同refid的次数。原方案通过存储过程先生成所有97032种组合再循环更新计数，运行效率极低，需更优解法。

数据模型 #

期望输出 #

最优解决方案 #

方案1：仅统计有匹配的组合 #

直接通过自连接+分组聚合完成，无需预生成所有组合，完全利用数据库的集合运算优化，避免循环开销：

SELECT 
    a.role AS a_role,
    b.role AS b_role,
    COUNT(DISTINCT a.refid) AS match_count
FROM 
    your_table a
JOIN 
    your_table b ON a.refid = b.refid AND a.role < b.role
GROUP BY 
    a.role, b.role
ORDER BY 
    match_count DESC;

• 核心逻辑：将表自连接，匹配同一refid下的不同role，通过a.role < b.role避免重复统计（如xxx-yyy和yyy-xxx视为同一组合），最后按组合分组统计不同refid的数量。

方案2：包含所有可能的组合（含计数为0的情况） #

如果需要展示所有97032种组合（即使没有共同refid的组合也要显示match_count=0），用CTE生成全组合后左连接统计结果：

WITH all_role_pairs AS (
    -- 生成role列所有唯一值的两两组合
    SELECT 
        r1.role AS a_role,
        r2.role AS b_role
    FROM 
        (SELECT DISTINCT role FROM your_table) r1
    JOIN 
        (SELECT DISTINCT role FROM your_table) r2 ON r1.role < r2.role
),
matched_counts AS (
    -- 统计实际有共同refid的组合计数
    SELECT 
        a.role AS a_role,
        b.role AS b_role,
        COUNT(DISTINCT a.refid) AS match_count
    FROM 
        your_table a
    JOIN 
        your_table b ON a.refid = b.refid AND a.role < b.role
    GROUP BY 
        a.role, b.role
)
-- 合并所有组合，无匹配的计数补0
SELECT 
    arp.a_role,
    arp.b_role,
    COALESCE(mc.match_count, 0) AS match_count
FROM 
    all_role_pairs arp
LEFT JOIN 
    matched_counts mc ON arp.a_role = mc.a_role AND arp.b_role = mc.b_role
ORDER BY 
    arp.a_role, arp.b_role;

方案优势 #

• 完全摒弃逐行循环的低效逻辑，利用数据库原生的集合运算和优化器，执行效率远高于存储过程循环。
• 代码简洁易维护，无需复杂的存储过程逻辑。

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