使用SQL Server聚合布尔值表：现有查询优化需求咨询

问题背景 #

现有仅含0/1值的表tab：

需要生成统计矩阵，其中单元格(X,Y)的值为原表中X=1且Y=1的行数，目标结果如下：

当前使用的嵌套子查询写法在大表上性能较差，需要更高效简洁的实现方式。

原查询代码：

SELECT
    'a' AS ' ',  
    SUM(a) AS a, 
    (SELECT SUM(b) FROM tab WHERE a = 1) AS b, 
    (SELECT SUM(c) FROM tab WHERE a = 1) AS c 
FROM 
    tab

UNION

SELECT
    'b', 
    (SELECT SUM(a) FROM tab WHERE b = 1),
    SUM(b), 
    (SELECT SUM(c) FROM tab WHERE b = 1) 
FROM
    tab

UNION

SELECT
    'c', 
    (SELECT SUM(a) FROM tab WHERE c = 1), 
    (SELECT SUM(b) FROM tab WHERE c = 1),
    SUM(c) 
FROM
    tab

优化方案 #

通过一次扫描预计算所有统计值，再用条件聚合生成目标矩阵，性能远优于多次子查询：

方案1：使用CTE（支持的SQL方言如PostgreSQL、MySQL 8.0+等） #

WITH col_sums AS (
    SELECT
        SUM(a) AS sum_a,
        SUM(b) AS sum_b,
        SUM(c) AS sum_c,
        SUM(a * b) AS sum_ab,
        SUM(a * c) AS sum_ac,
        SUM(b * c) AS sum_bc
    FROM tab
)
SELECT
    'a' AS ' ',
    sum_a AS a,
    sum_ab AS b,
    sum_ac AS c
FROM col_sums
UNION ALL
SELECT
    'b' AS ' ',
    sum_ab AS a,
    sum_b AS b,
    sum_bc AS c
FROM col_sums
UNION ALL
SELECT
    'c' AS ' ',
    sum_ac AS a,
    sum_bc AS b,
    sum_c AS c
FROM col_sums;

方案2：兼容老版本SQL（无CTE支持） #

SELECT
    'a' AS ' ',
    sum_a AS a,
    sum_ab AS b,
    sum_ac AS c
FROM (
    SELECT
        SUM(a) AS sum_a,
        SUM(b) AS sum_b,
        SUM(c) AS sum_c,
        SUM(a * b) AS sum_ab,
        SUM(a * c) AS sum_ac,
        SUM(b * c) AS sum_bc
    FROM tab
) AS col_sums
UNION ALL
SELECT
    'b' AS ' ',
    sum_ab AS a,
    sum_b AS b,
    sum_bc AS c
FROM (
    SELECT
        SUM(a) AS sum_a,
        SUM(b) AS sum_b,
        SUM(c) AS sum_c,
        SUM(a * b) AS sum_ab,
        SUM(a * c) AS sum_ac,
        SUM(b * c) AS sum_bc
    FROM tab
) AS col_sums
UNION ALL
SELECT
    'c' AS ' ',
    sum_ac AS a,
    sum_bc AS b,
    sum_c AS c
FROM (
    SELECT
        SUM(a) AS sum_a,
        SUM(b) AS sum_b,
        SUM(c) AS sum_c,
        SUM(a * b) AS sum_ab,
        SUM(a * c) AS sum_ac,
        SUM(b * c) AS sum_bc
    FROM tab
) AS col_sums;

方案3：临时表优化（适合超大表） #

CREATE TEMPORARY TABLE col_sums AS
SELECT
    SUM(a) AS sum_a,
    SUM(b) AS sum_b,
    SUM(c) AS sum_c,
    SUM(a * b) AS sum_ab,
    SUM(a * c) AS sum_ac,
    SUM(b * c) AS sum_bc
FROM tab;

SELECT
    'a' AS ' ',
    sum_a AS a,
    sum_ab AS b,
    sum_ac AS c
FROM col_sums
UNION ALL
SELECT
    'b' AS ' ',
    sum_ab AS a,
    sum_b AS b,
    sum_bc AS c
FROM col_sums
UNION ALL
SELECT
    'c' AS ' ',
    sum_ac AS a,
    sum_bc AS b,
    sum_c AS c
FROM col_sums;

DROP TEMPORARY TABLE IF EXISTS col_sums;

原理与性能说明 #

1. 统计逻辑：利用0/1值的特性，a*b仅当a和b同时为1时结果为1，求和即可得到共现行数；SUM(a)直接得到a列值为1的总行数（矩阵对角线值）。
2. 性能提升：原查询需要扫描原表10次以上（每个子查询独立扫表），优化方案仅需扫描原表1次，大表场景下性能差异显著。

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