PostgreSQL数据清洗实战从混乱评分到规范数字的完整解决方案接手历史遗留数据库时最令人头疼的莫过于那些充满创意的数据存储方式。上周我就遇到了这样一个案例某电商平台的商品评分表里rating字段竟然同时存在着A/B/C字母等级、1/2/3数字评分甚至还有优/良/差的中文评价。这种混乱的数据格式让统计分析几乎无法进行而业务部门正等着下周的季度报告。本文将完整还原这次数据清洗的全过程重点分享如何用PostgreSQL的CAST和CASE表达式将五花八门的评分统一为可计算的数字格式。1. 理解数据现状混乱背后的规律首先我们需要全面评估数据的混乱程度。通过以下查询可以快速了解rating字段的内容分布SELECT rating, COUNT(*) as count, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as percentage FROM ratings GROUP BY rating ORDER BY count DESC;在我的案例中查询结果显示了6种主要格式评分格式出现次数占比(%)A12,34538.218,76527.1B5,67817.623,45610.7C1,2343.839873.1提示在实际操作前务必先运行此类分析查询了解数据分布情况。这能帮助我们设计更合理的转换策略。通过进一步抽样检查发现这些格式其实对应着相同的评分体系A/1 优秀(3分)B/2 良好(2分)C/3 一般(1分)2. 设计转换逻辑CASE与CAST的完美配合基于上述分析我们需要设计一个转换逻辑将所有格式统一为1-3的数字评分。PostgreSQL的CASE WHEN表达式配合CAST是解决这类问题的利器。2.1 基础转换方案SELECT id, rating as original_rating, CASE WHEN rating A OR rating 1 THEN 3 WHEN rating B OR rating 2 THEN 2 WHEN rating C OR rating 3 THEN 1 ELSE NULL -- 处理意外值 END as numeric_rating FROM ratings;这个方案虽然可行但存在两个问题硬编码了转换规则难以维护没有验证rating字段是否包含意外值2.2 增强版转换逻辑更健壮的方案应该包含数据验证和灵活配置-- 首先创建转换规则配置表 CREATE TABLE rating_conversion_rules ( original_pattern VARCHAR(10), numeric_value INTEGER ); INSERT INTO rating_conversion_rules VALUES (A, 3), (1, 3), (B, 2), (2, 2), (C, 1), (3, 1); -- 使用正则表达式增强匹配能力 SELECT r.id, r.rating as original_rating, COALESCE( (SELECT numeric_value FROM rating_conversion_rules WHERE r.rating ~ (^ || original_pattern || $)), -1 -- 标记无法转换的记录 ) as numeric_rating FROM ratings r;3. 执行安全迁移事务与备份策略直接修改生产数据是危险的。以下是推荐的安全迁移步骤创建备份表CREATE TABLE ratings_backup AS SELECT * FROM ratings;添加新列存储转换结果ALTER TABLE ratings ADD COLUMN numeric_rating INTEGER;在事务中执行更新BEGIN; UPDATE ratings SET numeric_rating CASE WHEN rating A OR rating 1 THEN 3 WHEN rating B OR rating 2 THEN 2 WHEN rating C OR rating 3 THEN 1 ELSE NULL END; -- 验证更新记录数是否符合预期 SELECT COUNT(*) FROM ratings WHERE numeric_rating IS NULL; COMMIT;逐步切换应用使用新列先让应用同时读写rating和numeric_rating确认无误后再修改应用只使用numeric_rating4. 验证转换结果确保数据一致性数据迁移后必须进行严格验证。我通常使用以下检查方法4.1 统计分布对比-- 转换前分布 SELECT rating, COUNT(*) as old_count FROM ratings_backup GROUP BY rating ORDER BY rating; -- 转换后分布 SELECT numeric_rating, COUNT(*) as new_count FROM ratings GROUP BY numeric_rating ORDER BY numeric_rating;4.2 抽样验证SELECT r.id, b.rating as original_value, r.numeric_rating as converted_value FROM ratings r JOIN ratings_backup b ON r.id b.id WHERE r.id % 1000 0 -- 抽样间隔 ORDER BY r.id;4.3 完整性检查-- 检查是否有转换失败记录 SELECT COUNT(*) FROM ratings WHERE numeric_rating IS NULL; -- 检查数值范围是否合理 SELECT MIN(numeric_rating), MAX(numeric_rating), AVG(numeric_rating) FROM ratings;5. 性能优化处理大规模数据当面对数百万条记录时直接更新可能会导致锁表时间过长。这时可以考虑以下优化策略5.1 分批更新DO $$ DECLARE batch_size INTEGER : 10000; max_id INTEGER; min_id INTEGER : 0; BEGIN SELECT MAX(id) INTO max_id FROM ratings; WHILE min_id max_id LOOP RAISE NOTICE Processing batch % to %, min_id, min_id batch_size; UPDATE ratings SET numeric_rating CASE WHEN rating A OR rating 1 THEN 3 WHEN rating B OR rating 2 THEN 2 WHEN rating C OR rating 3 THEN 1 ELSE NULL END WHERE id min_id AND id min_id batch_size; min_id : min_id batch_size; COMMIT; END LOOP; END $$;5.2 并行处理-- 创建分区临时表 CREATE TEMP TABLE ratings_partitioned AS SELECT id, rating, id % 4 as partition -- 分为4个分区 FROM ratings; -- 并行更新不同分区 -- 可以在不同会话中同时执行以下4个更新 UPDATE ratings_partitioned SET numeric_rating CASE WHEN rating A OR rating 1 THEN 3 WHEN rating B OR rating 2 THEN 2 WHEN rating C OR rating 3 THEN 1 ELSE NULL END WHERE partition 0; -- 分别改为1,2,36. 长期解决方案防止数据混乱再现完成数据清洗后应该采取措施防止问题再次发生修改表结构约束ALTER TABLE ratings ALTER COLUMN numeric_rating SET NOT NULL, ADD CONSTRAINT valid_rating CHECK (numeric_rating BETWEEN 1 AND 3);创建视图保持兼容CREATE VIEW legacy_ratings AS SELECT id, CASE numeric_rating WHEN 3 THEN A WHEN 2 THEN B WHEN 1 THEN C END as legacy_rating FROM ratings;应用层验证在应用代码中添加输入验证使用ORM的before_save钩子统一数据格式定期数据质量检查-- 每月运行一次数据质量报告 SELECT ratings as table_name, COUNT(*) as total_rows, SUM(CASE WHEN numeric_rating IS NULL THEN 1 ELSE 0 END) as null_values, SUM(CASE WHEN numeric_rating NOT BETWEEN 1 AND 3 THEN 1 ELSE 0 END) as invalid_values FROM ratings;处理混乱数据是数据工程师的日常工作之一。这次经历让我深刻体会到比起技术语法更重要的是建立系统化的数据治理思维。每次数据清洗都应该视为改进数据质量的机会而不仅仅是完成一项任务。