數(shù)據(jù)準(zhǔn)備是數(shù)據(jù)分析師工作中非常重要的一部分。在進(jìn)行數(shù)據(jù)分析之前，必須對(duì)數(shù)據(jù)進(jìn)行準(zhǔn)備，以確保數(shù)據(jù)呈現(xiàn)出自己和其他人都能理解的格式，從而為后續(xù)工作鋪平道路。

實(shí)際上，數(shù)據(jù)準(zhǔn)備階段通常占據(jù)了整個(gè)數(shù)據(jù)分析工作的80%多的的工作量。這個(gè)階段的任務(wù)是整理和清理數(shù)據(jù)，以確保其質(zhì)量和一致性，并進(jìn)行必要的數(shù)據(jù)集成和轉(zhuǎn)換，以滿足分析需求。

1 數(shù)據(jù)清洗

數(shù)據(jù)清洗是將數(shù)據(jù)格式化并符合要求，通過刪除或修復(fù)不正確或不一致的部分來實(shí)現(xiàn)。

以下是其中的常見任務(wù)：

1.1 消除重復(fù)項(xiàng)

獲取唯一的記錄

SELECT DISTINCT order_id FROM orders;

獲取該屬性的第一條唯一記錄

SELECT DISTINCT ON ( customer_id ) * FROM orders;

1.2 處理缺失值

選擇列表中第一個(gè)非空值

SELECT COALESCE ( order_date , CURRENT_DATE ) FROM orders;

訂單金額為 0 時(shí)應(yīng)視為無效

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  標(biāo)準(zhǔn)化不匹配的數(shù)據(jù)類型

使用 CAST 函數(shù)可以直接轉(zhuǎn)換數(shù)據(jù)類型，就像直接處理數(shù)據(jù)一樣。

如果訂單金額是數(shù)字，但需要它作為整數(shù)，CAST 可以實(shí)現(xiàn)這一點(diǎn)。

SELECT CAST ( order_amount AS INTEGER ) FROM orders;

使用 CONVERT 函數(shù)可以間接地根據(jù)另一個(gè)值的數(shù)據(jù)類型更改值的數(shù)據(jù)類型

SELECT CONVERT ( order_date , CURRENT_DATE ) FROM orders;

使用 FORMAT 函數(shù)可以將值按照特定的模式進(jìn)行格式化，這是關(guān)于數(shù)據(jù)的樣式

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

1.4 分組和篩選數(shù)據(jù)操作

這些操作可以幫助數(shù)據(jù)分析師根據(jù)更具體的標(biāo)準(zhǔn)對(duì)數(shù)據(jù)進(jìn)行精簡、匯總或整理。

按客戶ID分組并計(jì)算總金額和平均金額

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

篩選出消費(fèi)額超過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 數(shù)據(jù)集成

數(shù)據(jù)集成是將來自不同來源的數(shù)據(jù)合并為一致的數(shù)據(jù)集的過程。

使用SQL連接和聯(lián)合來合并數(shù)據(jù)就像拼接一幅拼圖一樣。

2.1 SQL連接

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

連接訂單表和客戶表，將每個(gè)訂單與其對(duì)應(yīng)的客戶詳細(xì)信息一起顯示。

2.2 Union vs. Union ALL

UNION：它能去除重復(fù)的行，只保留不重復(fù)的行。

UNION ALL：保留所有行，包括重復(fù)的行。

SELECT * FROM orders UNION SELECT * FROM returns;

顯示訂單和退貨的合并視圖，這兩者的結(jié)構(gòu)相似。

3 數(shù)據(jù)轉(zhuǎn)換

數(shù)據(jù)轉(zhuǎn)換涉及調(diào)整數(shù)據(jù)結(jié)構(gòu)甚至調(diào)整實(shí)際內(nèi)容。

3.1 創(chuàng)建新變量

使用以下SQL查詢，根據(jù)訂單金額將每個(gè)訂單分類為“高”、“中”、“低”或“未知”：

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 聚合數(shù)據(jù)

使用SUM、COUNT、AVG、MIN、MAX、AND、GROUP和CONCAT等函數(shù)來聚合數(shù)據(jù)。例如，以下查詢按客戶ID分組，計(jì)算每個(gè)客戶的訂單總金額和平均金額：

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

3.3 應(yīng)用統(tǒng)計(jì)和數(shù)學(xué)函數(shù)

使用STDDEV、VARIANCE、ROUND、FLOOR和CEIL等函數(shù)。例如，以下查詢將訂單金額精確到小數(shù)點(diǎn)后兩位：

SELECT ROUND(order_amount, 2) FROM orders;

3.4 排序和排名數(shù)據(jù)

使用ORDER BY、LIMIT、OFFSET或RANK等函數(shù)。例如，以下查詢按訂單日期降序排列，僅顯示前10個(gè)訂單：

SELECT * FROM order ORDER BY order_date DESC LIMIT 10;