按Gender分组统计所有列Null Rate的非硬编码SQL查询需求

由于表包含大量列，无法硬编码列名，需要通过动态生成SQL的方式实现，以下是主流数据库的具体方案：

SQL Server #

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 生成所有非分组列的空值率计算表达式
SELECT @cols = STRING_AGG(
    CONCAT('AVG(CASE WHEN [', COLUMN_NAME, '] IS NULL THEN 1.0 ELSE 0.0 END) AS [', COLUMN_NAME, ']'),
    ', '
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'People' AND COLUMN_NAME != 'Gender';

-- 拼接并执行完整查询
SET @sql = CONCAT(
    'SELECT Gender, ', @cols, '
     FROM People
     GROUP BY Gender;'
);

EXEC sp_executesql @sql;

MySQL #

-- 临时调整GROUP_CONCAT长度，避免列过多时截断
SET SESSION group_concat_max_len = 1000000;

-- 生成列计算表达式
SELECT GROUP_CONCAT(
    CONCAT('AVG(CASE WHEN `', column_name, '` IS NULL THEN 1.0 ELSE 0.0 END) AS `', column_name, '`')
    SEPARATOR ', '
) INTO @cols
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'People' AND column_name != 'Gender';

-- 拼接并执行查询
SET @sql = CONCAT(
    'SELECT Gender, ', @cols, '
     FROM People
     GROUP BY Gender;'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL #

WITH column_exprs AS (
    SELECT string_agg(
        format('AVG(CASE WHEN "%I" IS NULL THEN 1.0 ELSE 0.0 END) AS "%I"', column_name, column_name),
        ', '
    ) AS cols
    FROM information_schema.columns
    WHERE table_name = 'people' AND column_name != 'gender'
)
SELECT format(
    'SELECT gender, %s FROM people GROUP BY gender;',
    cols
) INTO @sql
FROM column_exprs;

EXECUTE @sql;

说明 #

• 空值率计算逻辑：每组内该列空值行数 ÷ 组内总行数，返回0-1的小数；若需百分比格式，将1.0替换为100.0即可
• 脚本会自动读取表中所有非Gender列，无需手动列写，适配任意列数的表
• 注意标识符大小写：不同数据库对表名、列名的大小写处理规则不同，按需调整引号（方括号/反引号/双引号）

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