如何使用PIVOT函数构建大规模规则关联矩阵？

需求说明 #

• 现有超160万条ID数据，每条ID对应n个Rule的0/1取值
• 需要生成关联矩阵，统计任意两个Rule列取值相同的次数
• 已知需借助PIVOT函数实现，已有一段用于其他统计的基础SQL代码：

SELECT COUNT(*), SUM(Rule_1), SUM(Rule_2), SUM(Rule_3), SUM(Rule_4)...
FROM TABLE

实现方案 #

方案1：行转列+自连接+PIVOT（通用但性能一般） #

先将宽表转为长格式，通过自连接匹配所有Rule对，再用PIVOT转成矩阵：

SELECT *
FROM (
    -- 计算每对Rule的取值相同次数
    SELECT
        u1.rule_name AS rule_a,
        u2.rule_name AS rule_b,
        SUM(CASE WHEN u1.rule_val = u2.rule_val THEN 1 ELSE 0 END) AS match_count
    FROM (
        -- 把所有Rule列转为长格式（每个ID+Rule对应一行）
        SELECT ID, 'Rule_1' AS rule_name, Rule_1 AS rule_val FROM TABLE
        UNION ALL
        SELECT ID, 'Rule_2' AS rule_name, Rule_2 AS rule_val FROM TABLE
        UNION ALL
        SELECT ID, 'Rule_3' AS rule_name, Rule_3 AS rule_val FROM TABLE
        -- 需补充所有Rule列，比如Rule_4、Rule_5...
    ) AS u1
    -- 自连接获取所有Rule组合
    JOIN (
        SELECT ID, 'Rule_1' AS rule_name, Rule_1 AS rule_val FROM TABLE
        UNION ALL
        SELECT ID, 'Rule_2' AS rule_name, Rule_2 AS rule_val FROM TABLE
        UNION ALL
        SELECT ID, 'Rule_3' AS rule_name, Rule_3 AS rule_val FROM TABLE
        -- 同样补充所有Rule列
    ) AS u2 ON u1.ID = u2.ID
    GROUP BY u1.rule_name, u2.rule_name
) AS source_data
-- PIVOT转成矩阵格式
PIVOT (
    MAX(match_count) -- 每个Rule组合仅一行，MAX/SUM结果一致
    FOR rule_b IN ([Rule_1], [Rule_2], [Rule_3], [Rule_4]...) -- 列出所有Rule列名
) AS pivot_matrix

方案2：直接聚合计算（性能优化版） #

利用数学逻辑简化计算：两个Rule取值相同的次数 = 同时为1的次数 + 同时为0的次数，无需行转列和自连接，适合大数量级数据：

WITH rule_match_stats AS (
    SELECT
        -- 计算每对Rule的同时为1、同时为0的次数
        SUM(Rule_1 * Rule_2) AS both_1_1_2,
        SUM((1-Rule_1)*(1-Rule_2)) AS both_0_1_2,
        SUM(Rule_1 * Rule_3) AS both_1_1_3,
        SUM((1-Rule_1)*(1-Rule_3)) AS both_0_1_3,
        SUM(Rule_2 * Rule_3) AS both_1_2_3,
        SUM((1-Rule_2)*(1-Rule_3)) AS both_0_2_3,
        -- 补充所有Rule组合的计算，比如Rule_1&Rule_4、Rule_2&Rule_4等
        COUNT(*) AS total_rows -- 单个Rule与自身的匹配次数就是总数据量
    FROM TABLE
)
-- 构造矩阵行并通过PIVOT转列
SELECT *
FROM (
    SELECT 'Rule_1' AS rule_name, total_rows AS Rule_1, both_1_1_2+both_0_1_2 AS Rule_2, both_1_1_3+both_0_1_3 AS Rule_3 FROM rule_match_stats
    UNION ALL
    SELECT 'Rule_2' AS rule_name, both_1_1_2+both_0_1_2 AS Rule_1, total_rows AS Rule_2, both_1_2_3+both_0_2_3 AS Rule_3 FROM rule_match_stats
    UNION ALL
    SELECT 'Rule_3' AS rule_name, both_1_1_3+both_0_1_3 AS Rule_1, both_1_2_3+both_0_2_3 AS Rule_2, total_rows AS Rule_3 FROM rule_match_stats
    -- 为每个Rule生成一行数据
) AS matrix_source
PIVOT (
    MAX(Rule_1)
    FOR rule_name IN ([Rule_1], [Rule_2], [Rule_3])
) AS final_matrix

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