ubuntu pgadmin如何进行数据清洗 - 问答

Ubuntu环境下通过pgAdmin进行数据清洗的步骤
数据清洗是提升数据质量的核心环节，主要包括缺失值处理、异常值修正、重复数据去重、格式规范化等操作。在Ubuntu系统中，可通过pgAdmin的SQL控制台或查询工具直接执行SQL语句完成清洗，以下是具体方法：

一、准备工作：连接数据库与打开查询工具

打开pgAdmin4（Ubuntu应用菜单中搜索“pgAdmin 4”），输入服务器密码登录。
在左侧“浏览器”面板中，展开“服务器”→“数据库”，右键目标数据库→选择“查询工具”（Query Tool），打开SQL编辑窗口。

二、常见数据清洗操作（SQL语法示例）

1. 缺失值处理：填充或删除空值

填充缺失值：用字段的中位数（数值型）或众数（分类型）替代NULL值。

-- 数值字段（如“工资”）用中位数填充
UPDATE 表名 SET 工资 = (SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY 工资) FROM 表名) 
WHERE 工资 IS NULL;

-- 分类型字段（如“性别”）用众数填充
UPDATE 表名 SET 性别 = (SELECT MODE() WITHIN GROUP (ORDER BY 性别) FROM 表名) 
WHERE 性别 IS NULL;

删除含缺失值的行（谨慎使用，避免数据量大幅减少）：
```
DELETE FROM 表名 WHERE 工资 IS NULL OR 性别 IS NULL;
```

2. 异常值处理：修正不符合业务规则的值

逻辑规则修正：通过WHERE子句筛选异常值，并用合理值替换（如年龄超过100岁设为中位数，工资为负数设为中位数）。

-- 年龄异常（>100岁）修正
UPDATE 表名 SET 年龄 = (SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY 年龄) FROM 表名) 
WHERE 年龄 > 100;

-- 工资异常（<0）修正
UPDATE 表名 SET 工资 = (SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY 工资) FROM 表名) 
WHERE 工资 < 0;

3. 重复数据去重：删除完全重复的行

删除所有重复行（保留1条）：

DELETE FROM 表名 
WHERE ctid NOT IN (
    SELECT MIN(ctid) 
    FROM 表名 
    GROUP BY 姓名, 性别, 年龄, 工资, 入职日期  -- 列出所有字段，确保唯一性
);

注：ctid是PostgreSQL的系统列，表示行的物理位置，用于唯一标识行。

4. 格式规范化：统一数据类型与格式

日期字段规范化：将字符串类型的日期转换为DATE或TIMESTAMP类型（如“2022-01-05”“20220110”统一为YYYY-MM-DD）。

UPDATE 表名 SET 入职日期 = TO_DATE(入职日期, 'YYYYMMDD') 
WHERE 入职日期 ~ '^\d{8}$';  -- 匹配8位数字格式（如20220110）

UPDATE 表名 SET 入职日期 = TO_DATE(入职日期, 'YYYY-MM-DD') 
WHERE 入职日期 ~ '^\d{4}-\d{2}-\d{2}$';  -- 匹配YYYY-MM-DD格式

数值字段规范化：将字符串类型的数值转换为NUMERIC类型（如“5000”“6000”转为数值）。

UPDATE 表名 SET 工资 = CAST(工资 AS NUMERIC) 
WHERE 工资 ~ '^\d+(\.\d+)?$';  -- 匹配整数或小数

分类型字段规范化：统一分类值的表述（如“男”“M”“male”统一为“M”）。

UPDATE 表名 SET 性别 = CASE 
    WHEN 性别 IN ('男', 'male') THEN 'M'
    WHEN 性别 = '女' THEN 'F'
    ELSE 性别
END;

三、验证清洗结果

执行清洗操作后，通过SELECT语句检查数据是否符合预期：

-- 检查缺失值是否处理完成
SELECT COUNT(*) FROM 表名 WHERE 工资 IS NULL OR 性别 IS NULL;

-- 检查异常值是否修正
SELECT * FROM 表名 WHERE 年龄 > 100 OR 工资 < 0;

-- 检查重复数据是否去重
SELECT 姓名, 性别, 年龄, COUNT(*) 
FROM 表名 
GROUP BY 姓名, 性别, 年龄 
HAVING COUNT(*) > 1;

-- 检查日期格式是否规范
SELECT 入职日期 FROM 表名 WHERE 入职日期 !~ '^\d{4}-\d{2}-\d{2}$';

四、注意事项

备份数据：清洗前务必备份目标表（右键表→选择“备份”），避免误操作导致数据丢失。
事务处理：对于重要数据，可使用BEGIN;开启事务，清洗后通过COMMIT;提交或ROLLBACK;回滚，确保操作可逆。
自动化清洗：若需定期清洗，可将SQL脚本保存为.sql文件，通过pgAdmin的“查询工具”→“保存”→“计划任务”（pgAgent）设置定时执行。

通过以上步骤，可在Ubuntu系统的pgAdmin中高效完成数据清洗，为后续数据分析或建模奠定基础。

0 赞

0 踩