本文介紹什麼是 SQL 視窗函數，視窗函數可以進行排序、生成序列號等一般的 聚合函數 無法實現的高級操作。 本文重點 視窗函數可以進行排序、生成序列號等一般的聚合函數無法實現的高級操作。 理解 PARTITION BY 和 ORDER BY 這兩個關鍵字的含義十分重要。 一、什麼是視窗函數 視窗函數 ...
目錄
- 一、什麼是視窗函數
- 二、視窗函數的語法
- 三、語法的基本使用方法——使用 RANK 函數
- 四、無需指定 PARTITION BY
- 五、專用視窗函數的種類
- 六、視窗函數的適用範圍
- 七、作為視窗函數使用的聚合函數
- 八、計算移動平均
- 九、兩個 ORDER BY
本文介紹什麼是 SQL 視窗函數，視窗函數可以進行排序、生成序列號等一般的 聚合函數 無法實現的高級操作。
本文重點
視窗函數可以進行排序、生成序列號等一般的聚合函數無法實現的高級操作。
理解
PARTITION BY和ORDER BY這兩個關鍵字的含義十分重要。
一、什麼是視窗函數
視窗函數也稱為 OLAP 函數 [1]。為了讓大家快速形成直觀印象，才起了這樣一個容易理解的名稱（“視窗”的含義我們將在隨後進行說明）。
OLAP 是 OnLine Analytical Processing 的簡稱，意思是對資料庫數據進行實時分析處理。例如，市場分析、創建財務報表、創建計劃等日常性商務工作。
視窗函數就是為了實現 OLAP 而添加的標準 SQL 功能 [2]。
專欄
視窗函數的支持情況
很多資料庫相關工作者過去都會有這樣的想法：“好不容易將業務數據插入到了資料庫中，如果能夠使用 SQL 對其進行實時分析的話，一定會很方便吧。”但是關係資料庫提供支持 OLAP 用途的功能僅僅只有 10 年左右的時間。
其中的理由有很多，這裡我們就不一一介紹了。大家需要註意的是，還有一部分 DBMS 並不支持這樣的新功能。
本文將要介紹的視窗函數也是其中之一，截至 2016 年 5 月，Oracle、SQL Server、DB2、PostgreSQL 的最新版本都已經支持了該功能，但是 MySQL 的最新版本 5.7 還是不支持該功能。
通過前面的學習，我們已經知道各個 DBMS 都有自己支持的特定語法和不支持的語法。標準 SQL 添加新功能的時候也會遇到同樣的問題（隨著時間推移，標準 SQL 終將能夠在所有的 DBMS 中進行使用）。
二、視窗函數的語法
接下來，就讓我們通過示例來學習視窗函數吧。視窗函數的語法有些複雜。
語法 1 視窗函數
<視窗函數> OVER ([PARTITION BY <列清單>]
ORDER BY <排序用列清單>)
※
[]中的內容可以省略。
其中重要的關鍵字是 PARTITION BY 和 ORDER BY，理解這兩個關鍵字的作用是幫助我們理解視窗函數的關鍵。
2.1 能夠作為視窗函數使用的函數
在學習 PARTITION BY 和 ORDER BY 之前，我們先來列舉一下能夠作為視窗函數使用的函數。視窗函數大體可以分為以下兩種。
① 能夠作為視窗函數的聚合函數（SUM、AVG、COUNT、MAX、MIN）
② RANK、DENSE_RANK、ROW_NUMBER 等專用視窗函數
② 中的函數是標準 SQL 定義的 OLAP 專用函數，本文將其統稱為“專用視窗函數”。從這些函數的名稱可以很容易看出其 OLAP 的用途。
其中 ① 的部分是我們在 SQL 如何對錶進行聚合和分組查詢並對查詢結果進行排序 中學過的聚合函數。將聚合函數書寫在“語法 1”的“<視窗函數>”中，就能夠當作視窗函數來使用了。
總之，聚合函數根據使用語法的不同，可以在聚合函數和視窗函數之間進行轉換。
三、語法的基本使用方法——使用 RANK 函數
首先讓我們通過專用視窗函數 RANK 來理解一下視窗函數的語法吧。正如其名稱所示，RANK 是用來計算記錄排序的函數。
例如，對於之前使用過的 Product 表中的 8 件商品，讓我們根據不同的商品種類（product_type），按照銷售單價（sale_price）從低到高的順序排序，結果如下所示：
product_name | product_type | sale_price | ranking
-------------+--------------+------------+--------
叉子 | 廚房用具 | 500 | 1
擦菜板 | 廚房用具 | 880 | 2
菜刀 | 廚房用具 | 3000 | 3
高壓鍋 | 廚房用具 | 6800 | 4
T恤衫 | 衣服 | 1000 | 1
運動T恤 | 衣服 | 4000 | 2
圓珠筆 | 辦公用品 | 100 | 1
打孔器 | 辦公用品 | 500 | 2
以廚房用具為例，銷售單價最便宜的“叉子”排在第 1 位，最貴的“高壓鍋”排在第 4 位，確實按照我們的要求進行了排序。
能夠得到上述結果的 SELECT 語句請參考代碼清單 1。
代碼清單 1 根據不同的商品種類，按照銷售單價從低到高的順序創建排序表
Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (PARTITION BY product_type
ORDER BY sale_price) AS ranking
FROM Product;
PARTITION BY 能夠設定排序的對象範圍。本例中，為了按照商品種類進行排序，我們指定了 product_type。
ORDER BY 能夠指定按照哪一列、何種順序進行排序。為了按照銷售單價的升序進行排列，我們指定了 sale_price。
此外，視窗函數中的 ORDER BY 與 SELECT 語句末尾的 ORDER BY 一樣，可以通過關鍵字 ASC/DESC 來指定升序和降序。
省略該關鍵字時會預設按照 ASC，也就是升序進行排序。本例中就省略了上述關鍵字 [3]。
通過圖 1，我們就很容易理解 PARTITION BY 和 ORDER BY 的作用了。如圖所示，PARTITION BY 在橫向上對錶進行分組，而 ORDER BY 決定了縱向排序的規則。
圖 1 PARTITION BY 和 ORDER BY 的作用
視窗函數兼具之前我們學過的 GROUP BY 子句的分組功能以及 ORDER BY 子句的排序功能。但是，PARTITION BY 子句並不具備 GROUP BY 子句的彙總功能。
因此，使用 RANK 函數並不會減少原表中記錄的行數，結果中仍然包含 8 行數據。
法則 1
視窗函數兼具分組和排序兩種功能。
通過 PARTITION BY 分組後的記錄集合稱為視窗。此處的視窗並非“窗戶”的意思，而是代表範圍。這也是“視窗函數”名稱的由來。[4]
法則 2
通過
PARTITION BY分組後的記錄集合稱為“視窗”。
此外，各個視窗在定義上絕對不會包含共通的部分。就像刀切蛋糕一樣，乾凈利落。這與通過 GROUP BY 子句分割後的集合具有相同的特征。
四、無需指定 PARTITION BY
使用視窗函數時起到關鍵作用的是 PARTITION BY 和 GROUP BY。其中，PARTITION BY 並不是必需的，即使不指定也可以正常使用視窗函數。
那麼就讓我們來確認一下不指定 PARTITION BY 會得到什麼樣的結果吧。這和使用沒有 GROUP BY 的聚合函數時的效果一樣，也就是將整個表作為一個大的視窗來使用。
事實勝於雄辯，下麵就讓我們刪除代碼清單 1 中 SELECT 語句的 PARTITION BY 試試看吧（代碼清單 2）。
代碼清單 2 不指定 PARTITION BY
Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product;
上述 SELECT 語句的結果如下所示：
product_name | product_type | sale_price | ranking
-------------+--------------+------------+--------
圓珠筆 | 辦公用品 | 100 | 1
叉子 | 廚房用具 | 500 | 2
打孔器 | 辦公用品 | 500 | 2
擦菜板 | 廚房用具 | 880 | 4
T恤衫 | 衣服 | 1000 | 5
菜刀 | 廚房用具 | 3000 | 6
運動T恤 | 衣服 | 4000 | 7
高壓鍋 | 廚房用具 | 6800 | 8
之前我們得到的是按照商品種類分組後的排序，而這次變成了全部商品的排序。像這樣，當希望先將表中的數據分為多個部分（視窗），再使用視窗函數時，可以使用 PARTITION BY 選項。
五、專用視窗函數的種類
從上述結果中我們可以看到，“打孔器”和“叉子”都排在第 2 位，而之後的“擦菜板”跳過了第 3 位，直接排到了第 4 位，這也是通常的排序方法，但某些情況下可能並不希望跳過某個位次來進行排序。
這時可以使用 RANK 函數之外的函數來實現。下麵就讓我們來總結一下具有代表性的專用視窗函數吧。
-
RANK函數計算排序時，如果存在相同位次的記錄，則會跳過之後的位次。
例）有 3 條記錄排在第 1 位時：1 位、1 位、1 位、4 位……
-
DENSE_RANK函數同樣是計算排序，即使存在相同位次的記錄，也不會跳過之後的位次。
例）有 3 條記錄排在第 1 位時：1 位、1 位、1 位、2 位……
-
ROW_NUMBER函數賦予唯一的連續位次。
例）有 3 條記錄排在第 1 位時：1 位、2 位、3 位、4 位……
除此之外，各 DBMS 還提供了各自特有的視窗函數。上述 3 個函數（對於支持視窗函數的 DBMS 來說）在所有的 DBMS 中都能夠使用。
下麵就讓我們來比較一下使用這 3 個函數所得到的結果吧（代碼清單 3）。
代碼清單 3 比較 RANK、DENSE_RANK、ROW_NUMBER 的結果
Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking,
DENSE_RANK () OVER (ORDER BY sale_price) AS dense_ranking,
ROW_NUMBER () OVER (ORDER BY sale_price) AS row_num
FROM Product;
執行結果：
將結果中的 ranking 列和 dense_ranking 列進行比較可以發現，dense_ranking 列中有連續 2 個第 2 位，這和 ranking 列的情況相同。
但是接下來的“擦菜板”的位次並不是第 4 而是第 3。這就是使用 DENSE_RANK 函數的效果了。
此外，我們可以看到，在 row_num 列中，不管銷售單價（sale_price）是否相同，每件商品都會按照銷售單價從低到高的順序得到一個連續的位次。
銷售單價相同時，DBMS 會根據適當的順序對記錄進行排列。想為記錄賦予唯一的連續位次時，就可以像這樣使用 ROW_NUMBER 來實現。
使用 RANK 或 ROW_NUMBER 時無需任何參數，只需要像 RANK() 或者 ROW_NUMBER() 這樣保持括弧中為空就可以了。這也是專用視窗函數通常的使用方式，請大家牢記。
這一點與作為視窗函數使用的聚合函數有很大的不同，之後我們將會詳細介紹。
法則 3
由於專用視窗函數無需參數，因此通常括弧中都是空的。
六、視窗函數的適用範圍
目前為止我們學過的函數大部分都沒有使用位置的限制，最多也就是在 WHERE 子句中使用聚合函數時會有些註意事項。
但是，使用視窗函數的位置卻有非常大的限制。更確切地說，視窗函數只能書寫在一個特定的位置。
這個位置就是 SELECT 子句之中。反過來說，就是這類函數不能在 WHERE 子句或者 GROUP BY 子句中使用。[5]
雖然我們可以把它當作一種規則死記硬背下來，但是為什麼視窗函數只能在 SELECT 子句中使用呢（也就是不能在 WHERE 子句或者 GROUP BY 子句中使用）？
下麵我們就來簡單說明一下其中的理由。
其理由就是，在 DBMS 內部，視窗函數是對 WHERE 子句或者 GROUP BY 子句處理後的“結果”進行的操作。
大家仔細想一想就會明白，在得到用戶想要的結果之前，即使進行了排序處理，結果也是錯誤的。
在得到排序結果之後，如果通過 WHERE 子句中的條件除去了某些記錄，或者使用 GROUP BY 子句進行了彙總處理，那好不容易得到的排序結果也無法使用了。[6]
正是由於這樣的原因，在 SELECT 子句之外“使用視窗函數是沒有意義的”，所以在語法上才會有這樣的限制。
七、作為視窗函數使用的聚合函數
前面給大家介紹了使用專用視窗函數的示例，下麵我們再來看一看把之前學過的 SUM 或者 AVG 等聚合函數作為視窗函數使用的方法。
所有的聚合函數都能用作視窗函數，其語法和專用視窗函數完全相同。但大家可能對所能得到的結果還沒有一個直觀的印象，所以我們還是通過具體的示例來學習。
下麵我們先來看一個將 SUM 函數作為視窗函數使用的例子（代碼清單 4）。
代碼清單 4 將 SUM 函數作為視窗函數使用
Oracle SQL Server DB2 PostgreSQL
SELECT product_id, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;
執行結果：
product_id | product_name | sale_price | current_sum
------------+--------------+------------+------------
0001 | T恤衫 | 1000 | 1000 ←1000
0002 | 打孔器 | 500 | 1500 ←1000+500
0003 | 運動T恤 | 4000 | 5500 ←1000+500+4000
0004 | 菜刀 | 3000 | 8500 ←1000+500+4000+3000
0005 | 高壓鍋 | 6800 | 15300 ·
0006 | 叉子 | 500 | 15800 ·
0007 | 擦菜板 | 880 | 16680 ·
0008 | 圓珠筆 | 100 | 16780 ·
使用 SUM 函數時，並不像 RANK 或者 ROW_NUMBER 那樣括弧中的內容為空，而是和之前我們學過的一樣，需要在括弧內指定作為彙總對象的列。
本例中我們計算出了銷售單價（sale_price）的合計值（current_sum）。
但是我們得到的並不僅僅是合計值，而是按照 ORDER BY 子句指定的 product_id 的升序進行排列，計算出商品編號“小於自己”的商品的銷售單價的合計值。
因此，計算該合計值的邏輯就像金字塔堆積那樣，一行一行逐漸添加計算對象。在按照時間序列的順序，計算各個時間的銷售額總額等的時候，通常都會使用這種稱為累計的統計方法。
使用其他聚合函數時的操作邏輯也和本例相同。例如，使用 AVG 來代替 SELECT 語句中的 SUM（代碼清單 5）。
代碼清單 5 將 AVG 函數作為視窗函數使用
Oracle SQL Server DB2 PostgreSQL
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id) AS current_avg
FROM Product;
執行結果：
product_id | product_name | sale_price | current_avg
-----------+--------------+------------+-----------------------
0001 | T恤衫 | 1000 | 1000.0000000000000000 ←(1000)/1
0002 | 打孔器 | 500 | 750.0000000000000000 ←(1000+500)/2
0003 | 運動T恤 | 4000 | 1833.3333333333333333 ←(1000+500+4000)/3
0004 | 菜刀 | 3000 | 2125.0000000000000000 ←(1000+500+4000+3000)/4
0005 | 高壓鍋 | 6800 | 3060.0000000000000000 ←(1000+500+4000+3000+6800)/5
0006 | 叉子 | 500 | 2633.3333333333333333 ·
0007 | 擦菜板 | 880 | 2382.8571428571428571 ·
0008 | 圓珠筆 | 100 | 2097.5000000000000000 ·
從結果中我們可以看到，current_avg 的計算方法確實是計算平均值的方法，但作為統計對象的卻只是“排在自己之上”的記錄。
像這樣以“自身記錄（當前記錄）”作為基準進行統計，就是將聚合函數當作視窗函數使用時的最大特征。
八、計算移動平均
視窗函數就是將表以視窗為單位進行分割，併在其中進行排序的函數。其實其中還包含在視窗中指定更加詳細的彙總範圍的備選功能，該備選功能中的彙總範圍稱為框架。
其語法如代碼清單 6 所示，需要在 ORDER BY 子句之後使用指定範圍的關鍵字。
代碼清單 6 指定“最靠近的 3 行”作為彙總對象
Oracle SQL Server DB2 PostgreSQL
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg
FROM Product;
執行結果（在 DB2 中執行）：
product_id product_name sale_price moving_avg
----------- ------------- ------------- ------------
0001 T恤衫 1000 1000 ←(1000)/1
0002 打孔器 500 750 ←(1000+500)/2
0003 運動T恤 4000 1833 ←(1000+500+4000)/3
0004 菜刀 3000 2500 ←(500+4000+3000)/3
0005 高壓鍋 6800 4600 ←(4000+3000+6800)/3
0006 叉子 500 3433 ·
0007 擦菜板 880 2726 ·
0008 圓珠筆 100 493 ·
8.1 指定框架（彙總範圍）
我們將上述結果與之前的結果進行比較，可以發現商品編號為“0004”的“菜刀”以下的記錄和視窗函數的計算結果並不相同。
這是因為我們指定了框架，將彙總對象限定為了“最靠近的 3 行”。
這裡我們使用了 ROWS（“行”）和 PRECEDING（“之前”）兩個關鍵字，將框架指定為“截止到之前 ~ 行”，因此“ROWS 2 PRECEDING”就是將框架指定為“截止到之前 2 行”，也就是將作為彙總對象的記錄限定為如下的“最靠近的 3 行”。
-
自身（當前記錄）
-
之前 1 行的記錄
-
之前 2 行的記錄
也就是說，由於框架是根據當前記錄來確定的，因此和固定的視窗不同，其範圍會隨著當前記錄的變化而變化。
圖 2 將框架指定為截止到當前記錄之前 2 行（最靠近的 3 行）
如果將條件中的數字變為“ROWS 5 PRECEDING”，就是“截止到之前 5 行”（最靠近的 6 行）的意思。
這樣的統計方法稱為移動平均（moving average）。由於這種方法在希望實時把握“最近狀態”時非常方便，因此常常會應用在對股市趨勢的實時跟蹤當中。
使用關鍵字 FOLLOWING（“之後”）替換 PRECEDING，就可以指定“截止到之後~ 行”作為框架了（圖 3）。
圖 3 將框架指定為截止到當前記錄之後 2 行（最靠近的 3 行）
8.2 將當前記錄的前後行作為彙總對象
如果希望將當前記錄的前後行作為彙總對象時，就可以像代碼清單 7 那樣，同時使用 PRECEDING（“之前”）和 FOLLOWING（“之後”）關鍵字來實現。
代碼清單 7 將當前記錄的前後行作為彙總對象
Oracle SQL Server DB2 PostgreSQL
Oracle SQL Server DB2 PostgreSQL
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM Product;
執行結果（在 DB2 中執行）：
product_id product_name sale_price moving_avg
----------- ------------- ----------- -----------
0001 T恤衫 1000 750 ←(1000+500)/2
0002 打孔器 500 1833 ←(1000+500+4000)/3
0003 運動T恤 4000 2500 ←(500+4000+3000)/3
0004 菜刀 3000 4600 ←(4000+3000+6800)/3
0005 高壓鍋 6800 3433 ·
0006 叉子 500 2726 ·
0007 擦菜板 880 493 ·
0008 圓珠筆 100 490 ·
在上述代碼中，我們通過指定框架，將“1 PRECEDING”（之前 1 行）和“1 FOLLOWING”（之後 1 行）的區間作為彙總對象。
具體來說，就是將如下 3 行作為彙總對象來進行計算（圖 4）。
-
之前 1 行的記錄
-
自身（當前記錄）
-
之後 1 行的記錄
如果能夠熟練掌握框架功能，就可以稱為視窗函數高手了。
圖 4 將框架指定為當前記錄及其前後 1 行
九、兩個 ORDER BY
最後我們來介紹一下使用視窗函數時與結果形式相關的註意事項，那就是記錄的排列順序。
因為使用視窗函數時必須要在 OVER 子句中使用 ORDER BY，所以可能有讀者乍一看會覺得結果中的記錄會按照該 ORDER BY 指定的順序進行排序。
但其實這隻是一種錯覺。OVER 子句中的 ORDER BY 只是用來決定視窗函數按照什麼樣的順序進行計算的，對結果的排列順序並沒有影響。
因此也有可能像代碼清單 8 那樣，得到一個記錄的排列順序比較混亂的結果。有些 DBMS 也可以按照視窗函數的 ORDER BY 子句所指定的順序對結果進行排序，但那也僅僅是個例而已。
代碼清單 8 無法保證如下 SELECT 語句的結果的排列順序
Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product;
有可能會得到下麵這樣的結果：
product_name | product_type | sale_price | ranking
--------------+--------------+------------+--------
菜刀 | 廚房用具 | 3000 | 6
打孔器 | 辦公用品 | 500 | 2
運動T恤 | 衣服 | 4000 | 7
T恤衫 | 衣服 | 1000 | 5
高壓鍋 | 廚房用具 | 6800 | 8
叉子 | 廚房用具 | 500 | 2
擦菜板 | 廚房用具 | 880 | 4
圓珠筆 | 辦公用品 | 100 | 1
那麼，如何才能讓記錄切實按照 ranking 列的升序進行排列呢？
答案非常簡單。那就是在 SELECT 語句的最後，使用 ORDER BY 子句進行指定（代碼清單 9）。
這樣就能保證 SELECT 語句的結果中記錄的排列順序了，除此之外也沒有其他辦法了。
代碼清單 9 在語句末尾使用 ORDER BY 子句對結果進行排序
Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product
ORDER BY ranking;
也許大家會覺得在一條 SELECT 語句中使用兩次 ORDER BY 會有點彆扭，但是儘管這兩個 ORDER BY 看上去是相同的，但其實它們的功能卻完全不同。
法則 5
將聚合函數作為視窗函數使用時，會以當前記錄為基準來決定彙總對象的記錄。
原文鏈接：https://www.developerastrid.com/sql/sql-olap/
（完）
在 Oracle 和 SQL Server 中稱為分析函數。 ↩︎
目前 MySQL 還不支持視窗函數。詳細信息請參考專欄“視窗函數的支持情況”。 ↩︎
其所要遵循的規則與
SELECT語句末尾的ORDER BY子句完全相同。 ↩︎從詞語意思的角度考慮，可能“組”比“視窗”更合適一些，但是在 SQL 中，“組”更多的是用來特指使用
GROUP BY分割後的記錄集合，因此，為了避免混淆，使用PARTITION BY時稱為視窗。 ↩︎語法上，除了
SELECT子句，ORDER BY子句或者UPDATE語句的SET子句中也可以使用。但因為幾乎沒有實際的業務示例，所以開始的時候大家只要記得“只能在SELECT子句中使用”就可以了。 ↩︎反之，之所以在
ORDER BY子句中能夠使用視窗函數，是因為ORDER BY子句會在SELECT子句之後執行，並且記錄保證不會減少。 ↩︎
