[SQL 入門與面試實戰系列] 4. SQL 技巧新解鎖：掌握子查詢與 CASE WHEN

歡迎再次來到 [SQL 入門與面試實戰系列]！

掌握了基本查詢語法、WHERE 篩選條件，以及使用 GROUP BY 搭配聚合函數進行細緻化分析，我們已經能夠對單一資料表進行深入的數據挖掘。而當需要將不同資料表中的資料結合，進行更全面的查詢和分析時，我們也掌握 JOIN，搭配 LEFT JOIN 和 INNER JOIN 連接多張資料表來豐富我們的分析維度。

現在，我們將進入 [SQL 入門與面試實戰系列] 的第四章！這一章將進一步探索 SQL 的進階功能，包括子查詢以及 CASE WHEN 的使用方法。這些功能將使我們能夠更靈活、更高效地處理和分析數據。

[SQL 入門與面試實戰系列] 4_子查詢與 CASE WHEN.ipynb

• 子查詢的概念與應用
• CASE WHEN

• 子查詢的概念與應用

我們在過去章節中查詢資料表或是連接資料表時，都是直接使用 chinook.db 現成的資料表。不過，有時候我們需要的資料並不直接存在於任何一個表中，或者需要的是經過特定計算或過濾條件的結果。這時，我們可以自己創造臨時資料表！簡單來說，當我們在寫 SELECT … FROM… 查詢時，其實也是在創建一張資料表，這也就是子查詢 (Subquery) 的概念。

之所以叫「子」查詢，其實就是在查詢語法內，再放入另一個查詢，就像是在查詢中創建了一張臨時的資料表。當我們執行含有子查詢的指令時，SQL 會先處理括號內的子查詢部分，將其結果暫時存儲，然後再將這個結果用於外層的查詢中，完成整個查詢的過程。

讓我們使用熟悉的 `customers` 表與 `invoices` 表為例，假設我們想要搜尋來自美國的顧客訂單紀錄。我們的思考方式可以是使用 INNER JOIN 將 `invoices` 與 `customers` 表連接，再使用 WHERE 子句進行篩選美國顧客：

SELECT invoices.InvoiceId,
  customers.FirstName,
  customers.Country 
FROM invoices
INNER JOIN customers 
 ON invoices.CustomerId = customers.CustomerId
WHERE customers.Country = 'USA'

也可以換另一種思考方式，先對 `customers` 表中的顧客進行篩選，再將這個結果和 `invoices` 表進行連接。此時我們可以使用子查詢，先建立一個只包含美國顧客的臨時表格 `customers_usa` ，再進行連接操作：

SELECT invoices.InvoiceId,
  customers_usa.FirstName,
  customers_usa.Country 
FROM invoices
INNER JOIN 
(
 SELECT CustomerId, FirstName, Country 
 FROM customers 
 WHERE Country = 'USA'
) AS customers_usa
ON invoices.CustomerId = customers_usa.CustomerId

透過子查詢，我們可以更加靈活查詢數據，將既有的資料表進行篩選條件或聚合運算，再進行連接或主查詢的動作。再來看一個例子，假設我們想找出購買總金額在 40 元以上的顧客資料，我們可以先對 `invoices` 表，使用 GROUP BY 進行分組與聚合運算，再將此結果與 `customers` 表連接：

SELECT customers.CustomerId,
 customers.Firstname,
 customers_vip.CustomerTotal
FROM customers
INNER JOIN 
(
 SELECT CustomerId, Sum(Total) AS CustomerTotal
 FROM invoices
 GROUP BY CustomerId
 HAVING CustomerTotal >= 40
) AS customers_vip
ON customers.CustomerId = customers_vip.CustomerId

這段語法透過子查詢，先計算出每位顧客的購買總金額，並篩選出總金額超過 40 元的顧客；這個臨時生成的結果集再被用來與 `customers` 表進行連接，從而獲得符合條件的顧客資訊。

子查詢除了放在 FROM 和 JOIN 子句中，作為臨時資料表的來源，它也可以放在 WHERE 子句中！以前面的例子，我們也可以查詢 `customers` 表，使用 WHERE 子句搭配子查詢，直接篩選出購買總金額在 40 元以上的顧客：

SELECT customers.CustomerId,
 customers.Firstname
FROM customers
WHERE CustomerId IN (
  SELECT CustomerId
  FROM invoices
  GROUP BY CustomerId
  HAVING Sum(Total) >= 40
)

這個查詢先篩選出所有購買總金額超過 40 元的顧客的 `CustomerId`，然後從 `customers` 表中選出這些顧客的詳細資訊。我們直接針對目標顧客進行查詢，沒有進行連接的操作。

以上，就是子查詢的應用！它讓我們可以根據分析需求，對資料表先進行篩選與聚合運算，然後再進行連接或主查詢操作。不過隨著問題問題的複雜性增加，我們可能會發現自己越來越頻繁地使用子查詢和連接多張表，導致整個查詢語句充斥著多重 SELECT…FROM… 的結構，對閱讀和解析造成困擾。為了解決這個問題，我們可以使用 WITH 語法，來建立整個查詢的「暫存資料表」。

WITH 語法允許我們在查詢的開頭，指定一個或多個臨時的資料集，並且這些資料集在整個查詢執行過程中都可被引用。簡單說我們先將後續查詢會使用到的子查詢暫存表寫在開頭。這樣做的好處是大幅提升了查詢的清晰度和可讀性，尤其在處理複雜查詢時更易於維護和理解。(補充：嚴格來說暫存資料表不是子查詢，但概念與子查詢相似，都是臨時創建的資料集)

一樣以找出所有購買總金額超過 40 元的顧客資料為例。我們在開頭使用 WITH 定義一個名為 `customers_vip` 的暫存資料表，計算每位顧客的購買總金額與篩選出滿足條件的顧客。寫法如下：

WITH customers_vip AS (
  SELECT CustomerId, SUM(Total) AS TotalSales
  FROM invoices
  GROUP BY CustomerId
  HAVING SUM(Total) > 40
)

SELECT customers.CustomerId,
  customers.FirstName,
  customers_vip.TotalSales
FROM customers
INNER JOIN customers_vip
  ON customers.CustomerId = customers_vip.CustomerId

我們將此前撰寫在子查詢的語句，放到 WITH 語句並將其命名。之後我們一樣透過 INNER JOIN，將 `customers` 表與暫存資料表 `customers_vip` 連接，從而獲取符合條件的顧客資料。

總的來說，當面對不太複雜的情況時，直接使用子查詢是完全沒問題的。但如果整個查詢會包含多張表連接或多個子查詢生成的臨時表時，這時建議可以在查詢開頭通過 WITH 語法定義暫存資料表，這會讓整個查詢結構更加簡潔且易於管理。

• CASE WHEN

當我們想對查詢表的某些欄位數值進行分類、標記或者進行計算，同時又不希望修改原始資料表，這時就可以使用 CASE WHEN 語句。CASE WHEN 是一個條件表達式，讓我們根據設定的條件，對數據進行轉換或分類。(類似於 Excel 的 IF 函數)

CASE WHEN 的基本語法如下：

SELECT
 CASE
     WHEN "欄位" = '條件1' THEN '結果1'
     WHEN "欄位" = '條件2' THEN '結果2'
     ELSE '默認結果'
 END AS "新欄位名"
FROM "資料表名"

CASE WHEN 讓我們可以對欄位進行一個或多個條件的判斷，並根據條件的滿足與否返回不同的結果。如果沒有任何條件匹配，則返回 ELSE 部分指定的默認結果；而如果省略了 ELSE 部分 (沒有默認結果)，欄位值又沒有條件匹配，則會返回空值。最後再加上 END 並為其命名。

讓我們從簡單的練習開始。我們使用的 chinook.db 是一間美國唱片公司的相關資料，假設我們想根據顧客的國籍進行分類：若顧客來自美國則標記為 'Domestic'；否則標記為 'Foreign'。以下透過 CASE WHEN 來實現：

SELECT CustomerId, FirstName, Country,
  CASE 
    WHEN Country = 'USA' THEN 'Domestic'
    ELSE 'Foreign'
  END AS CustomerType
FROM customers

首先我們先判斷顧客是否來自美國。如果第一個條件不成立 (顧客不是來自美國)，則在 ELSE 部分將其分類為 'Foreign'。

進一步，我們也可以設定多個條件。比如我們想根據顧客所在的州和國家進行分類：若顧客來自美國加州 (CA)，則標註為 'Local Customer'；來自美國其他州為 'National Customer'；若不是來自美國則標註為 'Foreign'：

SELECT CustomerId, FirstName, State, Country,
  CASE
    WHEN State = 'CA' AND Country = 'USA' THEN 'Local Customer'
    WHEN State <> 'CA' AND Country = 'USA' THEN 'National Customer'
    ELSE 'Foreign'
 END AS CustomerType
FROM customers

CASE WHEN 也可以針對聚合後的數據進行分組。假設我們想根據顧客的總消費額對顧客進行分類，若顧客總消費達到 40 元以上則為 'VIP'，沒達到的顧客則為 'Normal'：

SELECT CustomerId, SUM(Total) AS TotalSales,
  CASE 
    WHEN SUM(Total) < 40 THEN 'Normal'
    WHEN SUM(Total) >= 40 THEN 'VIP'
    ELSE 'Undefined'
  END AS CustomerLevel
FROM invoices
GROUP BY CustomerId

CASE WHEN 所建立的欄位和普通的欄位一樣，也可以放在 WHERE 或 HAVING 設定條件的子句中使用。比如篩選出 VIP 的顧客： (補充：某些 SQL 環境不允許在直接在 WHERE 或 HAVING 子句中直接使用 CASE WHEN 創建的別名，這時就要直接寫出聚合函數或條件語句)

SELECT CustomerId, SUM(Total) AS TotalSales,
  CASE 
    WHEN SUM(Total) < 40 THEN 'Normal'
    WHEN SUM(Total) >= 40 THEN 'VIP'
    ELSE 'Undefined'
  END AS CustomerLevel
FROM invoices
GROUP BY CustomerId
HAVING CustomerLevel = 'VIP'

再補充一個絕招，我們還可以將 CASE WHEN 放在聚合函數裡面，在計算某些指標時進行條件篩選。例如我們想知道來自美國的顧客數量，我們可以使用之前所學的 COUNT，並且搭配 CASE WHEN 來進行條件設定：

SELECT 
  COUNT(DISTINCT CASE WHEN Country = 'USA' THEN CustomerId END) AS UsaCustomerCnt
FROM customers

在這個查詢中，CASE WHEN 被作為條件設定用於 COUNT 函數之內，只計算滿足條件（即國籍為美國）的顧客ID。這樣寫讓整個查詢語句非常簡潔。

最後讓我們結合前面所學的子查詢，挑戰比較有難度的問題。假設我們想要算出 VIP 與普通顧客的人數，大家可以想一下如何寫出查詢：

SELECT
  CustomerLevel,
  COUNT(DISTINCT CustomerId) AS CustomerCnt
FROM
(
  SELECT
    CustomerId,
    CASE 
      WHEN SUM(Total) < 40 THEN 'Normal'
      WHEN SUM(Total) >= 40 THEN 'VIP'
      ELSE 'Undefined'
    END AS CustomerLevel
  FROM invoices
  GROUP BY CustomerId
) AS SubQuery
GROUP BY CustomerLevel

想法可以是：先使用 GROUP BY 分組算出每一位顧客的總消費，並且使用 CASE WHEN 進行顧客級別的分類。之後我們將這個查詢作為子查詢，再使用 GROUP BY 對顧客級別分組，搭配 COUNT 來計算分級的顧人數。

以上，就是 CASE WHEN 的基本概念。當我們在使用 CASE WHEN 時，要注意以下事項：

1.記得設定條件不能有重複的狀況，尤其我們對連續數字進行多組分類的時候，要確保不同條件的範圍不要重疊，以避免同一條數據符合多個條件，從而導致分類結果不準確。(CASE WHEN 還是會運作，會按照條件寫的順序評估每個條件，只要匹配條件分類結果就不會變動)

-- 錯誤的寫法：條件範圍重疊
-- SUM(Total) >= 45 的顧客會先被分類到 'VIP'，即使符合下一個條件分類也不會變動
CASE 
 WHEN SUM(Total) < 40 THEN 'Normal'
 WHEN SUM(Total) >= 40 THEN 'VIP'
 WHEN SUM(Total) >= 45 THEN 'VVIP'
 ELSE 'Undefined'
END AS CustomerLevel

-- 正確的寫法：條件範圍明確且互斥，每個條件區間都是不重疊的
CASE 
 WHEN SUM(Total) < 40 THEN 'Normal'
 WHEN SUM(Total) >= 40 AND SUM(Total) < 45 THEN 'VIP'
 WHEN SUM(Total) >= 45 THEN 'VVIP'
 ELSE 'Undefined'
END AS CustomerLevel

2.預設值 ELSE 可以不寫，讓沒有匹配條件的值直接返回空值。不過實際上資料表中的數據可能很不乾淨，會有缺失值、甚至是格式不相同的狀況。所以通常還是建議提供一個預設值，來涵蓋所有未明確定義的情況。

3.不要將 CASE WHEN 跟 WHERE 子句搞混了。雖然兩者的寫法都是針對欄位的數值撰寫條件，但 WHERE 條件是用來篩選資料行，而 CASE WHEN 只是在已選定的資料集上進行條件判斷和數值轉換等操作。 (白話說 CASE WHEN 是放在選取欄位的地方，WHERE 是放在 FROM 資料表後面)

讓我們複習一下這章所學到的內容：

1. 子查詢：我們可以在查詢語法內再放入查詢，以更符合我們的查詢需求。我們主要將子查詢應用在 JOIN 連接的資料表、以及放在 WHERE 子句作為條件設定。此外，如果查詢語法中會用到太多子查詢，建議在查詢開頭透過 WITH 創建暫存表。
2. CASE WHEN：將欄位數據根據設定的條件進行值轉換。我們主要透過 CASE WHEN 對數據進行分類與在聚合函數中作為條件判斷。

至此，我們已經掌握所有 SQL 查詢語法的關鍵要素了。最後，讓我們了解 SQL 查詢的執行順序，幫助我們更清楚解題時如何撰寫查詢的各個部分。

1. WITH 子句 (暫存資料表)： SQL 首先處理 WITH 子句，定義一個或多個暫存資料表。這些暫存資料表在後面撰寫的查詢可以被重複使用。
2. FROM 子句 (讀取數據源)：接著從指定的資料表中讀取數據，包括進行 JOIN 操作來合併資料表。
3. WHERE 子句 (篩選資料)： 透過 WHERE 子句對數據源的資料行進行篩選。
4. GROUP BY 子句 (資料分組)：如果指定了 GROUP BY，則在 WHERE 子句之後對數據進行分組。
5. HAVING 子句 (分組後的篩選)：HAVING 子句對 GROUP BY 生成的分組進行條件篩選。
6. SELECT 子句 (選取欄位與計算)： 決定返回哪些欄位，以及進行相關的計算，以確定最終顯示的欄位和值。包括使用 CASE WHEN 對欄位值進行條件轉換，以及聚合函數計算數值。
7. ORDER BY子句 (結果排序)： 最後進行的是 ORDER BY 子句，根據指定欄位名對查詢結果進行排序。

讓我們用以下例子的查詢語法，來實際了解 SQL 執行流程。假設我們想找出有哪些國家有超過 2 位的高價值顧客，並且留有信箱能夠進行行銷：

-- 1. 先讀取 WITH 子句，建立消費超過 40 的 VIP 暫存資料表
WITH customers_vip AS (
  SELECT CustomerId, SUM(Total) AS TotalSales
  FROM invoices
  GROUP BY CustomerId
  HAVING SUM(Total) > 40
)

-- 6. 選取需要的欄位與計算每個國家的 VIP 顧客數量
SELECT customers.Country,
  COUNT(DISTINCT customers.CustomerId) AS VipCustomerCnt
-- 2. 接著指定資料表與進行 JOIN 操作，讓 customers 表與 vip 表連接
FROM customers
INNER JOIN customers_vip
  ON customers.CustomerId = customers_vip.CustomerId
-- 3-5. 對資料表進行篩選與分組
WHERE Email IS NOT NULL  -- 篩選出有有效電子郵件地址的顧客
GROUP BY Country  -- 按國家分組顧客，為了能夠計算出每個國家的 VIP 數量。
HAVING VipCustomerCnt >= 2  -- 進一步篩選出那些有至少 2 位 VIP 的國家
-- 7. 對結果根據 VIP 顧客的數量進行排序
ORDER BY VipCustomerCnt DESC

簡單說，SQL 就是從資料表 (第 1~5 的步驟)，選取需要的欄位 (第 6 個步驟) 進行排序 (第 7 個步驟)。

恭喜各位完成了第四章！是不是已經對 SQL 駕輕就熟了？下一章我們將學習針對字串、時間與數值的函數，讓我們能夠對資料值進行更細緻和高級的操作！