ubuntu pgadmin如何进行数据清洗

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

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

1. 打开pgAdmin4（Ubuntu应用菜单中搜索“pgAdmin 4”），输入服务器密码登录。
2. 在左侧“浏览器”面板中，展开“服务器”→“数据库”，右键目标数据库→选择“查询工具”（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}
    $';
    

四、注意事项

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

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

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！