表所选定列枚举值与数量

Question

问题：做某张表的部分数据列的枚举值分布情况，以及分布数量的统计

示例：像类似这个数据表test，共有四个列

create table test(
c1 varchar(10),
c2 varchar(10),
c3 varchar(10),
c4 varchar(10)
);
insert into test values ('a1','b1','c1','d1');
insert into test values ('a1','b1','c1','d2');
insert into test values ('a1','b3','c2','d3');

---

目标：怎么编写SQL，得到第2、3列的 “列名称”、 “出现枚举值” 、“枚举值出现的次数”，如这样：

列名称    出现枚举值    枚举值出现的次数
c2            b1                  2
c2            b3                  1
c3            c1                  2
c3            c2                  1

这个SQL怎么写，请指教感谢了

Answer 1

用一次全表扫描后利用CASE语句进行计数。

WITH Data_CTE AS (
    SELECT 
        'c2' AS col_name,
        c2 AS col_value,
        COUNT(*) AS cnt
    FROM test
    GROUP BY c2

    UNION ALL

    SELECT 
        'c3' AS col_name,
        c3 AS col_value,
        COUNT(*) AS cnt
    FROM test
    GROUP BY c3
)

SELECT col_name, col_value, SUM(cnt) AS cnt
FROM Data_CTE
GROUP BY col_name, col_value
ORDER BY col_name, cnt DESC;

但这个SQL仍然对每个列进行了一次全表扫描。对于你所说的大表，这可能不是最优的方法。

考虑到这点，可以在一次查询中解决：

SELECT 
    CASE WHEN grouping(c2) = 1 THEN 'c3' ELSE 'c2' END AS col_name,
    COALESCE(c2, c3) AS col_value,
    COUNT(*) AS cnt
FROM test
GROUP BY grouping sets ((c2), (c3))
ORDER BY col_name, cnt DESC;

用到了GROUPING SETS，它允许在一个GROUP BY子句中进行多个分组操作。GROUPING函数则用来确定当前是哪个分组。当你有更多列时，可以继续扩展此方法。但是如果你要对46个列进行此类分析，建议分批进行，不要试图一次性处理所有列。