当前位置：首页 > news >正文

利用 MySQL 进行数据清洗

news 2025/7/14 17:36:47

利用 MySQL 进行数据清洗是数据预处理的重要环节，以下是常见的数据清洗操作及对应 SQL 示例：

1. 去除重复数据

使用 ROW_NUMBER() 或 GROUP BY 识别并删除重复记录。

-- 查找重复记录（以 user_id 和 email 为例）
WITH Duplicates AS (SELECT user_id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS rnFROM users
)
SELECT * FROM Duplicates WHERE rn > 1;-- 删除重复记录（保留最新的一条）
DELETE FROM users
WHERE user_id NOT IN (SELECT MAX(user_id) FROM users GROUP BY email
);

2. 处理缺失值

填充默认值：使用 COALESCE() 或 IFNULL()。
删除缺失值：使用 WHERE 过滤。

-- 填充缺失值（将 NULL 替换为默认值）
UPDATE products
SET price = COALESCE(price, 0),  -- 价格为 NULL 时填充 0category = IFNULL(category, '未知')  -- 分类为 NULL 时填充 '未知'
WHERE price IS NULL OR category IS NULL;-- 删除包含缺失值的记录
DELETE FROM orders
WHERE customer_id IS NULL;

3. 数据标准化（大小写、格式统一）

转换大小写：使用 UPPER() 或 LOWER()。
去除空格：使用 TRIM()。
日期格式化：使用 STR_TO_DATE() 或 DATE_FORMAT()。

-- 统一邮箱为小写
UPDATE users
SET email = LOWER(TRIM(email));-- 标准化日期格式（将 '2023-12-31' 转为 '31-12-2023'）
UPDATE orders
SET order_date = DATE_FORMAT(STR_TO_DATE(order_date, '%Y-%m-%d'), '%d-%m-%Y');

4. 处理无效数据

范围过滤：检查数值是否在合理区间。
正则匹配：验证格式（如邮箱、手机号）。

-- 删除年龄小于 0 或大于 120 的记录
DELETE FROM users
WHERE age < 0 OR age > 120;-- 查找不符合邮箱格式的记录
SELECT * FROM users
WHERE email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

5. 数据类型转换

使用 CAST() 或 CONVERT() 转换字段类型。

-- 将字符串类型的价格转为数值类型
ALTER TABLE products
MODIFY price DECIMAL(10, 2) AFTER CAST(price AS DECIMAL(10, 2));

6. 合并 / 拆分字段

合并字段：使用 CONCAT()。
拆分字段：使用 SUBSTRING() 或 SUBSTRING_INDEX()。

-- 合并姓名（first_name 和 last_name）
UPDATE users
SET full_name = CONCAT(first_name, ' ', last_name);-- 拆分地址（以逗号分隔）
ALTER TABLE customers
ADD street VARCHAR(100),
ADD city VARCHAR(50);UPDATE customers
SET street = SUBSTRING_INDEX(address, ',', 1),city = SUBSTRING_INDEX(address, ',', -1);

7. 异常值处理

通过统计方法（如 Z-score）识别并处理异常值。

-- 计算平均价格和标准差
WITH Stats AS (SELECT AVG(price) AS avg_price,STDDEV(price) AS std_priceFROM products
)
-- 删除价格超过 3 个标准差的异常值
DELETE FROM products
WHERE ABS(price - (SELECT avg_price FROM Stats)) > 3 * (SELECT std_price FROM Stats);

执行建议

备份数据：清洗前先备份，避免误操作。
测试逻辑：先用 SELECT 验证清洗逻辑，再执行 UPDATE 或 DELETE。
分批处理：大数据量时使用 LIMIT 分批更新，避免锁表。

-- 示例：分批删除重复记录
DELETE FROM users
WHERE user_id IN (SELECT user_id FROM (SELECT user_id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS rnFROM users) tWHERE rn > 1
)
LIMIT 1000;  -- 每次处理 1000 条