统计多列0/1值数量并转成行的高效SQL实现方案

问题根因 #

原有逐列聚合+UNION ALL的写法会为每个列单独触发一次全表扫描，200余列相当于对同一张表重复扫描200余次，计算和IO资源消耗随列数线性增长，很容易触发资源超限。
由于所有列取值只有0和1，sum(列名)本身就等于该列1值的总数，0值总数直接用表总记录数减去1值总数即可，不需要额外用CASE WHEN统计，能直接砍掉一半计算量。

核心优化思路：仅做1次全表扫描计算所有列的聚合结果，再通过列转行逻辑把宽表转成需要的行式输出格式，从根源上避免重复扫表。

不同数据库适配写法 #

支持UNPIVOT语法的数据库（Oracle、SQL Server、Spark SQL、Presto/Trino、Snowflake等） #

这是最通用的高效写法，全程仅扫描1次原表：

SELECT
    columns,
    one_values,
    total_cnt - one_values AS zero_value
FROM (
    -- 内层单次扫描完成所有列的聚合计算
    SELECT
        SUM(col1) AS col1,
        SUM(col2) AS col2,
        SUM(col3) AS col3,
        -- 其余列按相同格式补充即可
        COUNT(*) AS total_cnt
    FROM t
) AS agg_result
UNPIVOT (
    one_values FOR columns IN (
        col1, col2, col3 -- 此处填入所有需要统计的列名，和内层SUM的列一一对应
    )
) AS unpivot_result;

200余列不需要手写所有列名，可以直接查询数据库的系统表（比如Oracle的USER_TAB_COLUMNS、SQL Server的INFORMATION_SCHEMA.COLUMNS）批量导出列名拼接即可，1分钟就能拼完所有列。

MySQL 8.0+ 写法 #

MySQL 不支持标准UNPIVOT语法，可以用JSON_TABLE实现列转行，同样只扫一次表：

SELECT
    j.columns,
    j.one_values,
    agg.total_cnt - j.one_values AS zero_value
FROM (
    SELECT
        SUM(col1) AS col1,
        SUM(col2) AS col2,
        SUM(col3) AS col3,
        -- 其余列按相同格式补充
        COUNT(*) AS total_cnt
    FROM t
) AS agg,
JSON_TABLE(
    JSON_OBJECT(
        'col1', agg.col1,
        'col2', agg.col2,
        'col3', agg.col3
        -- 其余列按相同格式补充
    ),
    '$.*' COLUMNS (
        columns VARCHAR(100) PATH '$key',
        one_values INT PATH '$value'
    )
) AS j;

Hive/Spark 等大数据引擎写法 #

数仓场景可以直接用STACK函数做列转行，写法更简洁：

SELECT
    columns,
    one_values,
    total_cnt - one_values AS zero_value
FROM (
    SELECT
        SUM(col1) AS col1,
        SUM(col2) AS col2,
        SUM(col3) AS col3,
        -- 其余列按相同格式补充
        COUNT(*) AS total_cnt
    FROM t
) AS agg
LATERAL VIEW STACK(
    3, -- 此处填需要统计的列总数量，200列就填200
    'col1', col1,
    'col2', col2,
    'col3', col3
    -- 其余列按'列名', 列别名的格式补充
) s AS columns, one_values;

性能对比 #

• 原UNION ALL写法：200列需要扫描200次表，资源消耗是最优写法的200倍以上
• 上述优化写法：不管多少列都只扫描1次原表，200列场景下查询速度可以提升100~300倍，完全不会触发资源超限。

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