欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

利用 MySQL 進行數(shù)據(jù)清洗的操作方法

 更新時間：2025年07月14日 15:56:04   作者：passion_up  
本文給大家介紹利用MySQL進行數(shù)據(jù)清洗的操作方法,下面給大家分享常見的數(shù)據(jù)清洗操作及對應SQL示例,感興趣的朋友一起看看吧

利用 MySQL 進行數(shù)據(jù)清洗是數(shù)據(jù)預處理的重要環(huán)節(jié)，以下是常見的數(shù)據(jù)清洗操作及對應 SQL 示例：

1. 去除重復數(shù)據(jù)

使用 ROW_NUMBER() 或 GROUP BY 識別并刪除重復記錄。

-- 查找重復記錄（以 user_id 和 email 為例）
WITH Duplicates AS (
  SELECT 
    user_id, 
    email, 
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS rn
  FROM users
)
SELECT * FROM Duplicates WHERE rn > 1;
-- 刪除重復記錄（保留最新的一條）
DELETE FROM users
WHERE user_id NOT IN (
  SELECT MAX(user_id) 
  FROM users 
  GROUP BY email
);

2. 處理缺失值

  • 填充默認值：使用 COALESCE() 或 IFNULL()。
  • 刪除缺失值：使用 WHERE 過濾。
-- 填充缺失值（將 NULL 替換為默認值）
UPDATE products
SET price = COALESCE(price, 0),  -- 價格為 NULL 時填充 0
    category = IFNULL(category, '未知')  -- 分類為 NULL 時填充 '未知'
WHERE price IS NULL OR category IS NULL;
-- 刪除包含缺失值的記錄
DELETE FROM orders
WHERE customer_id IS NULL;

3. 數(shù)據(jù)標準化（大小寫、格式統(tǒng)一）

  • 轉換大小寫：使用 UPPER() 或 LOWER()。
  • 去除空格：使用 TRIM()。
  • 日期格式化：使用 STR_TO_DATE() 或 DATE_FORMAT()。
-- 統(tǒng)一郵箱為小寫
UPDATE users
SET email = LOWER(TRIM(email));
-- 標準化日期格式（將 '2023-12-31' 轉為 '31-12-2023'）
UPDATE orders
SET order_date = DATE_FORMAT(STR_TO_DATE(order_date, '%Y-%m-%d'), '%d-%m-%Y');

4. 處理無效數(shù)據(jù)

  • 范圍過濾：檢查數(shù)值是否在合理區(qū)間。
  • 正則匹配：驗證格式（如郵箱、手機號）。
-- 刪除年齡小于 0 或大于 120 的記錄
DELETE FROM users
WHERE age < 0 OR age > 120;
-- 查找不符合郵箱格式的記錄
SELECT * FROM users
WHERE email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

5. 數(shù)據(jù)類型轉換

使用 CAST() 或 CONVERT() 轉換字段類型。

-- 將字符串類型的價格轉為數(shù)值類型
ALTER TABLE products
MODIFY price DECIMAL(10, 2) AFTER CAST(price AS DECIMAL(10, 2));

6. 合并 / 拆分字段

  • 合并字段：使用 CONCAT()。
  • 拆分字段：使用 SUBSTRING() 或 SUBSTRING_INDEX()。
-- 合并姓名（first_name 和 last_name）
UPDATE users
SET full_name = CONCAT(first_name, ' ', last_name);
-- 拆分地址（以逗號分隔）
ALTER TABLE customers
ADD street VARCHAR(100),
ADD city VARCHAR(50);
UPDATE customers
SET 
  street = SUBSTRING_INDEX(address, ',', 1),
  city = SUBSTRING_INDEX(address, ',', -1);

7. 異常值處理

通過統(tǒng)計方法（如 Z-score）識別并處理異常值。

-- 計算平均價格和標準差
WITH Stats AS (
  SELECT 
    AVG(price) AS avg_price,
    STDDEV(price) AS std_price
  FROM products
)
-- 刪除價格超過 3 個標準差的異常值
DELETE FROM products
WHERE ABS(price - (SELECT avg_price FROM Stats)) > 3 * (SELECT std_price FROM Stats);

執(zhí)行建議

  1. 備份數(shù)據(jù)：清洗前先備份，避免誤操作。
  2. 測試邏輯：先用 SELECT 驗證清洗邏輯，再執(zhí)行 UPDATE 或 DELETE。
  3. 分批處理：大數(shù)據(jù)量時使用 LIMIT 分批更新，避免鎖表。
-- 示例：分批刪除重復記錄
DELETE FROM users
WHERE user_id IN (
  SELECT user_id FROM (
    SELECT user_id, 
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS rn
    FROM users
  ) t
  WHERE rn > 1
)
LIMIT 1000;  -- 每次處理 1000 條

索引：

索引是數(shù)據(jù)庫中用于提高查詢效率的關鍵工具，它類似書籍的目錄，可以快速定位到數(shù)據(jù)的位置。

1. 索引的作用

  • 加速查詢：通過索引，數(shù)據(jù)庫無需掃描全量數(shù)據(jù)，直接定位到符合條件的記錄。

  • 優(yōu)化排序：索引通常已排序，可避免額外的排序操作。

  • 強制唯一性：唯一索引（如主鍵）可防止重復數(shù)據(jù)。

2. 索引的原理

  • 數(shù)據(jù)結構：常見的索引使用 B-Tree（MySQL InnoDB）或哈希表（Memory 引擎）。

  • 存儲方式：索引單獨存儲，包含鍵值和指向數(shù)據(jù)行的物理地址。

到此這篇關于利用 MySQL 進行數(shù)據(jù)清洗的操作方法的文章就介紹到這了,更多相關mysql數(shù)據(jù)清洗內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

相關文章

最新評論