如何一次性遍历数据表所有列并输出各列CountDistinct统计值

逐列手写统计语句效率太低，直接用对应数据库的动态SQL方案，替换表名即可一次性输出所有列的去重计数结果，输出格式为单行多列，可直接复制到文档使用。

核心逻辑 #

• 从数据库内置的元数据表中读取目标表的所有字段名
• 自动拼接每个字段对应的COUNT(DISTINCT 字段名)统计语句片段
• 拼接为完整的查询SQL后执行，直接返回全字段统计结果

各数据库可直接复用代码 #

MySQL #

-- 替换下方两个变量为实际的库名、表名
SET @target_schema = '你的数据库名';
SET @target_table = '你的目标表名';
SET @stat_sql = NULL;

SELECT GROUP_CONCAT(
    CONCAT('COUNT(DISTINCT `', COLUMN_NAME, '`) AS `', COLUMN_NAME, '_distinct_count`')
    SEPARATOR ', '
) INTO @stat_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @target_schema
  AND TABLE_NAME = @target_table;

SET @stat_sql = CONCAT('SELECT ', @stat_sql, ' FROM `', @target_schema, '`.`', @target_table, '`');

PREPARE stmt FROM @stat_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL #

-- 替换下方两个变量为实际的schema名、表名，默认schema为public
DO $$
DECLARE
    target_schema text := 'public';
    target_table text := '你的目标表名';
    stat_sql text;
BEGIN
    SELECT 'SELECT ' || STRING_AGG(
        'COUNT(DISTINCT "' || column_name || '") AS "' || column_name || '_distinct_count"',
        ', '
    ) || ' FROM "' || target_schema || '"."' || target_table || '"'
    INTO stat_sql
    FROM information_schema.columns
    WHERE table_schema = target_schema
      AND table_name = target_table;

    EXECUTE 'CREATE TEMP TABLE tmp_distinct_stat AS ' || stat_sql;
END $$;

SELECT * FROM tmp_distinct_stat;

SQL Server #

-- 替换下方两个变量为实际的schema名、表名，默认schema为dbo
DECLARE @target_schema NVARCHAR(100) = 'dbo'
DECLARE @target_table NVARCHAR(100) = '你的目标表名'
DECLARE @stat_sql NVARCHAR(MAX)

SELECT @stat_sql = STRING_AGG(
    'COUNT(DISTINCT [' + COLUMN_NAME + ']) AS [' + COLUMN_NAME + '_distinct_count]',
    ', '
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @target_schema
  AND TABLE_NAME = @target_table

SET @stat_sql = 'SELECT ' + @stat_sql + ' FROM [' + @target_schema + '].[' + @target_table + ']'
EXEC sp_executesql @stat_sql

注意：如果目标表数据量在千万级以上，全字段去重计数会占用较多计算资源，建议在业务低峰期执行，避免影响正常业务读写。

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