数据库表中的数据往往是杂乱无章的。您的数据可能包含缺失值、重复记录、异常值、数据输入不一致等问题。因此，在使用SQL分析数据之前，清理数据是非常重要的。

当您学习SQL时，您可以创建数据库表、修改它们、随意更新和删除记录。但在实践中，情况几乎从不是这样的。您可能没有权限更改表、更新和删除记录。但您将拥有对数据库的读取权限，并能够运行一系列的SELECT查询。

在本教程中，我们将创建一个数据库表，向其填充记录，并看看我们如何使用SQL来清洗数据。

创建包含记录的数据库表

在本教程中，让我们创建一个如下所示的表：employees

-- Create the employees table
CREATE TABLE employees (
 employee_id INT PRIMARY KEY,
 employee_name VARCHAR(50),
 salary DECIMAL(10, 2),
 hire_date VARCHAR(20),
 department VARCHAR(50)
);

接下来，让我们在表中插入一些虚构的示例记录：

-- Insert 20 sample records 
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');

如您所见，我已经使用了一小部分名字和姓氏来进行采样并构造记录的name字段，当然，也可以更有创意。 

注意：本教程中的所有查询都是针对MySQL的。 但是您可以自由选择使用RDBMS。

1.缺失值

数据记录中的缺失值始终是一个问题。因此，您必须相应地处理它们。

一种幼稚的方法是删除包含一个或多个字段的缺失值的所有记录。但是，除非您确定没有其他更好的方法来处理缺失值，否则不应这样做。

在表中，我们看到“department”列中有一个 NULL 值（参见第13employee_id行），表示缺少该字段：employees

SELECT * FROM employees;

您可以使用 COALESCE（） 函数将“Unknown”字符串用于 NULL 值：

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。因此，“薪水”列中的任何条目最多可以是100000。大于此值的条目是异常值。

我们可以通过运行以下查询来检查此类记录：

SELECT *
FROM employees
WHERE salary > 100000;

如上所述，“薪水”列中的所有条目都是有效的。所以结果集是空的：

5.数据输入不一致

不一致的数据条目和格式非常常见，尤其是在日期和字符串列中。

在表中，我们看到与员工“Joe Smith”对应的记录不在标题大小写中。employees

但为了保持一致性，让我们选择标题大小写中格式化的所有名称。您必须将 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.验证范围

在讨论异常值时，我们提到我们希望“薪水”列的上限为100000，并将任何高于100000 的薪水条目视为异常值。

但是，您不希望在“薪水”列中出现任何负值，这也是事实。因此，您可以运行以下查询来验证所有员工记录是否包含介于0和100000之间的值：

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

如图所示，结果集为空：

7.派生新列

派生新列本质上不是一个数据清理步骤。但是，在实践中，您可能需要使用现有列来派生对分析更有帮助的新列。

例如，该表包含“hire_date”列。一个更有用的字段可能是“years_of_service”列，它指示员工在公司工作了多长时间。employees

以下查询查找当前年份与“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 数据库表的权限。