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中高效完成數據清洗，為后續數據分析或建模奠定基礎。