NULL值占了30%，到底该怎么处理？——数据完整性治理实战

数据治理手册第 9 题约 10 分钟读完

本文目录

先说结论
NULL的三种含义
实战：数据倾斜中的NULL问题
NULL在聚合函数中的坑
NULL值治理的分层策略
NULL与三值逻辑
面试高频追问
实战踩坑
总结

先说结论

NULL处理的核心原则是：先搞清楚NULL的来源和含义，再决定怎么处理。NULL可能代表「不知道」「不适用」「未发生」三种不同的含义，处理方式完全不同。

治理的关键不是消灭所有NULL，而是确保NULL不会导致计算错误。有些NULL是合理的（比如用户没填手机号），有些NULL是数据质量问题（比如必填字段为空），要区分对待。

先把这段讲顺；面试官追问时，再展开下面的原理和细节。

详细讲解

NULL是数据开发中最容易被低估的问题。很多人写SQL时不注意NULL，结果JOIN的时候数据膨胀、聚合的时候数字偏小、过滤的时候莫名丢数据。面试被问到「NULL值怎么处理」时，如果只说「用COALESCE替换」，那就太浅了。面试官想听的是：你理解NULL的本质，知道它在不同场景下应该怎么处理，并且有实际的治理经验。

NULL的三种含义

含义	举例	正确处理	错误处理
不知道（Unknown）	用户没填年龄	保留NULL，聚合时排除	替换为0（会拉低平均值）
不适用（N/A）	未登录用户没有user_id	替换为特殊标记如'anonymous'	保留NULL（JOIN时丢数据）
未发生（Not Yet）	用户注册了但还没下单	保留NULL或0，视业务而定	直接过滤掉（丢失用户）

最常见的错误是不分情况地把所有NULL替换为0。比如用户年龄为NULL时替换为0，然后算平均年龄——0会拉低整体均值，导致数据失真。正确做法是用AVG()——它自动忽略NULL。

实战：数据倾斜中的NULL问题

我在某知名出海硬件品牌做JOIN优化时，遇到过一个经典的NULL导致数据倾斜的问题。有一张用户事件表，user_id字段有15%的值为NULL——这些是未登录状态下的匿名事件。当我用user_id做JOIN时，所有NULL值被分到了同一个Reduce Task，导致那个Task比其他Task慢10倍。

-- 问题SQL：NULL值导致JOIN数据倾斜
SELECT a.*, b.user_name
FROM dwd_user_event a
LEFT JOIN dim_user b ON a.user_id = b.user_id;
-- user_id为NULL的15%数据全部进入同一个Reduce
-- 导致执行时间从预期12分钟变成40分钟
 
-- 解决方案1：过滤NULL后再JOIN
SELECT a.*, b.user_name
FROM dwd_user_event a
LEFT JOIN dim_user b
  ON a.user_id = b.user_id
  AND a.user_id IS NOT NULL;  -- NULL不参与JOIN
 
-- 解决方案2：给NULL加随机数打散
SELECT a.*, b.user_name
FROM (
  SELECT *, COALESCE(user_id, CONCAT('null_', CAST(RAND()*100 AS INT))) AS join_key
  FROM dwd_user_event
) a
LEFT JOIN dim_user b ON a.join_key = b.user_id;
-- 执行时间从40分钟降到12分钟

最终我们选了方案1，因为user_id为NULL的记录本来就不需要关联用户信息。执行时间从40分钟降到了12分钟。这个案例面试被问到过好几次。

NULL在聚合函数中的坑

SQL中的聚合函数对NULL的处理方式不统一，这是很多Bug的来源：

-- NULL在不同聚合函数中的行为
-- 假设数据: [10, 20, NULL, 30]
 
COUNT(*)      = 4    -- 包含NULL行
COUNT(amount) = 3    -- 不包含NULL
SUM(amount)   = 60   -- 忽略NULL
AVG(amount)   = 20   -- 60/3，不是60/4！
MIN(amount)   = 10   -- 忽略NULL
MAX(amount)   = 30   -- 忽略NULL
 
-- 陷阱1: COUNT(*) vs COUNT(col)
-- 想统计有多少用户下过单，用COUNT(order_id)，不用COUNT(*)
 
-- 陷阱2: AVG的分母
-- AVG(amount) 的分母是非NULL行数，不是总行数
-- 如果想用总行数做分母：
SUM(amount) / COUNT(*)  -- 把NULL当0算

NULL值治理的分层策略

不同的数仓层级对NULL的处理策略不同：

ODS层：保留原样，不做任何NULL处理。ODS是原始数据的镜像，保持和数据源一致。如果数据源就是NULL，ODS也是NULL。

DWD层：根据字段属性分类处理。必填字段（如user_id、event_time）的NULL说明数据质量有问题，需要告警和排查。非必填字段（如手机号、地址）的NULL保留。

DWS层：根据聚合需求处理。用于求和的字段把NULL替换为0，用于计数的字段保留NULL（让COUNT自动排除），用于平均值的字段保留NULL（让AVG自动排除）。

-- DWD层：必填字段NULL校验
SELECT ds,
  SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) AS null_user_id,
  SUM(CASE WHEN event_time IS NULL THEN 1 ELSE 0 END) AS null_event_time,
  COUNT(*) AS total
FROM dwd_user_event
WHERE ds = '${bizdate}'
GROUP BY ds;
-- null_user_id > 0 就需要排查数据源
 
-- DWS层：根据聚合需求处理NULL
SELECT
  user_id,
  SUM(COALESCE(order_amount, 0)) AS total_amount,  -- 求和时NULL当0
  COUNT(order_id) AS order_count,                   -- 计数时自动排除NULL
  AVG(order_amount) AS avg_amount                   -- 平均时自动排除NULL
FROM dwd_order_detail
WHERE ds = '${bizdate}'
GROUP BY user_id;

NULL与三值逻辑

SQL使用三值逻辑（Three-Valued Logic）：TRUE、FALSE、NULL。这是很多人写SQL时容易出Bug的根源。

-- 三值逻辑的坑
 
-- 坑1: NULL不等于NULL
SELECT * FROM users WHERE age = NULL;    -- 永远返回空！
SELECT * FROM users WHERE age IS NULL;   -- 正确写法
 
-- 坑2: NOT IN遇到NULL
SELECT * FROM orders
WHERE user_id NOT IN (SELECT user_id FROM blacklist);
-- 如果blacklist里有一条user_id=NULL的记录
-- 整个NOT IN返回空！因为 x NOT IN (1,2,NULL) = UNKNOWN
 
-- 正确写法：用NOT EXISTS替代NOT IN
SELECT * FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM blacklist b WHERE b.user_id = o.user_id
);
 
-- 坑3: CASE WHEN与NULL
CASE WHEN status = 'active' THEN 1 ELSE 0 END  -- NULL的status会走ELSE

面试高频追问

追问一：COUNT(1)、COUNT(*)、COUNT(col)有什么区别？

COUNT(1)和COUNT(*)在大多数引擎里是等价的，都是统计总行数（包含NULL行）。COUNT(col)只统计col非NULL的行数。面试时常被考到：如果表里有100行，某个字段有10个NULL，那COUNT(*)=100，COUNT(col)=90。实际开发中，想统计「有多少用户填了手机号」用COUNT(phone)，想统计「总共多少条记录」用COUNT(*)。

追问二：COALESCE和IFNULL有什么区别？

IFNULL(a, b)只接受两个参数——如果a是NULL返回b。COALESCE(a, b, c, ...)接受多个参数——返回第一个非NULL的值。COALESCE是SQL标准函数，各个引擎都支持。IFNULL是MySQL/Hive特有的。建议统一用COALESCE，一是更通用，二是支持多个fallback值。

追问三：外连接中的NULL怎么处理？

LEFT JOIN时，右表没匹配到的行会填NULL。这种NULL的含义是「不存在」而不是「不知道」。处理方式取决于业务需求——如果想保留没匹配到的行（比如统计所有用户包括没下单的），就保留NULL；如果只关心匹配到的行，应该用INNER JOIN。一个常见的错误是LEFT JOIN之后在WHERE里过滤右表的字段，会把NULL行过滤掉，效果等同于INNER JOIN。

实战踩坑

我在某短剧出海公司遇到过一个NULL引起的数据错误。运营报表显示「昨天的用户付费率只有2%」，但实际应该是5%左右。排查发现是DWS层计算付费率的SQL有Bug：

-- Bug版本：NULL导致付费率偏低
SELECT
  ds,
  COUNT(DISTINCT CASE WHEN is_paid = 1 THEN user_id END) AS paid_users,
  COUNT(DISTINCT user_id) AS total_users,
  -- 这里total_users包含了大量is_paid=NULL的用户
  -- 导致分母偏大，付费率偏低
  ROUND(paid_users * 100.0 / total_users, 2) AS pay_rate
FROM dws_user_daily WHERE ds = '${bizdate}';
 
-- 修复版本：明确排除非目标用户
SELECT
  ds,
  COUNT(DISTINCT CASE WHEN is_paid = 1 THEN user_id END) AS paid_users,
  COUNT(DISTINCT CASE WHEN is_paid IS NOT NULL THEN user_id END) AS valid_users,
  ROUND(paid_users * 100.0 / valid_users, 2) AS pay_rate
FROM dws_user_daily WHERE ds = '${bizdate}';
-- 修复后付费率从2%变回了正确的5%

问题出在is_paid字段：新注册但还没有任何浏览行为的用户，is_paid是NULL（既不是付费也不是未付费）。这些用户被算进了total_users（分母），但没被算进paid_users（分子），导致付费率被拉低了。

解决方案：把分母改成「is_paid字段非NULL的用户数」，只统计有明确付费状态的用户。同时在DWD层加了数据质量规则：is_paid字段的NULL率超过10%就告警。

总结

NULL处理的核心是三步：搞清楚含义（不知道/不适用/未发生）→ 分层处理（ODS保留/DWD校验/DWS按需替换）→ 避免三值逻辑的坑（IS NULL/NOT EXISTS/COALESCE）。面试时能讲出「NULL导致JOIN倾斜」和「NOT IN遇到NULL返回空」这两个经典案例，就很有深度。

下一篇聊一个跨系统的数据治理问题：两个系统的用户ID对不上——数据一致性问题怎么解？

你遇到过NULL导致的数据Bug吗？最坑的是哪个？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长

刷题进度保存在当前浏览器