用SQL清洗雜亂數(shù)據(jù)，以便開(kāi)始進(jìn)行分析。學(xué)習(xí)如何處理缺失值、重復(fù)記錄、異常值等。

數(shù)據(jù)庫(kù)表中的數(shù)據(jù)經(jīng)常會(huì)很雜亂。你的數(shù)據(jù)可能包含缺失值、重復(fù)記錄、異常值、不一致的數(shù)據(jù)輸入等。因此，在使用SQL進(jìn)行分析之前清洗數(shù)據(jù)是非常重要的。

當(dāng)你學(xué)習(xí)SQL時(shí)，可以隨意地創(chuàng)建數(shù)據(jù)庫(kù)表，更改它們，根據(jù)需要更新和刪除記錄。但在實(shí)際操作中，幾乎從不會(huì)這樣。因?yàn)槟憧赡軟](méi)有權(quán)限更改表、更新和刪除記錄。但你有數(shù)據(jù)庫(kù)的讀取權(quán)限，可以運(yùn)行大量的SELECT查詢。

在本教程中，我們將創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)表，在其中填充記錄，并了解如何使用SQL清洗數(shù)據(jù)。

創(chuàng)建帶有記錄的數(shù)據(jù)庫(kù)表

在本教程中，讓我們創(chuàng)建一個(gè)名為employees的員工表，如下所示：

-- 創(chuàng)建employees表
CREATE TABLE employees (
 employee_id INT PRIMARY KEY,
 employee_name VARCHAR(50),
 salary DECIMAL(10, 2),
 hire_date VARCHAR(20),
 department VARCHAR(50)
);

接下來(lái)，讓我們向表中插入一些虛構(gòu)的樣本記錄：

-- 插入20個(gè)樣本記錄
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)建了姓名字段。不過(guò)，你也可以對(duì)記錄進(jìn)行更有創(chuàng)意的處理。

注意：本教程中的所有查詢都是針對(duì)MySQL的。但你可以自由選擇使用你喜歡的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)（RDBMS）。

1. 缺失值

數(shù)據(jù)記錄中的缺失值總是一個(gè)問(wèn)題。因此，必須對(duì)其進(jìn)行相應(yīng)的處理。

一種簡(jiǎn)單的方法是刪除包含一個(gè)或多個(gè)字段缺失值的所有記錄。然而，除非你確定沒(méi)有其他更好的處理缺失值的方法，否則不應(yīng)該這樣做。

在employees表中，我們可以看到department列中有一個(gè)NULL值（參見(jiàn)employee_id為13的行），表示該字段缺失：

SELECT * FROM employees;

可以使用COALESCE()函數(shù)將NULL值替換為Unknown字符串：

SELECT
 employee_id,
 employee_name,
 salary,
 hire_date,
 COALESCE(department, 'Unknown') AS department
FROM employees;

運(yùn)行上述查詢應(yīng)該會(huì)給出以下結(jié)果：

2. 重復(fù)記錄

數(shù)據(jù)庫(kù)表中的重復(fù)記錄可能會(huì)扭曲分析結(jié)果。我們?cè)跀?shù)據(jù)庫(kù)表中選擇了employee_id作為主鍵，因此在employee_data表中不會(huì)有重復(fù)的員工記錄。

仍然可以使用SELECT DISTINCT語(yǔ)句：

SELECT DISTINCT * FROM employees;

如預(yù)期所示，結(jié)果集包含了所有的20條記錄：

3. 數(shù)據(jù)類型轉(zhuǎn)換

可以注意到，hire_date列目前是VARCHAR類型,而不是日期類型。為了在處理日期時(shí)更方便，可以使用STR_TO_DATE()函數(shù)，如下所示：

SELECT
 employee_id,
 employee_name,
 salary,
 STR_TO_DATE(hire_date, '%Y-%m-%d') AS hire_date,
 department
FROM employees;

在這里，我們只選擇了hire_date列，而沒(méi)有對(duì)日期值執(zhí)行任何操作。因此，查詢的輸出結(jié)果應(yīng)與前一個(gè)查詢的結(jié)果相同。

但是，如果你想執(zhí)行諸如給值添加偏移日期之類的操作，那么該函數(shù)可能會(huì)有所幫助。

4. 異常值

一個(gè)或多個(gè)數(shù)值字段中的異常值可能會(huì)影響分析結(jié)果。因此，我們應(yīng)該檢查并清除異常值，以過(guò)濾掉不相關(guān)的數(shù)據(jù)。

但是，判斷哪些值構(gòu)成異常值需要領(lǐng)域知識(shí)，還需要利用領(lǐng)域知識(shí)和歷史數(shù)據(jù)。

在我們的示例中，假設(shè)我們知道salary列的上限為100000。因此，salary列中的任何條目最多只能是100000。而大于此值的條目則是異常值。

可以通過(guò)運(yùn)行以下查詢來(lái)檢查這樣的記錄：

SELECT *
FROM employees
WHERE salary > 100000;

如圖所示，salary列中的所有條目都是有效的。因此，結(jié)果集為空：

5. 數(shù)據(jù)輸入不一致

數(shù)據(jù)輸入和格式不一致的情況很常見(jiàn)，尤其是在日期和字符串列中。

在employees表中，可以看到員工joe smith對(duì)應(yīng)的記錄不是以標(biāo)題大小寫(xiě)形式顯示的。

但是，為了保持一致性，讓我們選擇所有以標(biāo)題大小寫(xiě)格式顯示的姓名。你需要將CONCAT()函數(shù)與UPPER()和SUBSTRING()函數(shù)結(jié)合使用，如下所示：

SELECT
 employee_id,
 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,
 salary,
 hire_date,
 department
FROM employees;

6. 驗(yàn)證范圍

在談?wù)摦惓Ｖ禃r(shí)，我們提到希望對(duì)salary列設(shè)置上限為100000，并將任何超過(guò)100000的薪資條目視為異常值。

但同樣也不能在salary列中有任何負(fù)值。因此，可以運(yùn)行以下查詢來(lái)驗(yàn)證所有員工記錄的salary列值是否都在0和100000之間：

SELECT
 employee_id,
 employee_name,
 salary,
 hire_date,
 department
FROM employees
WHERE salary < 0 OR salary > 100000;

如圖所示，salary列值都在0和100000之間。因此，結(jié)果集為空：

7. 派生新列

派生新列本質(zhì)上并不是數(shù)據(jù)清洗的步驟。然而，在實(shí)際操作中，你可能需要使用現(xiàn)有列派生出對(duì)分析更有幫助的新列。

例如，員工表包含一個(gè)hire_date列。更有幫助的字段可能是一個(gè)years_of_service列，表示員工在公司任職的年限。

以下查詢會(huì)計(jì)算當(dāng)前年份與hire_date中年份值的差值，從而計(jì)算出years_of_service：

SELECT
 employee_id,
 employee_name,
 salary,
 hire_date,
 department,
 YEAR(CURDATE()) - YEAR(hire_date) AS years_of_service
FROM employees;

應(yīng)該會(huì)看到以下輸出：

與我們運(yùn)行的其他查詢一樣，這不會(huì)修改原始表。要向原始表中添加新列，需要擁有ALTER數(shù)據(jù)庫(kù)表的權(quán)限。

總結(jié)

希望你理解了相關(guān)的數(shù)據(jù)清洗任務(wù)如何提高數(shù)據(jù)質(zhì)量并促進(jìn)更相關(guān)的分析。同時(shí)已經(jīng)學(xué)會(huì)了如何檢查缺失值、重復(fù)記錄、不一致的格式、異常值等。

嘗試創(chuàng)建自己的關(guān)系型數(shù)據(jù)庫(kù)表，并運(yùn)行一些查詢來(lái)執(zhí)行常見(jiàn)的數(shù)據(jù)清洗任務(wù)。