如何编写SQL统计百万行数据表各列的缺失数据占比

计算前置说明 #

统一两个基础规则，避免结果不符合预期：

• 缺失值默认判定标准为字段值为NULL的行；如果业务中将空字符串、自定义占位值（如-1、「未知」）也归类为缺失，可直接调整判断条件
• 适配百万行表的性能要求：所有列的缺失统计在单次全表扫描中完成，不做重复扫表
• 规避整数除法陷阱：如果遇到除法结果被截断为整数的情况，可将缺失值计数乘1.0转为浮点后再做除法，例如a_miss * 1.0 / total_rows

通用兼容写法（支持MySQL/PostgreSQL/Hive/Spark SQL等主流引擎） #

WITH total_stat AS (
    SELECT
        -- 单次扫表完成所有列的缺失行数统计
        COUNT(IF(A IS NULL, 1, NULL)) AS a_miss,
        COUNT(IF(B IS NULL, 1, NULL)) AS b_miss,
        COUNT(IF(C IS NULL, 1, NULL)) AS c_miss,
        COUNT(IF(D IS NULL, 1, NULL)) AS d_miss,
        COUNT(*) AS total_rows
    FROM your_table_name -- 替换为实际待统计的表名
)
-- 按要求拼接为「列名;缺失百分比」的结构化格式
SELECT CONCAT_WS(';', CONCAT(column_name, '列'), ROUND(miss_ratio, 2)) AS miss_stat
FROM (
    SELECT 'A' AS column_name, a_miss / total_rows AS miss_ratio FROM total_stat
    UNION ALL
    SELECT 'B' AS column_name, b_miss / total_rows AS miss_ratio FROM total_stat
    UNION ALL
    SELECT 'C' AS column_name, c_miss / total_rows AS miss_ratio FROM total_stat
    UNION ALL
    SELECT 'D' AS column_name, d_miss / total_rows AS miss_ratio FROM total_stat
) t
ORDER BY column_name;

写法调整说明 #

• 缺失规则调整：如果需要将空字符串计入缺失，将空值判断条件替换为IF(A IS NULL OR TRIM(A) = '', 1, NULL)即可
• 兼容性调整：如果遇到不支持IF()函数的老旧SQL引擎，将判断逻辑替换为CASE WHEN A IS NULL THEN 1 END即可
• 精度调整：ROUND(miss_ratio, 2)中的数字2为保留的小数位数，可根据需求调整
• 输出格式调整：当前拼接的分隔符为分号，若需调整可修改CONCAT_WS的第一个参数
• 执行效果：最终输出的行格式与需求完全匹配，示例输出为A列;0.05、B列;0.30、C列;0.40、D列;0.11

百万级大表执行优化提示 #

• 上述写法仅需要对全表做1次扫描，百万行规模下执行耗时通常为秒级，远低于逐列查询的写法（逐列查询每列做一次全表扫描，4列就需要扫描4次，耗时翻数倍）
• 如果表存在业务分区过滤规则，可在CTE的表查询部分补充WHERE条件，进一步减少需要扫描的数据量
• 不需要额外叠加全表排序、无意义的关联逻辑，避免额外的资源消耗

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