如何在HIVE中统计各属性缺失值并展示占比超50%的属性

实现步骤 #

要完成需求，需分两步执行：先统计每列的缺失值数量、总记录数及缺失占比，再将列级统计结果转为行格式，以便筛选占比超过50%的列。

完整SQL代码 #

WITH total_records AS (
    -- 先计算表的总记录数，避免重复计算浪费资源
    SELECT COUNT(*) AS total_rows FROM your_table_name
),
column_metrics AS (
    -- 统计每列的缺失值数量
    SELECT
        total_rows,
        SUM(CASE WHEN model IS NULL THEN 1 ELSE 0 END) AS model_missing,
        SUM(CASE WHEN mileage IS NULL THEN 1 ELSE 0 END) AS mileage_missing,
        SUM(CASE WHEN manufacture IS NULL THEN 1 ELSE 0 END) AS manufacture_missing,
        SUM(CASE WHEN engine_displacement IS NULL THEN 1 ELSE 0 END) AS engine_displacement_missing,
        SUM(CASE WHEN engine_power IS NULL THEN 1 ELSE 0 END) AS engine_power_missing,
        SUM(CASE WHEN body_type IS NULL THEN 1 ELSE 0 END) AS body_type_missing,
        SUM(CASE WHEN color_slug IS NULL THEN 1 ELSE 0 END) AS color_slug_missing,
        SUM(CASE WHEN skt_year IS NULL THEN 1 ELSE 0 END) AS skt_year_missing,
        SUM(CASE WHEN transmission IS NULL THEN 1 ELSE 0 END) AS transmission_missing,
        SUM(CASE WHEN door_count IS NULL THEN 1 ELSE 0 END) AS door_count_missing,
        SUM(CASE WHEN seat_count IS NULL THEN 1 ELSE 0 END) AS seat_count_missing,
        SUM(CASE WHEN fuel_type IS NULL THEN 1 ELSE 0 END) AS fuel_type_missing,
        SUM(CASE WHEN date_created IS NULL THEN 1 ELSE 0 END) AS date_created_missing,
        SUM(CASE WHEN date_seen IS NULL THEN 1 ELSE 0 END) AS date_seen_missing,
        SUM(CASE WHEN price IS NULL THEN 1 ELSE 0 END) AS price_missing
    FROM your_table_name, total_records
),
column_stats AS (
    -- 将列级统计结果转为行格式，方便后续筛选
    SELECT 'model' AS column_name, model_missing AS missing_count, ROUND(model_missing / total_rows, 4) AS missing_ratio FROM column_metrics
    UNION ALL
    SELECT 'mileage' AS column_name, mileage_missing AS missing_count, ROUND(mileage_missing / total_rows, 4) AS missing_ratio FROM column_metrics
    UNION ALL
    SELECT 'manufacture' AS column_name, manufacture_missing AS missing_count, ROUND(manufacture_missing / total_rows, 4) AS missing_ratio FROM column_metrics
    UNION ALL
    SELECT 'engine_displacement' AS column_name, engine_displacement_missing AS missing_count, ROUND(engine_displacement_missing / total_rows, 4) AS missing_ratio FROM column_metrics
    UNION ALL
    SELECT 'engine_power' AS column_name, engine_power_missing AS missing_count, ROUND(engine_power_missing / total_rows, 4) AS missing_ratio FROM column_metrics
    UNION ALL
    SELECT 'body_type' AS column_name, body_type_missing AS missing_count, ROUND(body_type_missing / total_rows, 4) AS missing_ratio FROM column_metrics
    UNION ALL
    SELECT 'color_slug' AS column_name, color_slug_missing AS missing_count, ROUND(color_slug_missing / total_rows, 4) AS missing_ratio FROM column_metrics
    UNION ALL
    SELECT 'skt_year' AS column_name, skt_year_missing AS missing_count, ROUND(skt_year_missing / total_rows, 4) AS missing_ratio FROM column_metrics
    UNION ALL
    SELECT 'transmission' AS column_name, transmission_missing AS missing_count, ROUND(transmission_missing / total_rows, 4) AS missing_ratio FROM column_metrics
    UNION ALL
    SELECT 'door_count' AS column_name, door_count_missing AS missing_count, ROUND(door_count_missing / total_rows, 4) AS missing_ratio FROM column_metrics
    UNION ALL
    SELECT 'seat_count' AS column_name, seat_count_missing AS missing_count, ROUND(seat_count_missing / total_rows, 4) AS missing_ratio FROM column_metrics
    UNION ALL
    SELECT 'fuel_type' AS column_name, fuel_type_missing AS missing_count, ROUND(fuel_type_missing / total_rows, 4) AS missing_ratio FROM column_metrics
    UNION ALL
    SELECT 'date_created' AS column_name, date_created_missing AS missing_count, ROUND(date_created_missing / total_rows, 4) AS missing_ratio FROM column_metrics
    UNION ALL
    SELECT 'date_seen' AS column_name, date_seen_missing AS missing_count, ROUND(date_seen_missing / total_rows, 4) AS missing_ratio FROM column_metrics
    UNION ALL
    SELECT 'price' AS column_name, price_missing AS missing_count, ROUND(price_missing / total_rows, 4) AS missing_ratio FROM column_metrics
)
-- 筛选缺失值占比超过50%的列；若要查看所有列的统计结果，替换为 SELECT * FROM column_stats
SELECT column_name, missing_count, missing_ratio
FROM column_stats
WHERE missing_ratio > 0.5;

关键说明 #

• 替换代码中的your_table_name为你实际的表名即可执行
• total_records CTE仅计算一次总记录数，避免多次执行COUNT(*)消耗资源
• CASE WHEN语句用于精准统计每列的NULL值数量，HIVE中IS NULL可直接识别缺失值
• ROUND函数用于将缺失占比保留4位小数，提升可读性

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