[SQL 入門與面試實戰系列] 6. Window Functions 與排名技巧攻略

我們來到了 [SQL 入門與面試實戰系列] 第六章，也是介紹 SQL 基本功能的壓軸篇章！

通過前五章的學習，我們已經具備足夠的技能，來應對大部分的數據分析和面試題目 (覆蓋 90% 的常見情景)。然而在面對排行相關的問題，比如要找出每月消費最多的前三名顧客，使用我們目前所學的子查詢和條件語句會讓整個查詢非常冗長，而本章要探討的 Window Functions (窗口函數) 能夠簡潔地解決這類問題。準備好就讓我們看下去！

[SQL 入門與面試實戰系列] 6_Window Functions.ipynb

• Window Functions 的基本概念
• 排名函數：RANK( )、DENSE_RANK( )、ROW_NUMBER( )

• Window Functions 的基本概念

我們在第二章時學會使用 GROUP BY 對資料進行分組，再搭配聚合函數進行分組計算。比如對 `invoices` 表的顧客編號分組，計算每位顧客的訂單數、總消費額等。然而使用 GROUP BY 以後，原始資料的結構便會會因為分組而改變，資料行數發生變動。而 Window Functions 則允許我們對分組資料進行計算的同時，保持原始資料的行數不變。

讓我們先來看 Window Functions 的基本語法：

SELECT
  "聚合函數"("計算欄位") OVER (PARTITION BY "分組欄位" ORDER BY "排序欄位")
FROM "資料表"

# 聚合函數：每個分組進行計算使用的函數，比如 SUM、AVG、RANK 等。
# OVER 子句：設定 Window Functions 的作用範圍。
# PARTITION BY 子句：按照哪個欄位來分組。可以不設定。
# ORDER BY 子句：在每個分組內，按照哪個欄位來排序。可以不設定。

比如想要看 invoices 表中每一位顧客的總消費額。除了使用我們熟悉的 GROUP BY：

SELECT CustomerId, SUM(Total) AS CustomerTotal
FROM invoices
GROUP BY CustomerId

在 GROUP BY 子句中，指定 CustomerId 作為分組欄位，並使用 SUM(Total) 對分組資料 (即每位顧客的每筆訂單消費金額) 加總。

如果我們使用 Window Functions，則是這樣寫：

SELECT
  CustomerId,
  InvoiceId,
  InvoiceDate,
  Total,
  SUM(Total) OVER (PARTITION BY CustomerId) AS CustomerTotal
FROM invoices

在 PARTITION BY 子句中，指定 `CustomerId` 作為分組欄位，並使用 SUM(Total) 計算每個顧客的總消費額，並創造一個新欄位 `CustomerTotal` 。我們不僅計算了每位顧客的總消費額，還保留了原始每筆訂單的信息，如 `InvoiceId` 、`InvoiceDate` 和 `Total` 。

讓我們練習另一個例子。這次我們計算每位顧客在不同年份的平均訂單金額，我們先使用熟悉的 GROUP BY 方式：

SELECT 
  CustomerId, 
  strftime('%Y', InvoiceDate) AS `Year`,
  AVG(Total) AS CustomerAvgSales
FROM invoices
GROUP BY CustomerId, `Year`

再來使用 Window Functions 達成：

SELECT
  CustomerId,
  InvoiceId,
  InvoiceDate,
  Total,
  AVG(Total) OVER (PARTITION BY CustomerId, strftime('%Y', InvoiceDate)) AS CustomerYearAvgSales
FROM invoices

Window Functions 與 GROUP BY 同樣都是對資料進行分組 (將 GROUP BY 指定的分組欄位放到 PARTITION BY 子句)，再去進行相關的計算。兩者最顯著的區別是，Window Functions 不會改變原始查詢資料表的行數，而 GROUP BY 則會則會按照指定的分組欄位，減少原始資料表的行數。

使用 Window Functions 保持資料完整性，也意味我們可以獲得更多資訊，比如計算得到每位顧客的消費總和，同時保留了每筆訂單的獨立紀錄與其他欄位。此外，Window Functions 讓我們可以在單一查詢中，使用多個窗口函數來完成多項計算，就不需撰寫複雜的子查詢。例如我們想要同時知道每位顧客的總消費額，以及每年的總消費額：

SELECT
  CustomerId,
  InvoiceId,
  InvoiceDate,
  Total,
  SUM(Total) OVER (PARTITION BY CustomerId) AS CustomerTotalSales,
  SUM(Total) OVER (PARTITION BY CustomerId, strftime('%Y', InvoiceDate)) AS CustomerYearSales
FROM invoices

以上，就是 Window Functions 的基本概念。

• 排名函數：RANK( )、DENSE_RANK( )、ROW_NUMBER( )

Window Functions 要展現真正威力，其實是當我們使用 RANK( ) 以及相關的排名函數來解決排序相關的問題！例如我們在使用 Window Functions 將顧客進行分組後，想要針對每位顧客的每筆訂單銷售額進行排名，我們可以這樣寫：

SELECT
  CustomerId,
  InvoiceId,
  InvoiceDate,
  Total,
  RANK() OVER (PARTITION BY CustomerId ORDER BY Total) AS CustomerSalesRank
FROM invoices

我們在聚合函數的位置放上 RANK( )，並在 OVER( ) 子句中加上 ORDER BY Total，這樣就可以在每個分組內以 Total 欄位進行排序，SQL 將在新的欄位 `CustomerSalesRank`，依照排序結果給予每筆訂單相應的排名。

我們進一步將會員編號為 1 的顧客單獨出來觀察，這次按照銷售額由大到小排序：

SELECT
  CustomerId,
  InvoiceId,
  InvoiceDate,
  Total,
  RANK() OVER (PARTITION BY CustomerId ORDER BY Total DESC) AS CustomerSalesRank
FROM invoices
WHERE CustomerId = 1

我們在 ORDER BY 子句部分，加上 DESC 來實現由大到小排序 (預設是由小到大)。我們可以看到組內訂單按照銷售額，由大到小進行排序，並且為每筆訂單給予排名。

而排名的方式也有所不同，以下介紹幾個不同的排名函數：

1.RANK( )
當遇到相同的排序值時，RANK( ) 會給予相同的排名，也就是並列排名；而相同排名之後的下一個排名會「跳號」。例如有兩個值並列排名第一，下一個排名會是第三。

2.DENSE_RANK( )
和 RANK( ) 一樣，會給相同的排序值相同的排名；但是下一個排序值的排名不會「跳號」，排名是連續的。例如有兩個值並列排名第一，下一個排名將是第二。

3.ROW_NUMBER( )
每一行資料會獲得不同的排名，即使排序的值相同。ROW_NUMBER( ) 的排名是根據排序條件的順序來進行連續編號的。

讓我們實際看一個例子。我們找出顧客編號為 2 的顧客，並且分別使用三種不同的排名方式，觀察三者的差別，特別是針對兩筆訂單金額相同 (1.98) 的處理方式：

SELECT
  CustomerId,
  InvoiceId,
  InvoiceDate,
  Total,
  RANK() OVER (PARTITION BY CustomerId ORDER BY Total DESC) AS SalesRank,
  DENSE_RANK() OVER (PARTITION BY CustomerId ORDER BY Total DESC) AS SalesDenseRank,
  ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY Total DESC) AS SalesRowNumber
FROM invoices
WHERE CustomerId = 2

RANK( ) 函數將這兩筆金額相同的訂單都給予第五名，並在下一筆金額較低的訂單 (0.99) 跳號為第七名；而 DENSE_RANK( ) 函數一樣給予並列第五名，但下一筆訂單為第六名，排名是連續的；ROW_NUMBER( ) 函數則每筆訂單都給予不同的排名，兩筆金額為 1.98 的訂單被賦予了第五名和第六名，排名的分配是基於訂單在原本數據集中的出現順序 (按照訂單編號)。

這就是 Window Functions 真正厲害的地方！在處理排名相關的問題，如果我們使用 MAX( ) 或 MIN( ) 等聚合函數，只能獲得第一名或最後一名，而 Window Functions 搭配排名函數，能夠輕鬆地找出任意排名的紀錄。讓我們試著解決章節開頭的問題，找出每個月總消費金額前三多的顧客。大家可以想一下怎麼寫：

WITH MonthlySales AS (
  SELECT
    strftime('%Y-%m', InvoiceDate) AS YM,
    CustomerId,
    SUM(Total) AS CustomerMonthSales
  FROM invoices
  GROUP BY YM, CustomerId
)

SELECT * FROM
(
  SELECT *, 
    RANK() OVER(PARTITION BY YM ORDER BY CustomerMonthSales DESC) AS Rank
  FROM MonthlySales
)
WHERE Rank <= 3

我們先建立一個暫存表，計算每個月每位顧客的總銷售額；接著使用 RANK( ) OVER 對銷售額進行降序排名；最後透過 WHERE 子句篩選每個月的銷售前三名顧客。

最後再試一題！我們試著將 `invoices` 表連接 `invoice_items` 和 `tracks` 表，並找出每一年銷售額前五名的商品：

WITH YearlySales AS (
  SELECT
    strftime('%Y', InvoiceDate) AS `Year`,
    tracks.Name AS TrackName,
    tracks.Composer,
    SUM(Total) AS TrackYearSales
 FROM invoices
 INNER JOIN invoice_items ON invoices.InvoiceId = invoice_items.InvoiceId
 INNER JOIN tracks ON invoice_items.TrackId = tracks.TrackId
 GROUP BY `Year`, TrackName
)

SELECT * FROM
(
  SELECT *, 
    RANK() OVER(PARTITION BY `Year` ORDER BY TrackYearSales DESC) AS Rank
  FROM YearlySales
)
WHERE Rank <= 5

以上，就是排名函數的相關應用！

讓我們複習本章的內容：

1. Window Functions 的基本概念：在維持原本資料表架構的情況下進行分組計算，讓我們可以同時獲得更多資訊細節。
2. 排名函數：我們可以使用 RANK( )、DENSE_RANK( ) 等函數，對分組數據進行排序並給予排名。這對解決排名相關的問題相當好用。

雖然 Window Functions 看起來很潮，但還是提醒大家要合理選擇使用場景，比如遇到排名相關的問題再使用就好。在許多情況下，簡單直接的方法就足夠了，資訊太多反而會造成對資料理解的困難。比如我們只是想知道顧客的總銷售額，使用 GROUP BY 獲取資訊就好，沒有必要使用窗格函數裝逼。

終於，我們完成了 [SQL 入門與面試實戰系列] 基本功能的學習！！！讓我們回顧一下所有學到的東西：

1. SELECT 查詢 + WHERE：基本的查詢語法，以及透過 WHERE 對資料進行篩選
2. GROUP + 聚合函數：用 GROUP BY 對資料進行分組並進行計算
3. JOIN：將資料表進行連接，獲得更多資訊
4. 子查詢和 CASE WHEN：透過子查詢來自定義查詢表；以及使用 CASE WHEN 對欄位值判斷分類
5. 字串、時間、數字函數：處理各種形式資料的相關函數
6. Window Functions：使用 RANK( ) 對分組資料進行排序

恭喜各位學會了 SQL！接下來我們將探討 SQL 的解題技巧，幫助大家在面試時過關斬將！！