如何高效统计数据表各列的唯一值及其出现行数？

需求说明 #

需要对customer表进行数据探索，统计每一列的每个唯一字符串对应的行数，期望输出格式如下：

+------------+-------------+------------+----------+
| table_name | column_name | distinct   | count(*) |
|            |             | row_string |          |
+------------+-------------+------------+----------+
| customer   | state       | WA         | 15       |
+------------+-------------+------------+----------+
| customer   | state       | NSW        | 786      |
+------------+-------------+------------+----------+
| ...        | ...         | ...        | ...      |
+------------+-------------+------------+----------+
| customer   | zip_code    | 3563       | 33       |
+------------+-------------+------------+----------+
| ...        | ...         | ...        | ...      |
+------------+-------------+------------+----------+

原实现方式为对每个列单独分组统计后用UNION拼接：

select state, count(*)
from customer
group by state
union
select zip_code, count(*)
from customer
group by zip_code
union
...

但列数较多时，该方法会多次全表扫描，效率极低。

高效实现方案 #

核心思路是先将表的列转置为行（Unpivot），再一次性分组统计，全程仅需扫描表一次，大幅降低IO开销。以下是不同数据库的具体实现：

1. 支持UNPIVOT语法的数据库（SQL Server、Oracle等） #

直接使用原生UNPIVOT语法转置列：

SELECT 
    'customer' AS table_name,
    column_name,
    row_string AS distinct_row_string,
    COUNT(*) AS count
FROM customer
UNPIVOT (
    row_string FOR column_name IN (state, zip_code, col1, col2) -- 列出所有需要统计的列
) AS unpivoted
GROUP BY column_name, row_string
ORDER BY column_name, count DESC;

2. PostgreSQL #

方法一：LATERAL 拼接行（适合列数较少时）

SELECT 
    'customer' AS table_name,
    column_name,
    row_string AS distinct_row_string,
    COUNT(*) AS count
FROM customer,
LATERAL (
    VALUES 
        ('state', state::text),
        ('zip_code', zip_code::text),
        ('col1', col1::text)
) AS unpivoted(column_name, row_string)
WHERE row_string IS NOT NULL -- 可选：过滤空值
GROUP BY column_name, row_string
ORDER BY column_name, count DESC;

方法二：JSONB 自动转置（适合列数较多时，无需手动列所有列）

SELECT 
    'customer' AS table_name,
    key AS column_name,
    value AS distinct_row_string,
    COUNT(*) AS count
FROM customer,
jsonb_each_text(to_jsonb(customer))
WHERE value IS NOT NULL -- 可选：过滤空值
GROUP BY key, value
ORDER BY key, count DESC;

3. MySQL（8.0+） #

方法一：WITH 子句转置后分组

WITH unpivoted AS (
    SELECT 'state' AS column_name, state AS row_string FROM customer
    UNION ALL
    SELECT 'zip_code' AS column_name, zip_code AS row_string FROM customer
    UNION ALL
    SELECT 'col1' AS column_name, col1 AS row_string FROM customer
    -- 继续添加其他需要统计的列
)
SELECT 
    'customer' AS table_name,
    column_name,
    row_string AS distinct_row_string,
    COUNT(*) AS count
FROM unpivoted
WHERE row_string IS NOT NULL
GROUP BY column_name, row_string
ORDER BY column_name, count DESC;

方法二：JSON_TABLE 自动转置

SELECT 
    'customer' AS table_name,
    j.column_name,
    j.row_string AS distinct_row_string,
    COUNT(*) AS count
FROM customer,
JSON_TABLE(
    JSON_OBJECT('state', state, 'zip_code', zip_code, 'col1', col1),
    '$.*' COLUMNS (
        column_name VARCHAR(255) PATH '$[key]',
        row_string VARCHAR(255) PATH '$[value]'
    )
) AS j
WHERE j.row_string IS NOT NULL
GROUP BY j.column_name, j.row_string
ORDER BY j.column_name, count DESC;

效率优势 #

原方法每个列的GROUP BY都会触发一次全表扫描，列数越多扫描次数越多；转置后仅需扫描表一次，后续分组统计仅基于转置后的数据集，IO开销大幅减少，大表场景下性能提升尤为明显。

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