用SQL清洗雜亂數(shù)據(jù)，以便開始進行分析。學(xué)習如何處理缺失值、重復(fù)記錄、異常值等。
使用Segmind SSD-1B模型生成的圖像
數(shù)據(jù)庫表中的數(shù)據(jù)經(jīng)常會很雜亂。你的數(shù)據(jù)可能包含缺失值、重復(fù)記錄、異常值、不一致的數(shù)據(jù)輸入等。因此，在使用SQL進行分析之前清洗數(shù)據(jù)是非常重要的。
當你學(xué)習SQL時，可以隨意地創(chuàng)建數(shù)據(jù)庫表，更改它們，根據(jù)需要更新和刪除記錄。但在實際操作中，幾乎從不會這樣。因為你可能沒有權(quán)限更改表、更新和刪除記錄。但你有數(shù)據(jù)庫的讀取權(quán)限，可以運行大量的SELECT查詢。
在本教程中，我們將創(chuàng)建一個數(shù)據(jù)庫表，在其中填充記錄，并了解如何使用SQL清洗數(shù)據(jù)。
創(chuàng)建帶有記錄的數(shù)據(jù)庫表 在本教程中，讓我們創(chuàng)建一個名為employees的員工表，如下所示：
-- 創(chuàng)建employees表 CREATE TABLE employees (INT PRIMARY KEY ,VARCHAR (50 ),DECIMAL (10 , 2 ),VARCHAR (20 ),VARCHAR (50 )接下來，讓我們向表中插入一些虛構(gòu)的樣本記錄：
-- 插入20個樣本記錄 INSERT INTO employees (employee_id, employee_name, salary, hire_date, department) VALUES 1 , 'Amy West' , 60000.00 , '2021-01-15' , 'HR' ),2 , 'Ivy Lee' , 75000.50 , '2020-05-22' , 'Sales' ),3 , 'joe smith' , 80000.75 , '2019-08-10' , 'Marketing' ), 4 , 'John White' , 90000.00 , '2020-11-05' , 'Finance' ),5 , 'Jane Hill' , 55000.25 , '2022-02-28' , 'IT' ),6 , 'Dave West' , 72000.00 , '2020-03-12' , 'Marketing' ),7 , 'Fanny Lee' , 85000.50 , '2018-06-25' , 'Sales' ),8 , 'Amy Smith' , 95000.25 , '2019-11-30' , 'Finance' ),9 , 'Ivy Hill' , 62000.75 , '2021-07-18' , 'IT' ),10 , 'Joe White' , 78000.00 , '2022-04-05' , 'Marketing' ),11 , 'John Lee' , 68000.50 , '2018-12-10' , 'HR' ),12 , 'Jane West' , 89000.25 , '2017-09-15' , 'Sales' ),13 , 'Dave Smith' , 60000.75 , '2022-01-08' , NULL ),14 , 'Fanny White' , 72000.00 , '2019-04-22' , 'IT' ),15 , 'Amy Hill' , 84000.50 , '2020-08-17' , 'Marketing' ),16 , 'Ivy West' , 92000.25 , '2021-02-03' , 'Finance' ),17 , 'Joe Lee' , 58000.75 , '2018-05-28' , 'IT' ),18 , 'John Smith' , 77000.00 , '2019-10-10' , 'HR' ),19 , 'Jane Hill' , 81000.50 , '2022-03-15' , 'Sales' ),20 , 'Dave White' , 70000.25 , '2017-12-20' , 'Marketing' );如果你能注意到的話，在這里使用了一小部分名字和姓氏作為樣本，并為記錄構(gòu)建了姓名字段。不過，你也可以對記錄進行更有創(chuàng)意的處理。
注意： 本教程中的所有查詢都是針對MySQL的。但你可以自由選擇使用你喜歡的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)（RDBMS）。
1. 缺失值 數(shù)據(jù)記錄中的缺失值總是一個問題。因此，必須對其進行相應(yīng)的處理。
一種簡單的方法是刪除包含一個或多個字段缺失值的所有記錄。然而，除非你確定沒有其他更好的處理缺失值的方法，否則不應(yīng)該這樣做。
在employees表中，我們可以看到department列中有一個NULL值（參見employee_id為13的行），表示該字段缺失：
SELECT * FROM employees;
可以使用COALESCE()函數(shù)將NULL值替換為Unknown字符串：
SELECT COALESCE (department, 'Unknown' ) AS departmentFROM employees;運行上述查詢應(yīng)該會給出以下結(jié)果：
2. 重復(fù)記錄 數(shù)據(jù)庫表中的重復(fù)記錄可能會扭曲分析結(jié)果。我們在數(shù)據(jù)庫表中選擇了employee_id作為主鍵，因此在employee_data表中不會有重復(fù)的員工記錄。
仍然可以使用SELECT DISTINCT語句：
SELECT DISTINCT * FROM employees;如預(yù)期所示，結(jié)果集包含了所有的20條記錄：
3. 數(shù)據(jù)類型轉(zhuǎn)換 可以注意到，hire_date列目前是VARCHAR類型,而不是日期類型。為了在處理日期時更方便，可以使用STR_TO_DATE()函數(shù)，如下所示：
SELECT STR_TO_DATE (hire_date, '%Y-%m-%d' ) AS hire_date,FROM employees;在這里，我們只選擇了hire_date列，而沒有對日期值執(zhí)行任何操作。因此，查詢的輸出結(jié)果應(yīng)與前一個查詢的結(jié)果相同。
但是，如果你想執(zhí)行諸如給值添加偏移日期之類的操作，那么該函數(shù)可能會有所幫助。
4. 異常值 一個或多個數(shù)值字段中的異常值可能會影響分析結(jié)果。因此，我們應(yīng)該檢查并清除異常值，以過濾掉不相關(guān)的數(shù)據(jù)。
但是，判斷哪些值構(gòu)成異常值需要領(lǐng)域知識，還需要利用領(lǐng)域知識和歷史數(shù)據(jù)。
在我們的示例中，假設(shè)我們知道salary列的上限為100000。因此，salary列中的任何條目最多只能是100000。而大于此值的條目則是異常值。
可以通過運行以下查詢來檢查這樣的記錄：
SELECT *FROM employeesWHERE salary > 100000 ;如圖所示，salary列中的所有條目都是有效的。因此，結(jié)果集為空：
5. 數(shù)據(jù)輸入不一致 數(shù)據(jù)輸入和格式不一致的情況很常見，尤其是在日期和字符串列中。
在employees表中，可以看到員工joe smith對應(yīng)的記錄不是以標題大小寫形式顯示的。
但是，為了保持一致性，讓我們選擇所有以標題大小寫格式顯示的姓名。你需要將CONCAT()函數(shù)與UPPER()和SUBSTRING()函數(shù)結(jié)合使用，如下所示：
SELECT CONCAT (UPPER (SUBSTRING (employee_name, 1 , 1 )), -- Capitalize the first letter of the first name LOWER (SUBSTRING (employee_name, 2 , LOCATE (' ' , employee_name) - 2 )), -- Make the rest of the first name lowercase ' ' ,UPPER (SUBSTRING (employee_name, LOCATE (' ' , employee_name) + 1 , 1 )), -- Capitalize the first letter of the last name LOWER (SUBSTRING (employee_name, LOCATE (' ' , employee_name) + 2 )) -- Make the rest of the last name lowercase AS employee_name_title_case,FROM employees;
6. 驗證范圍 在談?wù)摦惓Ｖ禃r，我們提到希望對salary列設(shè)置上限為100000，并將任何超過100000的薪資條目視為異常值。
但同樣也不能在salary列中有任何負值。因此，可以運行以下查詢來驗證所有員工記錄的salary列值是否都在0和100000之間：
SELECT FROM employeesWHERE salary < 0 OR salary > 100000 ;如圖所示，salary列值都在0和100000之間。因此，結(jié)果集為空：
7. 派生新列 派生新列本質(zhì)上并不是數(shù)據(jù)清洗的步驟。然而，在實際操作中，你可能需要使用現(xiàn)有列派生出對分析更有幫助的新列。
例如，員工表包含一個hire_date列。更有幫助的字段可能是一個years_of_service列，表示員工在公司任職的年限。
以下查詢會計算當前年份與hire_date中年份值的差值，從而計算出years_of_service：
SELECT YEAR (CURDATE ()) - YEAR (hire_date) AS years_of_serviceFROM employees;應(yīng)該會看到以下輸出：
與我們運行的其他查詢一樣，這不會修改原始表。要向原始表中添加新列，需要擁有ALTER數(shù)據(jù)庫表的權(quán)限。
總結(jié) 希望你理解了相關(guān)的數(shù)據(jù)清洗任務(wù)如何提高數(shù)據(jù)質(zhì)量并促進更相關(guān)的分析。同時已經(jīng)學(xué)會了如何檢查缺失值、重復(fù)記錄、不一致的格式、異常值等。
嘗試創(chuàng)建自己的關(guān)系型數(shù)據(jù)庫表，并運行一些查詢來執(zhí)行常見的數(shù)據(jù)清洗任務(wù)。
400 186 1886
