【第34天】SQL進階-SQL高級技巧-Window Funtion（SQL 小虛竹)

在 SQL 數據庫操作中，窗口函數 (Window Functions) 提供了一套強大的技術來處理查詢結果集中的行之間的關係。與傳統的聚合函數相比，窗口函數可以在保持每行的數據完整性的同時進行分組計算。本文將介紹 SQL 中常見的窗口函數及其應用場景。

什麼是 Window Function？

窗口函數允許您在不實際對錶或視圖的各行進行分組的情況下執行分析功能。它們通過定義一個“窗口”來實現這一點，該窗口通常基於排序後的行集合，並在每個窗口上應用特定的統計函數。

例如，假設我們有以下名爲 `sales` 的表，它包含了某商店的銷售記錄：

+--------+------------------+-----------+------------+-----------+
| OrderID | CustomerName     | SalesDate  | TotalSales | ProfitMargin |
+--------+------------------+-----------+------------+-----------+
|      1 | John Doe         | 2021-01-01 |    500.00  |   200.00  |
|      2 | Jane Smith       | 2021-01-08 |    300.00  |   150.00  |
|      3 | Michael Lee      | 2021-01-15 |    700.00  |   350.00  |
|      4 | Emily Chen       | 2021-01-22 |    600.00  |   300.00  |
|      5 | David Brown      | 2021-01-29 |    400.00  |   200.00  |
+--------+------------------+-----------+------------+-----------+

我們可以使用窗口函數來計算每個訂單的利潤佔其前一天所有訂單總利潤的比例：

SELECT OrderID,
CustomerName,
SalesDate,
TotalSales,
ProfitMargin,
RANK() OVER(ORDER BY TotalSales DESC) AS "Rank by Total Sales",
LAG(ProfitMargin) OVER(ORDER BY SalesDate) AS "Previous Day's Profit Margin",
CAST((ProfitMargin / LAG(SUM(ProfitMargin)) OVER(ORDER BY SalesDate)) * 100 AS DECIMAL(10, 2)) AS "Current Profit % of Previous Day's Totals"
FROM sales;

這段代碼使用了幾個不同的窗口函數：

• `OVER()`子句用於指定窗口的定義方式。在我們的例子中，我們按照 `SalesDate` 升序排列，以便每一行都與前面的日期相關聯。

• `RANK()` 函數爲每個訂單的總銷售額分配了一個排名。

• `LAG()` 函數返回當前行之前的數據值（在本例中爲前一天的利潤率）。

• `SUM()` 和 `CAST()` 函數用於計算當前利潤佔總利潤百分比。

請注意，窗口函數的結果會直接嵌入到輸出列中，而不是像傳統聚合那樣替換掉原始行。這種能力使得窗口函數特別適合於複雜的分析和比較任務。

常見窗口函數列表

以下是一些常用的 SQL 窗口函數：

1. ROW_NUMBER(): 爲每個分組的行分配一個連續編號。

2. RANK() 和 DENSE_RANK(): 根據特定條件對行進行排名。如果有多行具有相同的分數，則這些行將共享同一個排名（對於 RANK()）或者相鄰排名之間沒有間隙（對於 DENSE_RANK()）。

3. LEAD() 和 LAG(): 分別返回當前行之後的或者之前的某一行中的值。

4. FIRST_VALUE() 和 LAST_VALUE(): 分別返回同一組內的第一行或者最後一行的值。

5. NTH_VALUE(): 返回同一組內的第 n 行中的值。

6. PERCENT_RANK(): 返回行在其分佈中的百分位排名。

7. CEIL() 和 FLOOR(): 對窗口中的位置進行四捨五入向上取整或向下取整。

8. CUME_DIST(): 返回當前行在其所在的分組中高於當前行的值的百分比。

9. NTILE(): 將結果集分爲指定的數量相等的組。

應用案例

窗口函數可以用來實現許多實用的業務需求，如：

1. Top N 問題

你可以使用 `ROW_NUMBER()` 函數來解決這個問題，比如找出每個客戶的前三大最暢銷產品：

WITH CTE AS (
SELECT ProductID,
CustomerID,
SUM(QuantityOrdered) AS SumQty,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY SUM(QuantityOrdered) DESC) AS RowNum
FROM OrderDetails
GROUP BY ProductID, CustomerID
)
SELECT ProductID, CustomerID, SumQty FROM CTE WHERE RowNum <= 3;

2. Running Total（累計計數/總計）

使用 `SUM()` 結合 `PARTITION BY` 和 `ORDER BY` 以及 `OVER()` 子句，可以輕鬆地計算出按時間順序排列的累計總數：

SELECT OrderNumber, OrderDate, ItemPrice, QtyPurchased,
SUM(QtyPurchased) OVER (PARTITION BY CustomerId ORDER BY OrderNumber ASC) AS RunningTotal
FROM Orders;

3. Self Join with Ranking（帶有排名的自連接）

有時候你可能需要在一個查詢中進行自我關聯（Self Join），並對重複的鍵進行排名：

SELECT t1.KeyColumn, t1.ValueColumn, t2.ValueColumn AS OtherValueColumn
FROM Table1 t1 INNER JOIN (
SELECT KeyColumn, ValueColumn, ROW_NUMBER() OVER (PARTITION BY KeyColumn ORDER BY ValueColumn DESC) AS Ranking
FROM Table1
) t2 ON t1.KeyColumn = t2.KeyColumn AND t1.ValueColumn <> t2.ValueColumn
WHERE t2.Ranking <= 3;

窗口函數是一種強大的數據分析工具，它們擴展了 SQL 查詢的能力，使其能夠更靈活地對數據進行復雜處理。通過學習如何有效地使用這些函數，您可以極大地提高您的查詢效率和靈活性，從而更好地滿足業務需求。