解密數據清洗，SQL中如何準備數據以進行分析
數據庫表中的數據經常會很雜亂。你的數據可能包含缺失值、重復記錄、異常值、不一致的數據輸入等。因此，在使用SQL進行分析之前清洗數據是非常重要的。
當你學習SQL時，可以隨意地創建數據庫表，更改它們，根據需要更新和刪除記錄。但在實際操作中，幾乎從不會這樣。因為你可能沒有權限更改表、更新和刪除記錄。但你有數據庫的讀取權限，可以運行大量的SELECT查詢。
在本教程中，我們將創建一個數據庫表，在其中填充記錄，并了解如何使用SQL清洗數據。
創建帶有記錄的數據庫表
在本教程中，讓我們創建一個名為employees的員工表，如下所示：
-- 創建employees表
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
salary DECIMAL(10, 2),
hire_date VARCHAR(20),
department VARCHAR(50)
);接下來，讓我們向表中插入一些虛構的樣本記錄：
-- 插入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');如果你能注意到的話，在這里使用了一小部分名字和姓氏作為樣本，并為記錄構建了姓名字段。不過，你也可以對記錄進行更有創意的處理。
注意：本教程中的所有查詢都是針對MySQL的。但你可以自由選擇使用你喜歡的關系型數據庫管理系統（RDBMS）。
1. 缺失值
數據記錄中的缺失值總是一個問題。因此，必須對其進行相應的處理。
一種簡單的方法是刪除包含一個或多個字段缺失值的所有記錄。然而，除非你確定沒有其他更好的處理缺失值的方法，否則不應該這樣做。
在employees表中，我們可以看到department列中有一個NULL值（參見employee_id為13的行），表示該字段缺失：
SELECT * FROM employees;
圖片
可以使用COALESCE()函數將NULL值替換為Unknown字符串：
SELECT
employee_id,
employee_name,
salary,
hire_date,
COALESCE(department, 'Unknown') AS department
FROM employees;運行上述查詢應該會給出以下結果：
圖片
2. 重復記錄
數據庫表中的重復記錄可能會扭曲分析結果。我們在數據庫表中選擇了employee_id作為主鍵，因此在employee_data表中不會有重復的員工記錄。
仍然可以使用SELECT DISTINCT語句：
SELECT DISTINCT * FROM employees;如預期所示，結果集包含了所有的20條記錄：
圖片
3. 數據類型轉換
可以注意到，hire_date列目前是VARCHAR類型,而不是日期類型。為了在處理日期時更方便，可以使用STR_TO_DATE()函數，如下所示：
SELECT
employee_id,
employee_name,
salary,
STR_TO_DATE(hire_date, '%Y-%m-%d') AS hire_date,
department
FROM employees;在這里，我們只選擇了hire_date列，而沒有對日期值執行任何操作。因此，查詢的輸出結果應與前一個查詢的結果相同。
但是，如果你想執行諸如給值添加偏移日期之類的操作，那么該函數可能會有所幫助。
4. 異常值
一個或多個數值字段中的異常值可能會影響分析結果。因此，我們應該檢查并清除異常值，以過濾掉不相關的數據。
但是，判斷哪些值構成異常值需要領域知識，還需要利用領域知識和歷史數據。
在我們的示例中，假設我們知道salary列的上限為100000。因此，salary列中的任何條目最多只能是100000。而大于此值的條目則是異常值。
可以通過運行以下查詢來檢查這樣的記錄：
SELECT *
FROM employees
WHERE salary > 100000;如圖所示，salary列中的所有條目都是有效的。因此，結果集為空：
5. 數據輸入不一致
數據輸入和格式不一致的情況很常見，尤其是在日期和字符串列中。
在employees表中，可以看到員工joe smith對應的記錄不是以標題大小寫形式顯示的。
但是，為了保持一致性，讓我們選擇所有以標題大小寫格式顯示的姓名。你需要將CONCAT()函數與UPPER()和SUBSTRING()函數結合使用，如下所示：
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. 驗證范圍
在談論異常值時，我們提到希望對salary列設置上限為100000，并將任何超過100000的薪資條目視為異常值。
但同樣也不能在salary列中有任何負值。因此，可以運行以下查詢來驗證所有員工記錄的salary列值是否都在0和100000之間：
SELECT
employee_id,
employee_name,
salary,
hire_date,
department
FROM employees
WHERE salary < 0 OR salary > 100000;如圖所示，salary列值都在0和100000之間。因此，結果集為空：
圖片
7. 派生新列
派生新列本質上并不是數據清洗的步驟。然而，在實際操作中，你可能需要使用現有列派生出對分析更有幫助的新列。
例如，員工表包含一個hire_date列。更有幫助的字段可能是一個years_of_service列，表示員工在公司任職的年限。
以下查詢會計算當前年份與hire_date中年份值的差值，從而計算出years_of_service：
SELECT
employee_id,
employee_name,
salary,
hire_date,
department,
YEAR(CURDATE()) - YEAR(hire_date) AS years_of_service
FROM employees;應該會看到以下輸出：
與我們運行的其他查詢一樣，這不會修改原始表。要向原始表中添加新列，需要擁有ALTER數據庫表的權限。
總結
希望你理解了相關的數據清洗任務如何提高數據質量并促進更相關的分析。同時已經學會了如何檢查缺失值、重復記錄、不一致的格式、異常值等。
嘗試創建自己的關系型數據庫表，并運行一些查詢來執行常見的數據清洗任務。
