十條命令，精通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;
