PostgreSQL数据清洗实战：用CAST和CASE表达式把混乱的‘A/B/C/1/2/3’评分表统一成数字

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种主要格式：

提示：在实际操作前，务必先运行此类分析查询，了解数据分布情况。这能帮助我们设计更合理的转换策略。

通过进一步抽样检查，发现这些格式其实对应着相同的评分体系：

• 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;

这个方案虽然可行，但存在两个问题：

1. 硬编码了转换规则，难以维护
2. 没有验证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. 执行安全迁移：事务与备份策略

直接修改生产数据是危险的。以下是推荐的安全迁移步骤：

1. 创建备份表

   CREATE TABLE ratings_backup AS SELECT * FROM ratings;

2. 添加新列存储转换结果

   ALTER TABLE ratings ADD COLUMN numeric_rating INTEGER;

3. 在事务中执行更新

   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;

4. 逐步切换应用使用新列

   • 先让应用同时读写rating和numeric_rating
   • 确认无误后，再修改应用只使用numeric_rating

4. 验证转换结果：确保数据一致性

数据迁移后必须进行严格验证。我通常使用以下检查方法：

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,3

6. 长期解决方案：防止数据混乱再现

完成数据清洗后，应该采取措施防止问题再次发生：

1. 修改表结构约束

   ALTER TABLE ratings ALTER COLUMN numeric_rating SET NOT NULL, ADD CONSTRAINT valid_rating CHECK (numeric_rating BETWEEN 1 AND 3);

2. 创建视图保持兼容

   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;

3. 应用层验证

   • 在应用代码中添加输入验证
   • 使用ORM的before_save钩子统一数据格式

4. 定期数据质量检查

   -- 每月运行一次数据质量报告 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;

处理混乱数据是数据工程师的日常工作之一。这次经历让我深刻体会到，比起技术语法，更重要的是建立系统化的数据治理思维。每次数据清洗都应该视为改进数据质量的机会，而不仅仅是完成一项任务。