在数据流转的全链路中，原始数据从业务系统采集后，常常会因为采集故障、录入错误、规则变更等原因出现缺失值、异常值，这些问题如果不处理，会让后续的数据分析结果失真。下面我们就详细介绍如何用SQL完成缺失与异常数据的清洗工作。
一、缺失数据的识别与处理
1. 识别缺失数据
SQL中判断缺失值需要用到IS_NULL或者IS_NOT_NULL条件，注意空字符串和NULL在SQL中是不同的概念，需要分开判断。比如我们要查询用户表中手机号为缺失状态的记录，可以用以下代码：
-- 查询用户表中手机号为空的记录，包含NULL和空字符串两种情况 SELECT user_id, user_name, phone FROM user_info WHERE phone IS NULL OR phone = '';
2. 缺失值填充
根据业务规则的不同，缺失值可以选择用固定值、均值、同组其他值等方式填充。比如用户表中年龄缺失的，我们可以按性别分组用同性别的平均年龄填充：
-- 用同性别平均年龄填充年龄缺失值
UPDATE user_info u
JOIN (
SELECT gender, AVG(age) AS avg_age
FROM user_info
WHERE age IS NOT NULL AND age > 0
GROUP BY gender
) t ON u.gender = t.gender
SET u.age = ROUND(t.avg_age)
WHERE u.age IS NULL;3. 缺失值删除
如果缺失值占比过高，或者对应字段是核心分析字段，也可以选择直接删除缺失记录。比如订单表中订单金额为空的记录没有分析价值，可以直接删除：
-- 删除订单表中订单金额为空的无效记录 DELETE FROM order_info WHERE order_amount IS NULL;
二、异常数据的检测与处理
1. 基于统计规则的异常检测
常见的统计规则包括3σ原则、分位数法等，比如用户年龄如果在0到120之外就属于异常值，我们可以用以下代码查询异常年龄记录：
-- 查询年龄不在合理范围的异常用户记录 SELECT user_id, user_name, age FROM user_info WHERE age < 0 OR age > 120;
如果是数值型字段，也可以用分位数法检测异常，比如订单金额超过99分位数的视为异常：
-- 查询订单金额超过99分位数的异常订单
WITH order_percentile AS (
SELECT PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY order_amount) AS p99
FROM order_info
WHERE order_amount IS NOT NULL
)
SELECT o.order_id, o.user_id, o.order_amount
FROM order_info o, order_percentile op
WHERE o.order_amount > op.p99;2. 基于业务规则的异常检测
除了统计规则，还可以结合业务逻辑判断异常，比如订单的支付时间不能早于下单时间，出现这种情况就属于异常数据：
-- 查询支付时间早于下单时间的异常订单 SELECT order_id, user_id, create_time, pay_time FROM order_info WHERE pay_time IS NOT NULL AND pay_time < create_time;
3. 异常值处理
检测到异常值后，可以根据业务需求选择修正、标记为异常或者直接删除。比如把年龄异常的记录标记为异常，而不是直接删除，方便后续追溯：
-- 给年龄异常的用户添加异常标记 UPDATE user_info SET is_abnormal = 1 WHERE age < 0 OR age > 120;
三、数据清洗后的校验
完成清洗后，需要再次校验数据质量，确认缺失和异常数据已经被处理。比如可以统计清洗后用户表的缺失值占比、异常值数量：
-- 校验清洗后的用户数据质量
SELECT
COUNT(*) AS total_count,
SUM(CASE WHEN phone IS NULL OR phone = '' THEN 1 ELSE 0 END) AS null_phone_count,
SUM(CASE WHEN age < 0 OR age > 120 THEN 1 ELSE 0 END) AS abnormal_age_count
FROM user_info;通过以上的SQL方法，就可以完成大部分常见的缺失与异常数据清洗工作，实际应用中可以根据具体的业务场景调整规则，保障最终数据的可靠性。