📚 课程目录第3章数据预处理

3、数据预处理：数据清洗与格式化（SQL）

数据清洗，说白了就是给原始数据“洗澡”。

刚做反欺诈分析时，我拿到一张团伙关联表，里面全是乱糟糟的身份证号、手机号、IP地址。跑出的模型结果一塌糊涂。后来发现，同一个人的手机号在表里出现了三种格式：138-0000-1111、13800001111、138 0000 1111。嗯，不清洗的话，图谱里同一个实体会被当成三个人。

所以这一章，我就把自己踩过的坑和常用的SQL清洗套路整理出来，希望对你有用。

3.1 脏数据类型与处理策略

先看一张表，这是我在团伙识别项目里最常见的几种脏数据，以及对应的SQL清洗思路。

脏数据类型	示例	影响	SQL处理建议
缺失值（NULL）	身份证号为空	图谱节点断裂，关联丢失	`COALESCE` / `IFNULL` + 业务默认值或剔除
重复记录	同一手机号出现两次	边权重虚高，误判团伙	`ROW_NUMBER() OVER (PARTITION BY ...)` 去重
格式不一	日期：2024/1/1 vs 2024-01-01	JOIN 失败，聚合失真	`DATE_FORMAT` / `TO_DATE` 统一格式
异常值	年龄 200、转账金额负数	模型特征偏离，聚类偏差	范围 + 标准差过滤；业务规则截断
噪声字符	手机号含空格、短横线	相同实体分散在多行	`REGEXP_REPLACE` / `TRANSLATE`
大小写/编码不一致	"zhangsan" vs "ZhangSan"	分组偏差，团伙融合异常	`LOWER` / `UPPER` 统一；注意中文编码

小贴士： 我在一个项目中碰到地址字段混用了GBK和UTF-8，SELECT出来全是乱码。最后用CONVERT(column USING utf8) 才救回来。所以，清洗前建议先确认字符集。

3.2 SQL常用清洗函数清单

我个人习惯把常用的清洗函数分为三类，方便调取。

缺失值处理： COALESCE(val, default)、IFNULL(val, default)、NULLIF(val1, val2)
文本清洗： TRIM、REPLACE、REGEXP_REPLACE、LOWER/UPPER、SUBSTRING
类型转换： CAST(expr AS type)、CONVERT(expr, type)、DATE_FORMAT（MySQL）/TO_CHAR（PG/Oracle）

举个例子，清洗手机号字段，我喜欢一步到位：

-- 去除所有非数字字符，统一为11位数字
UPDATE member SET phone_clean = REGEXP_REPLACE(phone_raw, '[^0-9]', '')
WHERE phone_raw IS NOT NULL;

-- 再过滤无效号码
DELETE FROM member WHERE LENGTH(phone_clean) <> 11;

注意： 我曾经直接UPDATE原字段，结果把业务系统搞崩了——因为下游应用依赖原始格式。从此以后，我习惯保留原始列，新增_clean后缀字段。这样回溯问题很方便。

3.3 格式化处理要点

格式化是清洗的“化妆”环节。以团伙识别最常见的实体为例：身份证号、手机号、银行卡号、IP地址、MAC地址。

手机号： 统一为连续数字，不要加空格、短横线、国家码（除非业务需要区分国际号）。
身份证号： 统一大写，去掉末尾空格，注意15位和18位的兼容（我们通常转为18位）。
IP地址： 转为整数INET_ATON，方便范围计算和地区映射。
时间戳： 一律用标准UTC或Unix时间戳，避免时区混乱。
姓名： 去除首尾空格，繁体转简体（如果团伙涉及两岸三地）。

我遇到过最坑的一次：同一个身份证号，在A系统里带X，B系统里带x，C系统里还偷偷多了个空格。结果三个系统同一人无法聚合。解决方案很简单：

-- 统一身份证格式
UPDATE id_table SET id_card = UPPER(TRIM(id_card));

3.4 避坑指南：我踩过的雷

我曾经... 直接对全表做去重，以为SELECT DISTINCT *就能搞定。结果因为表中有一个日志时间戳字段每条都不同，去重后一条记录都没少。正确做法是：只在业务主键上做ROW_NUMBER()分区。

我曾经... 用WHERE phone = 'NULL'过滤无效手机，结果把字符串'NULL'当成正常数据保留，真实NULL反而丢掉了——应该用IS NULL或者phone IS NULL OR phone = ''。

我曾经... 清洗后直接覆盖原表，忘记备份，结果清洗逻辑有BUG，数据全乱了。现在我的流程中一定会有一句CREATE TABLE backup AS SELECT * FROM src。

3.5 清洗流程SOP

团队协作时，我建议大家把清洗步骤写成文档化的SQL批处理。下面是我用的一张流程清单，你可以照搬或修改。

步骤序号	动作	说明	SQL示例（节选）
1	备份原始表	防止误操作	`CREATE TABLE raw_bak AS SELECT * FROM raw`
2	缺失值标记	为NULL字段打上业务默认值	`UPDATE t SET phone = COALESCE(phone, '未知')`
3	去除重复	保留最新/最完整记录	`DELETE FROM t WHERE id IN (...)`
4	格式统一	日期、号码、地址格式化	`UPDATE t SET date = DATE_FORMAT(date, '%Y-%m-%d')`
5	异常过滤	阈值 / 3σ 剔除	`DELETE FROM t WHERE amount < 0 OR amount > 100000`
6	一致性检查	关联表冲突校验	`JOIN 外部字典表核对`
7	结果验证	抽样人工审核	`SELECT * FROM t LIMIT 1000`

关于验证： 别偷懒！我建议每轮清洗后，计算一下唯一值个数、空值比例、重复率等指标，跟清洗前对比。可以用COUNT(DISTINCT col)快速判断格式是否已统一。

嗯，数据清洗这块其实没有银弹，但按照上面这个套路走，至少能覆盖团伙识别中90%的场景。剩下的10%，就得靠你对自己业务的理解了。