SQL筆記(0)，撈取與分析資料的基本(系列合輯)

6 min readAug 25, 2022

這是一份關於資料處理基礎的筆記，內容會以 SQL 的基本語法為主。雖然我並不希望侷限於特定的語言或工具，但不得不說，SQL 在撈取資料、處理資料上，確實是個很有結構且優雅的語言。此外，我們也可以很容易地把分析思維與流程對應到 SQL 語法，或是以 SQL 為基礎去理解其他的語言與工具（如：python、Stata，甚至是像 Google Analytics 這樣的 BI 工具）。

處理資料的基礎與 SQL

SQL，又稱 Structure Query Language，是個用於和資料庫互動的程式語言。如果是作為分析師的話，最常接觸的可能還是屬於其中的資料調處語言（DML）中的 SELECT。當然，SQL 的範疇遠遠不止於 SELECT，但就撈取資料、處理資料來說，不外乎就是 SELECT、FROM、JOIN、WHERE、GROUP BY 的組合。以下會簡單地介紹這些基本語法，但不會鉅細靡遺地說明細節：

SELECT t1.<g1>, count(t2.<c2>), sum(t2.<c2>)
FROM <table1> t1
JOIN <table2> t2 on t1.<c1>=t2.<c2>
WHERE <condition>
GROUP BY t1.<g1>

上面這段 SQL 用到了這些常用語法，那麼，這段 SQL 是在做幹什麼呢？其處理資料的步驟、順序可以理解如下：

[FROM] 選取 <table1> 並取暱名（alias）為 t1。
[JOIN] 選取 <table2> 並取暱名（alias）為 t2，並將 t1、t2 分別依欄位 <c1>、<c2> 合併。比如說：會員資料檔和交易檔，可以用會員 ID 和買家 ID 合併。這邊根據需要，會再設定 JOIN 方式（如：LEFT、OUTER 等），用以處理 t1、t2 的配對欄位沒有配對成功時，是否保留紀錄。
[WHERE] 對合併的資料依欄位條件篩選，如：近一個月註冊的會員。
[GROUP BY] 然後，再依照 <g1> 欄位分組彙整。
[SELECT] 表列要彙整的方式與欄位，如：分組依據 <g1>、<c2>非空值的次數、<c2>的加總。

範例一，買家分級

來看個實例吧！假如我們想要統計女性會員，在某購物網站上的交易數據，像是：花了多少錢、買了幾次，資料敘述與 SQL 如下：

SELECT u.ID,
  count(t.T_ID) 交易次數,
  sum(case when t.amount is null then 0 else end) 交易金額
FROM user_registry u
LEFT JOIN transaction t ON u.ID=t.buyer
WHERE u.gender='F'
GROUP BY u.ID

這段在做什麼呢？參考上段介紹 SQL 處理資料的順序，

[FROM] 選取會員資料 user_registry，別名為 u。
[JOIN] 選取交易資料 transaction 並別名為 t，並將會員資料、交易資料分別依各自的欄位 ID、buyer。請注意，我這邊使用 LEFT JOIN 以確保沒有任何交易的會員也會被保留（如：ID=101 的會員）。
[WHERE] 使用性別欄位篩選女性。
[GROUP BY] 再依照會員 ID 欄位分組統計。請注意，若使用交易檔的 buyer_id 的話，沒有交易的人在 LEFT JOIN 後會是空值 NULL 喔。
[SELECT] 依需求顯示要彙整的方式與欄位，如：交易次數 count(t.T_ID)、交易總額 sum(case ...)。另外，這裡的 count() 僅會計算非空值的資料筆數，因此未交易的人會是 0，但為了讓未交易的人的交易總額也是 0，我還在 sum() 裡面加了一層 CASE 作轉換。

以上是使用 SQL 查詢時最基本的語法。說到 SQL 語法，我想到電影「龍紋身的女孩」，也有個用 SQL 查詢的橋段（如上圖），不知道大家是否有讀出這段 query 在幹什麼呢？我看過去之後推測，使用的資料應該有事件主檔 i、事件關鍵字檔 keyword、受害者檔 v，而這段 query 結合這幾份資料，去抓取受害者的事件關鍵字中帶有特定死法、事件介於特定年份間、受害者的姓名為特定開頭的人。

除了這些基本用法外，我把其他常用或相對進階的 SQL 語法整理如下。然而，比起背誦語法（像是叫我們來排排看 WHERE 、GROUP BY、 HAVING、ORDER BY ，嘻嘻），重要還是分析思維：想法先行，再去問問 SQL 能提供什麼服務。

視窗函數

我其實覺得視窗函數（window function）是個令人不知所措的名詞，但簡言之就是幫資料分組，然後做些群組別的操作。等等，那這和 GROUP BY 有什麼不同呢？

GROUP BY 後資料的單位或粒度是 group by 的欄位；而視窗函數則是在原資料上產生以視窗函數的組別 PARTITION BY 為依據的欄位。
有些任務幾乎是要靠視窗函數才能完成：像是組內排名、累計加總、Panel Data 中的前 N 期與後 N 期。

關於這題視窗函數的例子，可以參考：

SQL 筆記(1)，視窗函數的應用 — 排名與累計加總

條件彙整

條件彙整可以讓你做出更複雜的群組別統計，像是：每位學生及格的修課數、有設上下限的平均成交金額等等。這個實用的資料處理使用了 CASE 搭配 GROUP BY 或是視窗函數。可以參考：

SQL 筆記(2)，用CASE做條件彙整，以計算及格次數為例

應對各種資料

有時後我們之所以需要一些奇葩的操作，正是因為原本的資料就長得很奇葩，像是以「一段期間」為單位的資料、以「更動」為單位的 log 資料等。要能讓這些不同單位的資料彼此互動與結合，就需要一些額外的工處理。可以參考：

其他語法 MurMur

最後羅列一些有時也會使用到的語法（有空會陸續整理）

pivot — 對，就是樞紐分析，可以理解為 GROUP BY 的變體。相關用法可以參考這篇：SQL筆記(4)，用PIVOT做樞紐分析，以留存分析為例。
having — 用來對彙整 GROUP BY 後的資料做篩選。相較之下， WHERE 是對彙整前的資料篩選。
like — 用來匹配文字 pattern，找一下應該也會有 regular expression 的版本。