编写Hive查询语句统计各字段缺失值（Null值）数量

要统计Hive数据表中每个字段的Null值数量，你可以通过CASE WHEN结合聚合函数SUM实现，核心逻辑是对每个字段单独判断是否为Null，符合条件则计数1，否则0，最后求和得到该字段的缺失值总数。

通用查询模板 #

假设你的数据表名为your_table，包含字段col1, col2, col3, ..., coln，对应的查询语句如下：

SELECT
  SUM(CASE WHEN col1 IS NULL THEN 1 ELSE 0 END) AS col1_null_count,
  SUM(CASE WHEN col2 IS NULL THEN 1 ELSE 0 END) AS col2_null_count,
  SUM(CASE WHEN col3 IS NULL THEN 1 ELSE 0 END) AS col3_null_count,
  -- 依次添加其他字段的统计逻辑
  SUM(CASE WHEN coln IS NULL THEN 1 ELSE 0 END) AS coln_null_count
FROM your_table;

实际示例 #

比如有一张用户信息表user_info，字段包括user_id, username, age, email，统计各字段缺失值的查询如下：

SELECT
  SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) AS user_id_null_count,
  SUM(CASE WHEN username IS NULL THEN 1 ELSE 0 END) AS username_null_count,
  SUM(CASE WHEN age IS NULL THEN 1 ELSE 0 END) AS age_null_count,
  SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS email_null_count
FROM user_info;

扩展：同时统计缺失率 #

如果需要同时查看每个字段的缺失值占总记录数的比例，可以额外计算总行数后做除法，用ROUND函数控制小数位数提升可读性：

SELECT
  COUNT(*) AS total_rows,
  SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) AS user_id_null_count,
  ROUND(SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) AS user_id_null_rate,
  SUM(CASE WHEN username IS NULL THEN 1 ELSE 0 END) AS username_null_count,
  ROUND(SUM(CASE WHEN username IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) AS username_null_rate,
  SUM(CASE WHEN age IS NULL THEN 1 ELSE 0 END) AS age_null_count,
  ROUND(SUM(CASE WHEN age IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) AS age_null_rate,
  SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS email_null_count,
  ROUND(SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) / COUNT(*), 4) AS email_null_rate
FROM user_info;

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