【趣学SQL】第三章：数据处理与管理 3.1数据清洗技术——给数据库做“数据SPA“的魔幻之旅

第三章：数据处理与管理

3.1 数据清洗技术——给数据库做"数据SPA"的魔幻之旅

欢迎来到「数据库美容院」！今天我们将化身"数据美容师"，用一家虚拟网红餐厅的翻车案例，教你如何把脏乱差的原始数据变成清爽整洁的"素颜美女"。🧼✨

---

3.1.1 数据清洗的重要性——当餐厅吃出蟑螂

真实惨案：
某餐厅因数据混乱导致：

• 顾客生日祝福短信发给了已注销用户
• 素食者收到牛排优惠券
• 订单系统把"2023-02-30"当作有效日期

数据清洗的三大使命：

1. 删除重复：消灭克隆人订单（同一用户秒下5单佛跳墙）
2. 填补空白：给缺失电话号码的顾客发不了外卖
3. 格式整形：把"138一二三四5678"变成标准手机号

📌 行业金句：“Garbage in, garbage out”（垃圾进，垃圾出）——脏数据会让AI推荐香菜奶茶配螺蛳粉！

---

3.1.2 删除重复数据——消灭"影分身之术"

-- 发现重复订单（同一用户+同一菜品+5分钟内）  
SELECT  
  customer_id,  
  dish_name,  
  COUNT(*) AS clone_count  
FROM orders  
GROUP BY customer_id, dish_name, DATE_FORMAT(order_time, '%Y%m%d%H%i')  
HAVING clone_count > 1;  

-- 核弹级去重（保留最新记录）  
DELETE t1 FROM orders t1  
INNER JOIN orders t2   
WHERE t1.id < t2.id   
  AND t1.customer_id = t2.customer_id  
  AND t1.dish_name = t2.dish_name  
  AND TIMESTAMPDIFF(MINUTE, t1.order_time, t2.order_time) < 5;  

搞笑案例：
某顾客因手抖连续提交12次"死亡辣度"火锅订单，后因厨房报警被列入黑名单！

---

3.1.3 更新缺失值——给失忆数据打补丁

-- 用默认值填充空地址（暂存为"地址待补充"）  
UPDATE customers  
SET address = '地址待补充'  
WHERE address IS NULL;  

-- 用平均值替代异常价格（防止-999元漏洞）  
UPDATE menu  
SET price = (  
  SELECT ROUND(AVG(price), 2)   
  FROM menu   
  WHERE price BETWEEN 10 AND 1000  
)  
WHERE price < 0;  

高级技巧：

-- 三套车填充法  
COALESCE(phone, '未知号码') -- 优先级填充  
IFNULL(email, 'default@restaurant.com') -- 简单替换  
CASE WHEN birthdate IS NULL THEN '2000-01-01' ELSE birthdate END -- 条件判断  

---

3.1.4 格式化数据——强迫症患者的福音

-- 手机号标准化（131-2345-6789 → 13123456789）  
UPDATE customers  
SET phone = REPLACE(REPLACE(phone, '-', ''), ' ', '');  

-- 日期统一格式化（美式/中式混搭 → 标准ISO格式）  
UPDATE orders  
SET order_date = DATE_FORMAT(STR_TO_DATE(order_date, '%m/%d/%Y'), '%Y-%m-%d')  
WHERE order_date LIKE '%/%';  

经典乱象：

• 手机号：138一二三4五六78、138 1234 5678、138-1234-5678
• 日期：2023年12月32日（是的，真有勇士这么存！）

---

3.1.5 数据类型转换——数据界的"变形记"

-- 修复价格字段（varchar误存了￥符号）  
ALTER TABLE menu MODIFY price DECIMAL(10,2);  
UPDATE menu  
SET price = CAST(REPLACE(price, '￥', '') AS DECIMAL(10,2));  

-- 时间戳转换（字符串转真实时间）  
UPDATE user_actions  
SET action_time = FROM_UNIXTIME(CAST(action_time_str AS UNSIGNED))  
WHERE action_time_str REGEXP '^[0-9]{10}$';  

血泪教训：
某系统用varchar存库存数量，直到出现"库存充足"、"约100件"等文本值，导致促销活动崩溃！

---

3.1.6 常见的数据清洗工具——瑞士军刀套装

工具名称	适用场景	经典操作
SQL	基础清洗	DELETE/UPDATE+正则表达式
Python	复杂逻辑清洗	Pandas的fillna()+apply()
OpenRefine	可视化清洗	聚类相似值
dbt	自动化清洗流水线	测试断言+文档生成

工具选型段子：

• SQL就像厨房菜刀——基础但万能
• Python像料理机——能打碎一切复杂问题
• OpenRefine是雕花工具——专治各种"视觉洁癖"

---

课后彩蛋：数据清洗冷知识

• 最离奇的数据污染案例：某电商数据库出现"怀孕的男性用户"（性别字段被篡改）
• MySQL的utf8其实是阉割版，真正支持emoji的是utf8mb4
• 1970年代的数据清洗需要物理擦除打孔卡片

现在你已经成为"数据清洗界的保洁战神"！下一章我们将进入《分区表与分区索引——给数据库做"分舱救灾"的硬核指南》的魔幻现实主义领域，记得戴上防毒面具——脏数据的味道可比鲱鱼罐头还刺激！ 🚀