在数据驱动的业务环境中，数据质量决定分析结果准确性和决策有效性。而SQL数据清洗（Data Cleaning）作为数据预处理的主要环节，直接影响后续建模、报表、BI等工作。下面，我们系统拆解常见SQL数据清洗操作，仅供参考。

数据清洗第一守则：不备份，不清洗。

任何对生产表的UPDATE或DELETE操作都必须：先创建备份表；在测试环境验证逻辑；使用事务包裹（如果支持的话）；记录操作日志。

备份语句模板：

MySQL：

-- 创建结构+数据备份（推荐）
CREATE TABLE user_backup_20251226 LIKE user;
INSERT INTO user_backup_20251226 SELECT * FROM user;

Oracle：

-- 一步创建备份表（含数据）
CREATE TABLE user_backup_20251226 AS SELECT * FROM user;

PostgreSQL：

-- 同Oracle语法
CREATE TABLE user_backup_20251226 AS SELECT * FROM user;

提示：备份表名，我们建议包含日期（如：_20251226），便于版本管理。

一、清洗操作详解

1、删除空值（NULL值）

删除指定列中为NULL的整行记录。适用于该列不允许为空且缺失即无效的场景（如：用户ID、订单号）。

⚠️ 警告：慎用！可能造成数据丢失。我们建议先统计空值比例。

语法模板：

数据库	语法
通用	DELETE FROM 表名 WHERE 列名 IS NULL;

所有主流数据库均支持IS NULL判断。

可复现示例（以MySQL为例）：

-- 1.创建测试表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 2.插入含空值数据
INSERT INTO users VALUES
(1, 'Alice', 'alice@example.com'),
(2, 'Bob', NULL),
(3, NULL, 'charlie@example.com'),
(4, 'David', 'david@example.com');

-- 3.查看原始数据
SELECT * FROM users;
/*
+----+--------+---------------------+
| id | name   | email               |
+----+--------+---------------------+
| 1  | Alice  | alice@example.com   |
| 2  | Bob    | NULL                |
| 3  | NULL   | charlie@example.com |
| 4  | David  | david@example.com   |
+----+--------+---------------------+
*/

-- 4.删除email为空的行
DELETE FROM users WHERE email IS NULL;

-- 5.验证结果
SELECT * FROM users;
/*
+----+-------+-------------------+
| id | name  | email             |
+----+-------+-------------------+
| 1  | Alice | alice@example.com |
| 3  | NULL  | charlie@example.com |
| 4  | David | david@example.com |
+----+-------+-------------------+
*/

注意：name为NULL的行未被删除，因为条件只针对email。

2、用默认值替换空值

将NULL值替换为指定默认值（如：'未知'、0、'N/A'），保留行完整性，适用于允许缺失但是需要填充的字段。

我们推荐使用SELECT预览效果，再决定是否UPDATE。

语法模板：

数据库	函数	示例
MySQL	COALESCE(col, val)	SELECT COALESCE(name, '匿名') FROM user;
PostgreSQL	COALESCE(col, val)	同上
Oracle	NVL(col, val)	SELECT NVL(name, '匿名') FROM user;

COALESCE是SQL标准函数，支持多参数（返回第一个非NULL值）；NVL仅Oracle特有，仅支持两个参数。

可复现示例（PostgreSQL）：

-- 1.建表并插入数据
CREATE TABLE product (
    id SERIAL,
    name TEXT,
    price NUMERIC
);

INSERT INTO product (name, price) VALUES
('手机', 2999),
(NULL, 1999),
('耳机', NULL),
('手表', 899);

-- 2.查询时替换NULL
SELECT 
    id,
    COALESCE(name, '商品名称缺失') AS name_cleaned,
    COALESCE(price, 0) AS price_cleaned
FROM product;
/*
 id |    name_cleaned     | price_cleaned
----+---------------------+---------------
  1 | 手机                |          2999
  2 | 商品名称缺失        |          1999
  3 | 耳机                |             0
  4 | 手表                |           899
*/

-- 3.如果需要永久更新（谨慎！）
UPDATE product
SET 
    name = COALESCE(name, '商品名称缺失'),
    price = COALESCE(price, 0);

3、删除重复行

保留每组重复记录中的一行（通常是最小ID或最新时间），删除其余重复项。适用于主键冲突、导入重复等问题。

思路：通过GROUP BY找出唯一标识（如：最小ID），保留这些ID对应的行。

语法模板（基于自增ID）：需要子查询支持

DELETE FROM 表名
WHERE id NOT IN (
    SELECT MIN(id)
    FROM (SELECT * FROM 表名) AS tmp  -- MySQL需要加临时表绕过限制
    GROUP BY 列名1, 列名2
);

⚠️ MySQL 5.7+对DELETE ... WHERE ... IN (子查询)有限制，我们需要用派生表（FROM (SELECT ...) AS tmp）。

可复现实例（MySQL）：

-- 1.创建含重复数据的表
CREATE TABLE order_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product VARCHAR(50),
    amount DECIMAL(10,2)
);

INSERT INTO order_log (user_id, product, amount) VALUES
(101, '手机', 2999.00),
(102, '耳机', 199.00),
(101, '手机', 2999.00),  -- 重复
(103, '手表', 899.00),
(102, '耳机', 199.00);   -- 重复

-- 2.查看重复情况
SELECT user_id, product, COUNT(*) 
FROM order_log 
GROUP BY user_id, product 
HAVING COUNT(*) > 1;
/*
+---------+--------+----------+
| user_id | product| count(*) |
+---------+--------+----------+
| 101     | 手机   | 2        |
| 102     | 耳机   | 2        |
+---------+--------+----------+
*/

-- 3.删除重复（保留最小id）
DELETE FROM order_log
WHERE id NOT IN (
    SELECT * FROM (
        SELECT MIN(id)
        FROM order_log
        GROUP BY user_id, product
    ) AS tmp
);

-- 4.验证结果（应剩3行）
SELECT * FROM order_log;
/*
+----+---------+--------+---------+
| id | user_id | product| amount  |
+----+---------+--------+---------+
| 1  | 101     | 手机   | 2999.00 |
| 2  | 102     | 耳机   | 199.00 |
| 4  | 103     | 手表   | 899.00 |
+----+---------+--------+---------+
*/

NOT IN + MIN(id)方法很经典，但是对于超大型表（千万级以上），我们推荐用窗口函数ROW_NUMBER()实现，性能更优且逻辑更清晰：

-- MySQL 8.0+
DELETE FROM order_log
WHERE id IN (
   SELECT id FROM (
        SELECT 
            id,
           ROW_NUMBER() OVER (PARTITION BY user_id, product ORDER BY id) AS rn
       FROM order_log
    ) t WHERE rn > 1
);

这种写法能精准标记每组重复数据中需要删除的行，避免全表扫描的性能问题。

4、文本转小写（标准化大小写）

将文本统一转为小写（或大写），消除因大小写不一致导致的统计偏差（如：'Apple' vs 'apple'）。

语法模板：

操作	函数
转小写	LOWER(col)
转大写	UPPER(col)

示例（Oracle）：

-- 原始数据
SELECT brand FROM products;
/*
BRAND
------
Apple
SAMSUNG
apple
Huawei
*/

-- 更新为小写
UPDATE products SET brand = LOWER(brand);

-- 结果
/*
brand
------
apple
samsung
apple
huawei
*/

应用场景：用户输入品牌、国家名、邮箱域名等。

5、查找异常值（离群点检测）

识别数值型字段中超出合理范围的记录（如：年龄=200、价格=-100）。

方法：基于业务规则设定上下限，或使用统计方法（如：3σ原则）。

语法模板：

-- 基于固定阈值
SELECT * FROM 表名
WHERE 列名 < 下限 OR 列名 > 上限;

-- 基于标准差（PostgreSQL示例）
WITH stats AS (
    SELECT AVG(列名) AS mean, STDDEV(列名) AS stddev
    FROM 表名
)
SELECT t.*
FROM 表名 t, stats s
WHERE ABS(t.列名 - s.mean) > 3 * s.stddev;

示例：查找异常年龄

-- MySQL
SELECT * FROM user
WHERE age < 0 OR age > 120;

我们建议先用SELECT查看异常数据，确认后再决定是否删除或修正。

6、查找重复行（诊断用）

不删除，仅列出重复的记录及其出现次数，用于数据质量评估。

语法模板：

SELECT 列名1, 列名2, ..., COUNT(*) AS cnt
FROM 表名
GROUP BY 列名1, 列名2, ...
HAVING COUNT(*) > 1;

示例：

-- 查找重复邮箱
SELECT email, COUNT(*) 
FROM user 
GROUP BY email 
HAVING COUNT(*) > 1;

我们可结合ROW_NUMBER()窗口函数标记重复行。

7、去除首尾空格（TRIM）

清除字符串开头和结尾的空白字符（空格、制表符等），避免' John '与'John'被视为不同值。

语法：

UPDATE 表名 SET 列名 = TRIM(列名);

扩展：LTRIM()（左去空格）、RTRIM()（右去空格）。

示例：

-- 原始：'  Hello World  '
UPDATE messages SET content = TRIM(content);
-- 结果：'Hello World'

8、修正拼写错误

批量修正已知的错误值（如：'USA'、'U.S.A' → 'United States'）。

语法模板：

UPDATE 表名
SET 列名 = '正确值'
WHERE 列名 IN ('错误1', '错误2', ...);

示例：

-- 统一国家名称
UPDATE user
SET country = 'United States'
WHERE country IN ('USA', 'U.S.A', 'America');

我们可结合CASE WHEN一次修正多种错误：

UPDATE user
SET country = CASE
    WHEN country IN ('USA', 'U.S.A') THEN 'United States'
    WHEN country = 'UK' THEN 'United Kingdom'
    ELSE country
END;

9、拆分全名为姓和名

将"张,三"或"三 张"拆分为first_name和last_name。

假设格式为"姓,名"（中文习惯）或"名 姓"（英文习惯），我们需要根据实际调整。

跨数据库语法对照：

数据库	拆分函数
MySQL	SUBSTRING_INDEX(str, delim, count)
Oracle	SUBSTR  + INSTR
PostgreSQL	SPLIT_PART(str, delim, pos)

示例：按逗号拆分"张,三"

MySQL：

UPDATE person
SET 
    last_name = SUBSTRING_INDEX(full_name, ',', 1),
    first_name = SUBSTRING_INDEX(full_name, ',', -1);

Oracle：

UPDATE person
SET 
    last_name = SUBSTR(full_name, 1, INSTR(full_name, ',') - 1),
    first_name = SUBSTR(full_name, INSTR(full_name, ',') + 1);

PostgreSQL：

UPDATE person
SET 
    last_name = SPLIT_PART(full_name, ',', 1),
    first_name = SPLIT_PART(full_name, ',', 2);

⚠️ 注意：如果分隔符不存在，各数据库行为不同（MySQL返回原串，PG返回空），我们建议先过滤含分隔符的行。

10、标准化日期格式

将不同格式的日期（如：'2025/12/26'、'26-Dec-2025'）统一为标准DATE类型。

跨数据库语法：

数据库	语法
MySQL	STR_TO_DATE(字符串, '%Y-%m-%d') 或DATE(列)
Oracle	TO_DATE(字符串, 'YYYY-MM-DD')
PostgreSQL	字符串::DATE 或TO_DATE(字符串, 'YYYY-MM-DD')

示例：将字符串转为标准日期

MySQL：

-- 假设date_str = '2025-12-26'
UPDATE events
SET event_date = STR_TO_DATE(date_str, '%Y-%m-%d');

Oracle：

UPDATE events
SET event_date = TO_DATE(date_str, 'YYYY-MM-DD');

PostgreSQL：

UPDATE events
SET event_date = date_str::DATE;
-- 或
UPDATE events
SET event_date = TO_DATE(date_str, 'YYYY-MM-DD');

推荐做法：存储日期时，我们应使用DATE或TIMESTAMP类型，而非字符串。

实际场景中，日期字符串格式可能混乱（如：2025-12-26/2025/12/26/12/26/2025），我们可以增加容错逻辑：

-- MySQL 8.0+自动识别多种日期格式
UPDATE events 
SET event_date = STR_TO_DATE(date_str, '%Y-%m-%d')
WHERE STR_TO_DATE(date_str, '%Y-%m-%d') IS NOT NULL;
   
-- 处理其他格式
UPDATE events 
SET event_date = STR_TO_DATE(date_str, '%Y/%m/%d')
WHERE STR_TO_DATE(date_str, '%Y/%m/%d') IS NOT NULL 
  AND event_date IS NULL;

11、标准化电话号码格式

移除非数字字符（如：+86-138 1234 5678 → 8613812345678），便于去重和匹配。

语法（使用正则）：

-- 通用思路：保留0-9，删除其他
UPDATE 表名
SET phone = REGEXP_REPLACE(phone, '[^0-9]', '');

示例：

-- 原始：'+86-138 1234 5678'
UPDATE contact
SET phone = REGEXP_REPLACE(phone, '[^0-9]', '');
-- 结果：'8613812345678'

二、高级技巧与推荐做法

1、使用事务保障安全（PostgreSQL/Oracle）

BEGIN;
-- 执行清洗
DELETE FROM user WHERE email IS NULL;
-- 验证
SELECT COUNT(*) FROM user;
-- 如果正确则提交，否则ROLLBACK
COMMIT; -- 或ROLLBACK;

MySQL InnoDB也支持事务，但MyISAM不支持。

2、使用CTE或临时表预览结果

-- PostgreSQL: 先看要删哪些
WITH to_delete AS (
    SELECT id FROM user WHERE email IS NULL
)
SELECT * FROM user WHERE id IN (SELECT id FROM to_delete);

3、性能优化建议

• 对大表清洗：添加LIMIT分批处理（MySQL）；
• 在WHERE条件列上建索引；
• 避免在UPDATE中使用复杂子查询。