Lakehouse 缺失值填充指南

概述

数据缺失是数据清洗中最常见的问题。云器 Lakehouse 提供多种处理 NULL 值的函数，包括条件替换、默认值填充、前后值填充等。本指南按业务场景分类，帮助你快速掌握高效的缺失值处理方法。

涉及的 SQL 命令

COALESCE(col1, col2, ...)

IFNULL(col, value)

NULLIF(col, value)

LAG(col) / LEAD(col)

AVG() OVER (PARTITION BY)

IS NULL

IS NOT NULL

前置准备

以下示例使用模拟的用户信息表

user_profiles

-- 创建测试表 CREATE TABLE IF NOT EXISTS user_profiles ( user_id INT, user_name STRING, age INT, city STRING, last_login DATE ); -- 插入测试数据（包含 NULL 值） INSERT INTO user_profiles VALUES (1, 'Alice', 28, 'Shanghai', '2024-06-01'), (2, 'Bob', NULL, 'Beijing', '2024-05-15'), (3, 'Carol', 35, NULL, '2024-06-02'), (4, 'David', NULL, NULL, '2024-04-20'), (5, 'Eve', 22, 'Shanghai', NULL);

基础 NULL 替换

使用

COALESCE

-- 使用 COALESCE 填充缺失的城市信息 SELECT user_id, user_name, COALESCE(city, 'Unknown') as city FROM user_profiles ORDER BY user_id;

结果说明：

多优先级填充

COALESCE

-- 多优先级填充：优先用城市，其次用 'N/A' SELECT user_id, user_name, COALESCE(city, user_name, 'N/A') as display_location FROM user_profiles ORDER BY user_id;

结果说明：

两值条件替换

当只需要在 NULL 和一个默认值之间切换时，

IFNULL

-- 使用 IFNULL 填充缺失的年龄 SELECT user_id, user_name, IFNULL(age, 0) as age FROM user_profiles ORDER BY user_id;

结果说明：

将特定值转为 NULL

使用

NULLIF

-- 将年龄为 0 的记录转为 NULL（表示未知） SELECT user_id, user_name, NULLIF(age, 0) as age FROM user_profiles ORDER BY user_id;

前后值填充

在时间序列数据中，常用前一行或后一行的值填充当前缺失值。

-- 使用上一次登录时间填充缺失值 SELECT user_id, user_name, last_login, LAG(last_login) OVER (ORDER BY user_id) as prev_login, COALESCE(last_login, LAG(last_login) OVER (ORDER BY user_id)) as filled_login FROM user_profiles ORDER BY user_id;

结果说明：

nan

NaT

IS NULL

分组均值填充

对于数值型缺失值，常用同组的平均值或中位数填充。

-- 使用同龄用户的平均年龄填充缺失值（按城市分组） SELECT user_id, user_name, age, city, ROUND(AVG(age) OVER (PARTITION BY city), 1) as city_avg_age, COALESCE(age, AVG(age) OVER (PARTITION BY city)) as filled_age FROM user_profiles ORDER BY user_id;

结果说明：

PARTITION BY city

AVG

AVG

COALESCE

COALESCE(age, AVG(age) OVER (PARTITION BY city), AVG(age) OVER ())

NULL 值判断

正确处理 NULL 值的逻辑判断，避免常见问题。

-- 正确的 NULL 判断方式 SELECT user_id, user_name, CASE WHEN age IS NULL THEN 'Unknown' WHEN age < 18 THEN 'Minor' WHEN age < 60 THEN 'Adult' ELSE 'Senior' END as age_group FROM user_profiles ORDER BY user_id;

结果说明：

常见问题

-- ❌ 错误：NULL 与任何值比较都返回 NULL（不是 TRUE） SELECT * FROM user_profiles WHERE age = NULL; -- 返回 0 行 -- ✅ 正确：使用 IS NULL SELECT * FROM user_profiles WHERE age IS NULL;

清理测试数据

完成缺失值处理验证后，建议清理测试表：

-- 删除测试表 DROP TABLE IF EXISTS user_profiles;

UNDROP TABLE

注意事项

1. COALESCE vs IFNULL：

   COALESCE

   COALESCE
   是标准 SQL，支持多个参数；

   IFNULL

   IFNULL
   只支持两个参数，是

   COALESCE

   COALESCE
   的简写形式。
2. NULL 与空字符串：

   NULL

   NULL
   表示缺失值，

   ''

   ''
   （空字符串）是有效值。处理时需区分。
3. 聚合函数与 NULL：

   COUNT(col)

   COUNT(col)
   不统计 NULL 值，

   SUM()

   SUM()
   、

   AVG()

   AVG()
   会忽略 NULL 值。
4. NULL 值显示：Lakehouse 中数值类型 NULL 显示为

   nan

   nan
   ，时间类型 NULL 显示为

   NaT

   NaT
   ，但逻辑判断不受影响。

相关文档

• 条件函数
• 聚合函数
• 窗口函数