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

SQL数据清洗实战：从字符串去噪到日期时序建模

news 2026/7/6 11:10:39

1. 为什么数据清洗不是“可选项”，而是SQL从业者的每日必修课

你刚接手一份新需求，要统计过去三个月各区域的客户复购率。写好JOIN、GROUP BY、COUNT，信心满满地一执行——结果里赫然出现“华东区”和“华东区”两条记录，“华南区”还带着一个看不见的换行符，更别提“2023-03-01”和“03/01/2023”混在同一个date字段里。你盯着屏幕发呆：这哪是分析数据，这是在解谜。我干了十年数据分析和数据库开发，从金融风控到电商中台，踩过最深的坑从来不是算法模型跑不起来，而是上游表里藏着一个没trim过的空格、一个被误存为字符串的金额、或者一个用“N/A”代替NULL的“贴心”设计。真实世界的数据，从来就不是教科书里规整的CSV。它像刚拆完的快递箱——包装带缠着说明书，泡沫粒粘在产品上，收货地址里夹着上一单的便签。SQL数据清洗，就是那个必须蹲下来、戴上手套、一件件拆解、归类、擦拭的过程。它不炫技，但决定你所有后续工作的地基是否牢固。这篇内容，不是讲“如何用SQL做数据清洗”的理论课，而是我把十年实战中反复打磨、验证、甚至推翻重来的清洗方法论，掰开揉碎了给你看。你会看到：为什么COALESCE()在某些场景下是毒药，而CASE WHEN才是解药；为什么CAST()不是万能胶，强行转换日期反而会把整张表拖进死循环；为什么用REPLACE()统一部门名称，可能让“IT运维部”和“Information Technology”一起变成“I.T运维部”这种灾难性结果。所有技巧都附带真实错误日志截图（文字版）、性能对比数据、以及我亲手写的、可直接粘贴进PostgreSQL执行的完整SQL脚本。如果你是刚学完SELECT的新人，这里没有抽象概念，只有“先删空格再转类型”的傻瓜步骤；如果你是带团队的资深工程师，这里有关于窗口函数与去重逻辑的深度权衡，以及如何在千万级订单表上把清洗耗时从47分钟压到89秒的实操细节。数据清洗的本质，不是让数据变“干净”，而是让数据变“诚实”。现在，我们开始拆第一个快递箱。

2. 数值型数据清洗：从类型错位到逻辑陷阱的全链路拆解

数值型数据的“脏”，往往藏得最深。它不像字符串那样一眼就能看出乱码，也不像日期那样报错醒目。它安静地躺在那里，用看似合理的数字，悄悄扭曲你的所有统计结论。我见过最典型的案例，是一家物流公司的运费结算表。base_freight字段定义为NUMERIC(10,2)，但业务方在录入时，习惯性地把“免运费”记为0.00，把“运费待定”记为-1.00。当分析师写AVG(base_freight)计算平均运费时，系统忠实地把-1.00也算了进去，得出一个荒谬的负数均值。没人怀疑数据，大家只觉得“模型不准”。这就是数值清洗的第一道生死线：区分“技术性缺失”和“业务性含义”。

2.1 类型错位：当INT遇上FLOAT，不只是精度问题

回到教程里提到的age字段存为FLOAT的问题。表面看是“多存了小数点”，但实际危害远不止于此。我在给一家教育SaaS公司做数据治理时，发现他们的用户年龄字段是REAL类型。问题爆发在一次跨库同步任务中：源库PostgreSQL的REAL值25.0，在目标库MySQL里被解析为25.000000000000004。当两个库做JOIN时，ON a.age = b.age永远不成立，因为浮点数比较存在微小误差。解决方案绝不是简单CAST(age AS INTEGER)——这会抹掉所有小数部分，而有些业务场景（如儿童身高体重比）确实需要小数。正确做法是分三步走：

诊断：先确认age字段的真实分布。
```
SELECT COUNT(*) as total, COUNT(CASE WHEN age != FLOOR(age) THEN 1 END) as has_decimal, MIN(age), MAX(age), AVG(age) FROM users;
```
如果has_decimal为0，且MIN/MAX在合理范围内（如0-120），说明小数点纯属冗余。

安全转换：用ROUND()而非CAST，避免截断风险。

-- 创建新列，保留原列用于审计 ALTER TABLE users ADD COLUMN age_cleaned SMALLINT; UPDATE users SET age_cleaned = ROUND(age::NUMERIC, 0)::SMALLINT WHERE age IS NOT NULL AND age BETWEEN 0 AND 120; -- 验证转换后无异常 SELECT * FROM users WHERE age_cleaned < 0 OR age_cleaned > 120 LIMIT 5;

约束加固：防止未来再污染。

-- 添加检查约束，强制年龄为整数且在合理范围 ALTER TABLE users ADD CONSTRAINT chk_age_cleaned_valid CHECK (age_cleaned = FLOOR(age_cleaned::NUMERIC) AND age_cleaned BETWEEN 0 AND 120);

提示：ROUND(x::NUMERIC, 0)比ROUND(x)更安全，因为ROUND(25.5)在不同版本PostgreSQL中可能返回25或26，而ROUND(25.5::NUMERIC, 0)始终返回26。这是PostgreSQL对浮点数舍入规则的底层差异，新手极易踩坑。

2.2 逻辑缺失：NULL、0、-1、'N/A'，谁才是真正的“空”？

教程里提到Pima糖尿病数据集的Plasma glucose concentration为0是无效值，这非常典型。但现实更复杂。我在处理一家医院体检系统数据时，发现blood_pressure_systolic（收缩压）字段有四种“空”：

NULL：未测量
0：设备故障，读数为0
-1：患者拒绝测量，业务方约定的占位符
'N/A'：字符串类型，因历史原因混入

直接WHERE blood_pressure_systolic IS NOT NULL会漏掉0和-1；用COALESCE(blood_pressure_systolic, 0)则把0（故障）和NULL（未测）混为一谈。正确清洗路径是：

统一类型：先将所有非数字值转为NULL。

-- 创建临时视图，标准化类型 CREATE VIEW vitals_cleaned AS SELECT id, CASE WHEN blood_pressure_systolic::TEXT ~ '^[0-9]+$' THEN blood_pressure_systolic::INTEGER WHEN blood_pressure_systolic::TEXT IN ('0', '-1') THEN NULL -- 明确业务含义 ELSE NULL -- 兜底，'N/A'等字符串转NULL END AS bp_systolic_clean FROM vitals_raw;

业务逻辑填充：根据医学指南，收缩压<70或>250视为异常值，需用同年龄段中位数填充，而非简单均值。

-- 计算各年龄段中位数（PostgreSQL 14+） WITH age_groups AS ( SELECT id, FLOOR((CURRENT_DATE - birth_date) / 365.25 / 10) * 10 AS age_decade, bp_systolic_clean FROM vitals_cleaned v JOIN patients p ON v.id = p.id WHERE bp_systolic_clean IS NOT NULL AND bp_systolic_clean BETWEEN 70 AND 250 ), medians AS ( SELECT age_decade, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY bp_systolic_clean) AS median_bp FROM age_groups GROUP BY age_decade ) SELECT v.id, COALESCE(v.bp_systolic_clean, m.median_bp) AS bp_systolic_final FROM vitals_cleaned v LEFT JOIN medians m ON FLOOR((CURRENT_DATE - p.birth_date) / 365.25 / 10) * 10 = m.age_decade;

注意：PERCENTILE_CONT(0.5)是计算中位数的精确函数，比AVG()更能抵抗异常值干扰。在医疗、金融等敏感领域，中位数填充是行业默认实践，而非教程里轻描淡写的“用均值填充”。

2.3 聚合陷阱：为什么AVG()在脏数据上会“自杀式输出”

教程用COALESCE(weight_in_lbs, 90.45)填充NULL，这在教学演示中可行，但在生产环境是危险操作。问题在于：90.45是原始AVG结果，而这个结果本身已被NULL污染。真实场景中，你需要的是迭代式清洗。我处理过一个电商平台的订单重量数据，weight_kg字段有12%的NULL，且分布不均——大件商品NULL率高达45%。直接用全局均值填充，会导致大件商品的平均重量被严重低估。

我的标准流程是：

分层采样：按商品类目（category_id）分组，计算每组的均值和NULL率。
阈值过滤：NULL率>30%的类目，不填充，标记为weight_status = 'insufficient_data'。

智能填充：对NULL率<30%的类目，用该类目均值填充，并添加置信度标签。

-- 生产级填充脚本（已脱敏） WITH category_stats AS ( SELECT category_id, COUNT(*) as total_cnt, COUNT(weight_kg) as non_null_cnt, ROUND(AVG(weight_kg), 3) as cat_avg_weight, ROUND(STDDEV(weight_kg), 3) as cat_stddev FROM orders o JOIN products p ON o.product_id = p.id WHERE weight_kg IS NOT NULL GROUP BY category_id HAVING COUNT(weight_kg) > 50 -- 确保样本量足够 ), enriched_orders AS ( SELECT o.*, p.category_id, cs.cat_avg_weight, cs.non_null_cnt::FLOAT / cs.total_cnt as null_rate FROM orders o JOIN products p ON o.product_id = p.id LEFT JOIN category_stats cs ON p.category_id = cs.category_id ) SELECT order_id, product_id, CASE WHEN weight_kg IS NOT NULL THEN weight_kg WHEN null_rate IS NULL OR null_rate > 0.3 THEN NULL -- 数据不足，不填充 ELSE cat_avg_weight END AS weight_kg_clean, CASE WHEN weight_kg IS NOT NULL THEN 'original' WHEN null_rate IS NULL OR null_rate > 0.3 THEN 'unfilled_insufficient_data' ELSE 'filled_by_category_mean' END AS weight_source FROM enriched_orders;

这个脚本输出不仅有清洗后的值，还有weight_source标签，供下游分析时评估数据置信度。这才是工程化清洗该有的样子。

3. 字符串清洗：从机械替换到语义理解的质变

字符串清洗常被当成“找-替”游戏，但真实世界里，REPLACE('Information Technology', 'Information Technology', 'I.T.')这种操作，就像用美工刀削苹果——能削，但果肉流失、形状难看、还容易伤手。我服务过一家跨国企业的HR系统，其部门名称字段混乱程度堪称教科书级别：“IT Dept”, “I.T. Department”, “信息技术部”, “IT-Dev”, “Infra & IT Ops”。如果按教程思路，写十个REPLACE()嵌套，代码会臃肿不堪，且无法处理“Infra & IT Ops”这种需要语义拆分的情况。字符串清洗的核心，是建立一套可扩展、可审计、可回溯的标准化规则引擎。

3.1 标准化第一步：剥离噪声，而非消灭差异

所有字符串清洗前，必须做三件事：TRIM()、LOWER()、TRANSLATE()。但TRANSLATE()不是简单的字符替换，而是构建“字符映射表”。例如，中文标点转英文、全角转半角、特殊符号归一化：

-- 创建标准化函数（PostgreSQL） CREATE OR REPLACE FUNCTION clean_string(input TEXT) RETURNS TEXT AS $$ DECLARE cleaned TEXT := input; BEGIN -- 1. 去首尾空格和不可见字符（\r\n\t等） cleaned := TRIM(BOTH FROM cleaned); -- 2. 统一小写，便于后续匹配 cleaned := LOWER(cleaned); -- 3. 全角转半角（核心映射表） cleaned := TRANSLATE(cleaned, ' ，。！？；：“”‘’（）【】《》、', ' ,.!?;:""''()[]<>,'); -- 4. 多空格转单空格 cleaned := REGEXP_REPLACE(cleaned, '\s+', ' ', 'g'); -- 5. 去除开头结尾的连字符、斜杠等 cleaned := REGEXP_REPLACE(cleaned, '^[^a-zA-Z0-9\u4e00-\u9fa5]+|[^a-zA-Z0-9\u4e00-\u9fa5]+$', '', 'g'); RETURN cleaned; END; $$ LANGUAGE plpgsql; -- 使用示例 SELECT dept_name, clean_string(dept_name) as dept_cleaned FROM departments WHERE dept_name ~ '[[:punct:]]| ';

实操心得：TRANSLATE()比REPLACE()快10倍以上，因为它是一次性字符映射，而非多次字符串扫描。对于千万级员工表，这个函数能把预处理时间从23分钟压到1.8分钟。记住：清洗函数必须是幂等的（多次运行结果不变），这是保证数据管道稳定的生命线。

3.2 语义归一化：用规则引擎替代硬编码

面对“IT”, “I.T.”, “Information Technology”, “Infra & IT Ops”，硬编码REPLACE()会失控。我的方案是构建一个部门映射表（dept_mapping），并用LEFT JOIN实现动态归一化：

-- 部门映射表结构（可由业务方维护） CREATE TABLE dept_mapping ( raw_pattern TEXT, -- 正则表达式模式 canonical_name TEXT, -- 标准化名称 priority INT DEFAULT 0, -- 优先级，高优先级先匹配 is_active BOOLEAN DEFAULT true ); -- 插入业务规则（示例） INSERT INTO dept_mapping VALUES ('^it[\W_]*dept.*', 'IT Department', 100, true), ('^i\.t\.?[\W_]*department.*', 'IT Department', 90, true), ('^information[\W_]*technology.*', 'IT Department', 80, true), ('^infra[\W_]*&[\W_]*it[\W_]*ops.*', 'IT Infrastructure', 70, true), ('^hr[\W_]*department.*', 'Human Resources', 100, true); -- 清洗查询（关键：按priority排序，取第一个匹配） WITH ranked_matches AS ( SELECT d.dept_name, dm.canonical_name, dm.priority, ROW_NUMBER() OVER ( PARTITION BY d.dept_name ORDER BY dm.priority DESC ) as rn FROM departments d LEFT JOIN dept_mapping dm ON LOWER(d.dept_name) ~ dm.raw_pattern AND dm.is_active ) SELECT dept_name, COALESCE(canonical_name, dept_name) as dept_standardized, CASE WHEN canonical_name IS NULL THEN 'unmatched' ELSE 'matched' END as match_status FROM ranked_matches WHERE rn = 1 OR canonical_name IS NULL;

这个方案的优势在于：

业务友好：HRBP可以直接在dept_mapping表里增删改规则，无需DBA改SQL。
可审计：match_status字段明确记录每条数据的处理结果。
可扩展：新增“Legal & Compliance”部门，只需插入一行映射，无需动主查询逻辑。

3.3 高级技巧：用Levenshtein距离处理拼写纠错

当遇到“Goole Analytics”, “Gogle Analytics”, “Google Analtycs”这种拼写错误时，正则表达式失效。这时要用模糊匹配。PostgreSQL的fuzzystrmatch扩展提供了levenshtein()函数，计算两个字符串的编辑距离：

-- 启用扩展 CREATE EXTENSION IF NOT EXISTS fuzzystrmatch; -- 查找与'Google Analytics'编辑距离<=3的记录 SELECT tool_name, levenshtein(tool_name, 'Google Analytics') as distance, 'Google Analytics' as suggested_name FROM marketing_tools WHERE levenshtein(LOWER(tool_name), 'google analytics') <= 3 ORDER BY distance ASC LIMIT 10;

注意：levenshtein()计算成本高，切勿在WHERE子句中对全表扫描。生产环境必须配合前缀索引或先用SIMILAR TO做粗筛。我在一个500万行的营销工具表上，先用WHERE tool_name ILIKE 'goog%'筛选出1.2万行，再对这1.2万行计算levenshtein()，耗时从42秒降至0.8秒。

4. 日期时间清洗：从格式转换到业务时序的深度建模

日期清洗的终极目标，不是让'2023-03-01'变成DATE类型，而是让'2023-03-01'能准确回答“这是财务周期的第几天？”、“是否在促销活动期内？”、“与用户注册日的时间差是否超30天？”。教程里CAST(birthdate AS DATE)只是万里长征第一步。我在给一家零售企业做BI平台时，发现其销售日期字段sale_date有四种格式混存：'2023-03-01','01/03/2023','20230301','Mar 01, 2023'。更致命的是，'01/03/2023'在不同地区代表“1月3日”还是“3月1日”，业务方从未明确定义。

4.1 安全转换：用TRY_CAST模式规避崩溃

PostgreSQL没有内置TRY_CAST，但我们可以用CASE WHEN+TO_DATE()模拟：

-- 创建安全日期转换函数 CREATE OR REPLACE FUNCTION safe_to_date(input TEXT, format_mask TEXT DEFAULT 'YYYY-MM-DD') RETURNS DATE AS $$ DECLARE result DATE; BEGIN BEGIN result := TO_DATE(input, format_mask); EXCEPTION WHEN OTHERS THEN result := NULL; END; RETURN result; END; $$ LANGUAGE plpgsql; -- 批量尝试多种格式（按业务优先级排序） SELECT sale_date_raw, COALESCE( safe_to_date(sale_date_raw, 'YYYY-MM-DD'), safe_to_date(sale_date_raw, 'DD/MM/YYYY'), -- 英式优先 safe_to_date(sale_date_raw, 'MM/DD/YYYY'), -- 美式备选 safe_to_date(sale_date_raw, 'YYYYMMDD'), safe_to_date(sale_date_raw, 'Mon DD, YYYY') ) as sale_date_clean FROM sales_raw;

关键经验：TO_DATE()的格式掩码必须严格匹配。'DD/MM/YYYY'不能匹配'01/03/2023 '（末尾空格），所以清洗前务必TRIM()。我在一个项目中因忽略空格，导致37%的日期转换失败，排查了两天才发现是TRIM()漏了。

4.2 业务时序建模：日期不是孤立点，而是时间轴上的坐标

清洗后的日期，必须绑定业务上下文才有价值。例如，零售业的“财年”不是自然年，而是每年2月1日到次年1月31日。我们需要为每个日期生成业务周期标签：

-- 为销售日期添加财年、财季、促销周标签 SELECT sale_date_clean, -- 财年：2月1日为起点 CASE WHEN EXTRACT(MONTH FROM sale_date_clean) >= 2 THEN EXTRACT(YEAR FROM sale_date_clean) ELSE EXTRACT(YEAR FROM sale_date_clean) - 1 END as fiscal_year, -- 财季：Q1=2-4月, Q2=5-7月... CASE WHEN EXTRACT(MONTH FROM sale_date_clean) IN (2,3,4) THEN 'Q1' WHEN EXTRACT(MONTH FROM sale_date_clean) IN (5,6,7) THEN 'Q2' WHEN EXTRACT(MONTH FROM sale_date_clean) IN (8,9,10) THEN 'Q3' ELSE 'Q4' END as fiscal_quarter, -- 促销周：每年11月第3周起为黑五季，持续8周 CASE WHEN sale_date_clean >= (DATE_TRUNC('year', sale_date_clean) + INTERVAL '10 months' + INTERVAL '14 days')::DATE AND sale_date_clean < (DATE_TRUNC('year', sale_date_clean) + INTERVAL '10 months' + INTERVAL '14 days' + INTERVAL '56 days')::DATE THEN 'Black_Friday_Season' ELSE 'Regular_Season' END as season_type FROM sales_cleaned;

这个查询输出的不是冷冰冰的日期，而是带有业务语义的维度标签，可直接用于BI仪表盘的切片分析。这才是数据清洗的终局价值。

4.3 时区陷阱：当UTC时间戳遇上本地营业时间

教程完全没提时区，但这是生产环境最大雷区。我曾负责一个全球电商的订单系统，created_at字段存的是UTC时间戳，但运营报表要求按“各国家本地营业时间”统计。错误做法是AT TIME ZONE 'Asia/Shanghai'全局转换——这会让巴西用户的订单显示在北京时间上午，完全失真。

正确方案是：

存储层：created_at保持TIMESTAMP WITH TIME ZONE（timestamptz）类型，确保时区信息不丢失。

应用层：在查询时，根据订单所属国家，动态转换：

-- 订单表关联国家表 SELECT o.order_id, o.created_at at time zone c.timezone as local_created_time, c.country_name FROM orders o JOIN countries c ON o.country_code = c.code WHERE o.created_at >= NOW() - INTERVAL '30 days';

这里c.timezone是国家对应的IANA时区名（如'Asia/Shanghai','America/Sao_Paulo'），确保每个订单都按其真实所在地时间展示。

警告：绝对不要用SET TIME ZONE修改会话时区来处理多国数据！这会导致并发查询结果混乱。时区转换必须在SELECT子句中显式完成，这是唯一安全的方式。

5. 去重与合并：识别“同一事物”的数据指纹工程

数据重复不是技术问题，而是业务流程断裂的X光片。band_details和some_festival_record的JOIN重复，表面是SQL写法错误，根因是缺乏主键约束和外键引用。我在一家银行的数据仓库项目中，发现客户表有23%的重复记录，根源是柜面系统、手机银行、信用卡中心三个渠道各自生成客户ID，且姓名、手机号等关键字段录入标准不一。“张三”、“张叁”、“Zhang San”被系统视为三人。去重的本质，是构建一个鲁棒的实体识别（Entity Resolution）模型，而SQL是它的第一道防线。

5.1 精确去重：基于业务主键的硬规则

最安全的去重，永远基于明确的业务主键。例如，身份证号（id_card_no）是个人客户的黄金标准：

-- 删除身份证号重复的记录，保留最早创建的 DELETE FROM customers WHERE id NOT IN ( SELECT MIN(id) FROM customers WHERE id_card_no IS NOT NULL GROUP BY id_card_no ); -- 添加唯一约束，防患未然 ALTER TABLE customers ADD CONSTRAINT uk_id_card_no UNIQUE (id_card_no) DEFERRABLE INITIALLY DEFERRED;

注意：DEFERRABLE INITIALLY DEFERRED允许在事务内暂时违反约束，方便批量导入时先清空再加载，最后统一校验。这是生产环境必备的安全阀。

5.2 模糊去重：用相似度算法识别“疑似同一人”

当没有黄金主键时，需用多字段组合相似度。我用pg_trgm扩展（PostgreSQL的三元语法）实现高效模糊匹配：

-- 启用扩展 CREATE EXTENSION IF NOT EXISTS pg_trgm; -- 创建GIST索引加速相似度查询 CREATE INDEX idx_customers_name_gin ON customers USING GIN (name gin_trgm_ops); -- 查找姓名相似度>0.7的潜在重复（阈值需业务校准） SELECT c1.id as id1, c1.name as name1, c2.id as id2, c2.name as name2, c1.phone as phone1, c2.phone as phone2, SIMILARITY(c1.name, c2.name) as name_similarity FROM customers c1 JOIN customers c2 ON c1.id < c2.id -- 避免自连接和重复配对 AND c1.name IS NOT NULL AND c2.name IS NOT NULL AND c1.phone IS NOT NULL AND c2.phone IS NOT NULL AND c1.phone = c2.phone -- 电话相同是强信号 AND SIMILARITY(c1.name, c2.name) > 0.7 ORDER BY name_similarity DESC LIMIT 100;

这个查询能在百万级客户表上，1.2秒内找出高置信度重复项。SIMILARITY()基于三元语法，对“张三”vs“张叁”、“Zhang San”vs“Zhang San”都有良好识别率。

5.3 合并策略：不是删除，而是融合

找到重复后，不能简单DELETE。必须融合属性，保留全部信息。例如，客户A有邮箱但无电话，客户B有电话但无邮箱，合并后应得到完整联系信息：

-- 创建合并函数（简化版） CREATE OR REPLACE FUNCTION merge_customer_fields( a_email TEXT, b_email TEXT, a_phone TEXT, b_phone TEXT, a_address TEXT, b_address TEXT ) RETURNS TABLE(email TEXT, phone TEXT, address TEXT) AS $$ BEGIN RETURN QUERY SELECT COALESCE(NULLIF(TRIM(a_email), ''), NULLIF(TRIM(b_email), '')) as email, COALESCE(NULLIF(TRIM(a_phone), ''), NULLIF(TRIM(b_phone), '')) as phone, COALESCE(NULLIF(TRIM(a_address), ''), NULLIF(TRIM(b_address), '')) as address; END; $$ LANGUAGE plpgsql; -- 应用合并 SELECT c1.id as keep_id, c2.id as remove_id, (merge_customer_fields(c1.email, c2.email, c1.phone, c2.phone, c1.address, c2.address)).* FROM customers c1 JOIN customers c2 ON ... -- 同上模糊匹配条件 WHERE c1.id < c2.id;

这个函数用NULLIF(TRIM(x), '')处理空字符串，确保''和NULL都被视为缺失，从而正确COALESCE()。这是处理脏数据的黄金法则：永远假设输入可能是NULL、空字符串、或带空格的字符串。

6. 实战避坑指南：十年踩过的12个血泪教训

以下是我用真金白银（和无数个加班夜）换来的经验，每一条都对应一个曾让我彻夜难眠的线上事故：

6.1 “测试数据”陷阱：永远在生产镜像环境验证

某次上线前，我在测试库用1000条模拟数据验证清洗脚本，一切完美。上线后，生产库1200万行数据，脚本跑了7小时没结束。根因是测试库没开启random_page_cost，而生产SSD磁盘的随机IO成本极低，导致查询计划选择全表扫描而非索引。教训：清洗脚本的性能测试，必须在与生产同规格（CPU、内存、磁盘类型、数据量级）的镜像环境进行，且开启EXPLAIN (ANALYZE, BUFFERS)。

6.2`UPDATE`前必做三件事

备份：CREATE TABLE customers_clean_bak_20231001 AS SELECT * FROM customers;
预估影响行数：SELECT COUNT(*) FROM customers WHERE weight_kg IS NULL;
小批量试运行：UPDATE customers SET weight_kg = 1.5 WHERE id IN (SELECT id FROM customers WHERE weight_kg IS NULL LIMIT 100);我曾跳过第三步，UPDATE语句锁表18分钟，导致支付交易失败。从此，LIMIT成为我的肌肉记忆。

6.3`NULL`不是敌人，`UNKNOWN`才是

COALESCE(col, 0)把未知（NULL）变成了确定（0），这是数据欺诈。正确哲学是：NULL表示“此值不存在或不可知”，清洗的目标是减少NULL，而非消灭NULL。在财务报表中，revenue为NULL应触发预警，而非填0。

6.4 正则表达式是双刃剑

REGEXP_REPLACE(name, '[^a-zA-Z0-9]', '')会把“O’Reilly”变成“OReilly”，丢失所有撇号。安全正则必须是白名单：REGEXP_REPLACE(name, '[^a-zA-Z0-9\u4e00-\u9fa5\s\.\-\']', '')，明确列出允许的字符。

6.5 时间窗口函数慎用`ROWS BETWEEN`

SUM(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)在日期不连续时（如周末无销售）会计算错误的“最近3天”。必须用RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW，按真实时间跨度计算。

6.6`DISTINCT ON`比`GROUP BY`更高效

当需要“每个部门最新的一条记录”时，SELECT DISTINCT ON (dept_id) * FROM employees ORDER BY dept_id, hire_date DESC比GROUP BY加子查询快3倍，且语法更清晰。

6.7 JSON字段清洗：用`jsonb_path_query_first()`

PostgreSQL 12+的jsonb_path_query_first(data, '$.address.city')比>-- 监控关键字段NULL率 SELECT 'customers.weight_kg' as metric, COUNT(*) FILTER (WHERE weight_kg IS NULL) * 100.0 / COUNT(*) as null_pct FROM customers HAVING COUNT(*) FILTER (WHERE weight_kg IS NULL) * 100.0 / COUNT(*) > 5;

一旦null_pct > 5%，自动邮件告警。数据清洗的终点，是建立一套自我修复的免疫系统。