【趣学SQL】第三章：数据处理与管理 3.1数据清洗技术—

在这里插入图片描述

第三章：数据处理与管理

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

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

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

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

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

数据清洗的三大使命：

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

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

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

sql">-- 发现重复订单（同一用户+同一菜品+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 更新缺失值——给失忆数据打补丁

sql">-- 用默认值填充空地址（暂存为"地址待补充"）  
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;

高级技巧：

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

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

sql">-- 手机号标准化（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 数据类型转换——数据界的"变形记"

sql">-- 修复价格字段（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	自动化清洗流水线	测试断言+文档生成