【大数据】精通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;
