SQL数据清洗常见操作_SQL处理脏数据技巧

SQL数据清洗核心是识别并修正不一致、重复、缺失或格式错误的数据，关键在于理清脏数据类型后用基础操作精准处理：空值需用IS NULL判断并填充或过滤；重复记录通过GROUP BY+HAVING查重、DELETE或建新表去重；文本用TRIM、REPLACE、UPPER等统一格式；数值/日期用范围校验和函数转换修复逻辑错误。

SQL数据清洗的核心是识别并修正不一致、重复、缺失或格式错误的数据，让原始数据变得可靠可用。关键不在于写多复杂的语句，而在于理清脏数据的类型，再用对应的基础操作精准处理。

处理空值和缺失数据

空值（NULL）最常见也最容易被忽略。直接用= NULL会失效，必须用IS NULL或IS NOT NULL判断。

• 填充默认值：用COALESCE(age, 0)把空年龄替换成0；用CASE WHEN name IS NULL THEN '未知' ELSE name END做条件替换
• 过滤掉关键字段为空的行：WHERE email IS NOT NULL AND email != ''（注意还要排除空字符串）
• 统计缺失比例有助于判断是否要删字段：SELECT COUNT(*)*100.0/COUNT(*) FROM table对比COUNT(col)

清理重复记录

重复可能源于导入错误或业务逻辑漏洞。先确认是否真要删——有时重复本身有业务含义（如多次下单），不能一概而论。

• 查重：用GROUP BY + HAVING COUNT(*) > 1定位重复组合，例如SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1
• 删重（保留最小id）：DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email)
• 更安全的做法是先创建去重后的新表，验证无误再替换原表

统一文本格式与异常字符

姓名、地址、电话等字段常混入空格、换行符、全角符号或大小写混乱。

AIBox 一站式AI创作平台

AIBox365一站式AI创作平台，支持ChatGPT、GPT4、Claue3、Gemini、Midjourney等国内外大模型

224

查看详情

• 去首尾空格：TRIM(name)；去全部空格：REPLACE(name, ' ', '')
• 标准化大小写：UPPER(email) 或 INITCAP(name)（PostgreSQL）；MySQL可用CONCAT(UPPER(LEFT(name,1)), LOWER(SUBSTRING(name,2)))
• 剔除不可见字符：REGEXP_REPLACE(phone, '[^0-9+\-()]', '')（不同数据库语法略有差异，核心是正则匹配非预期字符）

校验和修正数值/日期逻辑错误

比如出生年份写成2025、订单金额为负数、注册时间晚于下单时间——这类问题靠约束难覆盖，得靠清洗脚本主动拦截。

• 用BETWEEN或范围条件筛出异常值：WHERE age 120
• 修复日期格式错乱：STR_TO_DATE(birth_str, '%Y-%m-%d')（MySQL）或TO_DATE(birth_str, 'YYYY-MM-DD')（PostgreSQL），失败时返回NULL便于后续处理
• 业务逻辑校验举例：WHERE order_date 可找出明显不合理记录，人工复核或打标待处理

基本上就这些。SQL清洗不是一步到位的事，而是“查→标→修→验”的循环过程。真正耗时的往往不是写SQL，而是理解业务含义、定义什么是“脏”。定好规则，剩下的就是组合几个函数和子查询的事。

以上就是SQL数据清洗常见操作_SQL处理脏数据技巧的详细内容，更多请关注php中文网其它相关文章！