如何从布尔列中获取True值数量最多的前5列列名？

核心思路 #

先统计每列中True值的数量，将列名与对应计数转为行数据后按计数降序排序，最后取前5列并拼接成逗号分隔的结果。

实现SQL（以PostgreSQL为例） #

SELECT STRING_AGG(col_name, ', ' ORDER BY true_count DESC) AS top_columns
FROM (
    -- 统计每列的True数量
    SELECT 'column1' AS col_name, SUM(CASE WHEN column1 THEN 1 ELSE 0 END) AS true_count FROM test
    UNION ALL
    SELECT 'column2' AS col_name, SUM(CASE WHEN column2 THEN 1 ELSE 0 END) AS true_count FROM test
    UNION ALL
    SELECT 'column3' AS col_name, SUM(CASE WHEN column3 THEN 1 ELSE 0 END) AS true_count FROM test
) AS column_counts
ORDER BY true_count DESC
LIMIT 5;

逻辑说明 #

1. 子查询统计：通过UNION ALL将每列的统计结果转为行记录，用CASE WHEN把布尔值转换为1（True）或0（False），再用SUM计算每列True的总数量。
2. 排序与拼接：外层用STRING_AGG函数按True数量降序拼接列名，LIMIT 5确保只取数量最多的前5列。

简化版本（MySQL适用） #

MySQL中布尔值以tinyint存储（True=1，False=0），可直接用SUM统计：

SELECT GROUP_CONCAT(col_name ORDER BY true_count DESC SEPARATOR ', ') AS top_columns
FROM (
    SELECT 'column1' AS col_name, SUM(column1) AS true_count FROM test
    UNION ALL
    SELECT 'column2' AS col_name, SUM(column2) AS true_count FROM test
    UNION ALL
    SELECT 'column3' AS col_name, SUM(column3) AS true_count FROM test
) AS column_counts
ORDER BY true_count DESC
LIMIT 5;

执行上述SQL后，会得到符合期望的结果：column1, column3, column2

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