什麼是 SQL 的窗口函數?

在一般使用聚合函數的時候，每一組的資訊會被統合為一筆資料。但是在有些情況下，我們會需要保留住每一筆原始資料的資訊，而不是只看到每一組資料統合後的結果。在這類情況下，我們就會用到窗口函數(又稱窗函數，英文為window function)。

窗口函數的名稱由來是因為它被應用在一個窗口之內的每一筆資料。我們用OVER()來設定窗口的定義。OVER()包含以下兩個主要的元素：

  • PARTITION BY： 設定每個窗口內的資料是如何分割的。這跟GROUP BY很類似。這個元素不是一定需要的。當我們的窗口是包含所有的資料時，我們就不需要用到PARTITION BY。
  • ORDER BY:設定在每個窗口內的資料是如何排列的。雖然這個元素並非必須，不過在大部分的情況下，我們會需要設定結果是如何排列的。

有好幾個函數可以被用來在窗口函數上。以下是一些常見的函數：

以下我們介紹每一類的函數：

一般聚合函數: SUM, AVG, COUNT

我們用以下這個表格來當例子： Store_Sales表格

 Store_ID  Salesperson  Sales 
1 Aaron 374 
1 Beatrice 492 
1 Cathy 430 
2 Dan 462 
2 Elmo 747 
2 Frank 1332 
2 Gina 898 
2 Harry 603 
3 Isabel 247 
3 Jimmy 1030 
3 Kara 1030 
3 Lamar 1314 
3 Mary 462 

我們的需求是：要在以上的表格加上一個欄位來顯現出每間店(Store_ID)中所有銷售員Sales的平均值。要達到這個目標，我們用以下的窗口函數：

SELECT Store_ID, Salesperson, Sales, AVG(Sales) OVER (PARTITION BY Store_ID) Avg_Store
FROM Store_Sales;

結果：

 
Store_ID Salesperson SalesAvg_Store
1 Aaron 374 432
1 Beatrice 492 432
1 Cathy 430 432
2 Dan 462 808.4
2 Elmo 747 808.4
2 Frank 1332 808.4
2 Gina 898 808.4
2 Harry 603 808.4
3 Isabel 247 816.6
3 Jimmy 1030 816.6
3 Kara 1030 816.6
3 Lamar 1314 816.6
3 Mary 462 816.6

Avg_Store 欄位的值代表每間店中所有銷售員Sales的平均值。因為它的單位是店，所以在同一個店中的所有銷售員在這個欄位的值都是一樣的，雖然每個銷售員本身賣出去的收入(Sales欄位)通常是不同的。

排名函數: RANK, DENSE_RANK, ROW_NUMBER

ROW_NUMBER(), RANK(), and DENSE_RANK() 都是用來計算每一筆資料在窗口之內的排名。這三個函數的不同處在於它們如何處理平手的情況。

RANK(): 有平手的情況時，這個函數給平手的資料相同的名次。下面的排名則會照有多少筆資料平手而決定。舉例來說，如果有兩筆資料都是並列第一，那下一筆資料就是第三。

DENSE_RANK(): 有平手的情況時，這個函數給平手的資料相同的名次。下面的排名則是直接照排名次序排列。舉例來說，如果有兩筆資料都是並列第一，那下一筆資料就是第二。

ROW_NUMBER(): 每一筆資料都會有不同的名次，包括平手的資料。在平手的狀況下，系統隨機決定哪一筆平手的資料獲得到較高的排名。舉例來說，如果有兩筆資料都是最大值，那其中一筆會被排為第一，另一筆則會被排為第二。

如果沒有平手的情況發生，那這三個函數所產生的排名就都一樣。

接下來我們繼續用以上的Store_Sales表格來當例子。若我們要對銷售員依照她們在每間店裡的銷售成績排名，我們會用以下的窗口函數：

SELECT Store_ID, Salesperson, Sales, RANK() OVER (PARTITION BY Store_ID ORDER BY Sales DESC) Sales_Rank
FROM Store_Sales
ORDER BY Store_ID, Sales_Rank;

結果:

Store_ID Salesperson Sales Sales_Rank
1 Beatrice 492 1
1 Cathy 430 2
1 Aaron 374 3
2 Frank 1332 1
2 Gina 898 2
2 Elmo 747 3
2 Harry 603 4
2 Dan 462 5
3 Lamar 1314 1
3 Jimmy 1030 2
3 Kara 1030 2
3 Mary 462 4
3 Isabel 247 5

若我們將RANK()換為DENSE_RANK()，我們的SQL將會成為：

SELECT Store_ID, Salesperson, Sales, DENSE_RANK() OVER (PARTITION BY Store_ID ORDER BY Sales DESC) Sales_Dense_Rank
FROM Store_Sales
ORDER BY Store_ID, Sales_Rank;

結果：

Store_ID Salesperson Sales Sales_Dense_Rank
1 Beatrice 492 1
1 Cathy 430 2
1 Aaron 374 3
2 Frank 1332 1
2 Gina 898 2
2 Elmo 747 3
2 Harry 603 4
2 Dan 462 5
3 Lamar 1314 1
3 Jimmy 1030 2
3 Kara 1030 2
3 Mary 462 3
3 Isabel 247 4

若我們將RANK()換為ROW_NUMBER()，我們的SQL將會成為：

SELECT Store_ID, Salesperson, Sales, ROW_NUMBER() OVER (PARTITION BY Store_ID ORDER BY Sales DESC) Sales_Row_Number
FROM Store_Sales
ORDER BY Store_ID, Sales_Rank;

結果:

Store_ID Salesperson Sales Sales_Row_Number
1 Beatrice 492 1
1 Cathy 430 2
1 Aaron 374 3
2 Frank 1332 1
2 Gina 898 2
2 Elmo 747 3
2 Harry 603 4
2 Dan 462 5
3 Lamar 1314 1
3 Jimmy 1030 2
3 Kara 1030 3
3 Mary 462 4
3 Isabel 247 5

這三個不同的函數在 Store_ID = 3 的資料排名有所不同。在這間店中，有兩個銷售員的成績是並列第二。當我們用RANK()時，排名順序是 1,2,2,4,5；用DENSE_RANK()時，排名順序是1,2,2,3,4；而用ROW_NUMBER()時，排名順序則是1,2,3,4,5。

分割函數: NTILE

我們在用NTILE()函數時，會加入一個整數的參數。這個參數會代表在窗口內所有的資料要分成多少組，而NTILE()會自動將窗口內所有的資料分割成為這麼多組，而每組的資料筆數會是相同或是幾乎相同。最前面的一組會被稱為第一組，第二前面的組會被稱為第二組，等等。

舉例來說，如果窗口內有100筆資料，而我們用NTILE(5)，那每一個NTILE就會包含20筆資料。

如果總共資料筆數不能被組的數量整除的話，那不同組的資料筆數就可能會差一。比較前面的組裡面的資料筆數會比較多。

舉例來說，如果窗口內有99筆資料，而我們用NTILE(5)，那前面四組就會包含20筆資料，而最後一組則有19筆資料。

我們來看個例子。如果我們要將所有的銷售員依照他們的銷售量來分為四組的話 (無論她們是屬於哪一間店)，我們就用以下的SQL:

SELECT Store_ID, Salesperson, Sales, NTILE(4) OVER (ORDER BY Sales DESC) Sales_Group
FROM Store_Sales
ORDER BY Sales DESC;

結果:

Store_ID Salesperson Sales Sales_Group
2 Frank 1332 1
3 Lamar 1314 1
3 Jimmy 1030 1
3 Kara 1030 1
2 Gina 898 2
2 Harry 603 2
2 Elmo 747 2
1 Beatrice 492 3
2 Dan 462 3
3 Mary 462 3
1 Cathy 430 4
1 Aaron 374 4
3 Isabel 247 4

這裡我們要將13筆資料分為4組，所以第一組有4筆資料，而其他三組則各有3筆資料。

之前和之後函數: LEAD, LAG

當我們的運算需要用到在同一窗口內排於該筆資料之前或之後的資料的話，我們就可以用LEAD() 和 LAG()這兩個函數。以下我們分別介紹：

LAG()函數讓我們運用排在該筆資料前面的資料。LAG()函數的語法為下：

LAG(expression [, interval [, default_value]]) OVER ()

interval參數代表要跳過幾筆資料。如果這個參數沒有被設定，那代表從前一筆資料開始。default_value參數的用處是如果LAG()函數造成我們離開了窗口之外時，我們可以傳回default_value的值，而不是傳回NULL。

LEAD()函數讓我們運用排在該筆資料後面的資料。LEAD()函數的語法為下：

LEAD(expression [, interval [, default_value]]) OVER ()

interval參數代表要跳過幾筆資料。如果這個參數沒有被設定，那代表從後一筆資料開始。default_value參數的用處是如果LEAD()函數造成我們離開了窗口之外時，我們可以傳回default_value的值，而不是傳回NULL。

我們來看個例子。以下的SQL，

SELECT Store_ID, Salesperson, Sales, LEAD(Sales) OVER (PARTITION BY Store_ID ORDER BY Sales DESC) Sales_Lead, LAG(Sales) OVER (PARTITION BY Store_ID ORDER BY Sales DESC) Sales_Lag
FROM Store_Sales
ORDER BY Store_ID, Sales DESC;

會讓我們得到如下的結果：

Store_ID Salesperson Sales Sales_Lead Sales_Lag
1Beatrice 492 430 NULL
1Cathy 430 374 492
1Aaron 374 NULL 430
2Frank 1332 898 NULL
2Gina 898 747 1332
2Elmo 747 603 898
2Harry 603 462 747
2Dan 462 NULL 603
3Lamar 1314 1030 NULL
3Jimmy 1030 1030 1314
3Kara 1030 462 1030
3Mary 462 247 1030
3Isabel 247 NULL 462

我們來看 Cathy。Cathy這一筆資料在Sales_Lead欄位的值是374，而這就是排在Cathy下一個Aaron的Sales值。同一筆資料在Sales_Lag欄位的值是492，而這就是排在Cathy上一個Beatrice的Sales值。

另一個比較常見的例子是當我們需要知道每筆資料和排名在它前面那筆資料的差距有多大。以下的SQL可以獲得答案：

SELECT Store_ID, Salesperson, Sales, LAG(Sales,1,Sales) OVER (PARTITION BY Store_ID ORDER BY Sales DESC) - Sales Sales_Difference FROM Store_Sales
ORDER BY Store_ID, Sales DESC;

結果：

Store_ID Salesperson Sales Sales_Difference
1Beatrice 492 0
1Cathy 430 62
1Aaron 374 56
2Frank 1332 0
2Gina 898 434
2Elmo 747 151
2Harry 603 144
2Dan 462 141
3Lamar 1314 0
3Jimmy 1030 284
3Kara 1030 0
3Mary 462 568
3Isabel 247 215

下一頁：SEQUENCE 和 NEXTVAL

本頁最近於 2023年12月25日更新


Copyright © 2024   1keydata.com   版權所有