10条命令，精通SQL数据清洗、集成和转换

数据准备是数据分析师工作中非常重要的一部分。在进行数据分析之前，必须对数据进行准备，以确保数据呈现出自己和其他人都能理解的格式，从而为后续工作铺平道路。

实际上，数据准备阶段通常占据了整个数据分析工作的80%多的的工作量。这个阶段的任务是整理和清理数据，以确保其质量和一致性，并进行必要的数据集成和转换，以满足分析需求。

1 数据清洗

数据清洗是将数据格式化并符合要求，通过删除或修复不正确或不一致的部分来实现。

以下是其中的常见任务：

1.1 消除重复项

获取唯一的记录

SELECT DISTINCT order_id FROM orders;

获取该属性的第一条唯一记录

SELECT DISTINCT ON ( customer_id ) * FROM orders;

1.2 处理缺失值

选择列表中第一个非空值

SELECT COALESCE ( order_date , CURRENT_DATE ) FROM orders;

订单金额为 0 时应视为无效

SELECT NULLIF ( order_amount , 0 ) FROM orders;

CASE 用于为缺失值赋予新的身份

SELECT CASE WHEN order_amount > 1000 THEN 'High' WHEN order_amount BETWEEN 500 AND 1000 THEN 'Medium' WHEN order_amount < 500 THEN 'Low' ELSE 'Unknown';

1.3  标准化不匹配的数据类型

使用 CAST 函数可以直接转换数据类型，就像直接处理数据一样。

如果订单金额是数字，但需要它作为整数，CAST 可以实现这一点。

SELECT CAST ( order_amount AS INTEGER ) FROM orders;

使用 CONVERT 函数可以间接地根据另一个值的数据类型更改值的数据类型

SELECT CONVERT ( order_date , CURRENT_DATE ) FROM orders;

使用 FORMAT 函数可以将值按照特定的模式进行格式化，这是关于数据的样式

SELECT FORMAT ( order_date , 'YYYY-MM-DD') FROM orders;

1.4 分组和筛选数据操作

这些操作可以帮助数据分析师根据更具体的标准对数据进行精简、汇总或整理。

按客户ID分组并计算总金额和平均金额

SELECT customer_id , SUM ( order_amount ) AS total_amount , AVG ( order_amount ) AS average_amount FROM orders GROUP BY customer_id;

筛选出消费额超过5000美元的客户

SELECT customer_id , SUM ( order_amount ) > 5000;

查看2024年1月份的订单

SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

2 数据集成

数据集成是将来自不同来源的数据合并为一致的数据集的过程。

使用SQL连接和联合来合并数据就像拼接一幅拼图一样。

2.1 SQL连接

SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;

连接订单表和客户表，将每个订单与其对应的客户详细信息一起显示。

2.2 Union vs. Union ALL

UNION：它能去除重复的行，只保留不重复的行。

UNION ALL：保留所有行，包括重复的行。

SELECT * FROM orders UNION SELECT * FROM returns;

显示订单和退货的合并视图，这两者的结构相似。

3 数据转换

数据转换涉及调整数据结构甚至调整实际内容。

3.1 创建新变量

使用以下SQL查询，根据订单金额将每个订单分类为“高”、“中”、“低”或“未知”：

SELECT order_amount,
       CASE WHEN order_amount > 1000 THEN 'High'
            WHEN order_amount BETWEEN 500 AND 1000 THEN 'Medium'
            WHEN order_amount < 500 THEN 'Low'
            ELSE 'Unknown'
       END AS order_level
FROM orders;

3.2 聚合数据

使用SUM、COUNT、AVG、MIN、MAX、AND、GROUP和CONCAT等函数来聚合数据。例如，以下查询按客户ID分组，计算每个客户的订单总金额和平均金额：

SELECT customer_id,
       SUM(order_amount) AS total_amount,
       AVG(order_amount) AS average_amount
FROM orders
GROUP BY customer_id;

3.3 应用统计和数学函数

使用STDDEV、VARIANCE、ROUND、FLOOR和CEIL等函数。例如，以下查询将订单金额精确到小数点后两位：

SELECT ROUND(order_amount, 2) FROM orders;

3.4 排序和排名数据

使用ORDER BY、LIMIT、OFFSET或RANK等函数。例如，以下查询按订单日期降序排列，仅显示前10个订单：

SELECT * FROM order ORDER BY order_date DESC LIMIT 10;