SQL 視窗函式簡介-有解無憂

一、什么是視窗函式
二、視窗函式的語法
- 2.1 能夠作為視窗函式使用的函式
三、語法的基本使用方法——使用 RANK 函式
四、無需指定 PARTITION BY
五、專用視窗函式的種類
六、視窗函式的適用范圍
七、作為視窗函式使用的聚合函式
八、計算移動平均
- 8.1 指定框架（匯總范圍）
- 8.2 將當前記錄的前后行作為匯總物件
九、兩個 ORDER BY
請參閱

學習重點

視窗函式可以進行排序、生成序列號等一般的聚合函式無法實作的高級操作，

理解 PARTITION BY 和 ORDER BY 這兩個關鍵字的含義十分重要，

一、什么是視窗函式

視窗函式也稱為 OLAP 函式 ^[1]，為了讓大家快速形成直觀印象，才起了這樣一個容易理解的名稱（“視窗”的含義我們將在隨后進行說明），

KEYWORD

視窗函式

OLAP 函式

OLAP 是 OnLine Analytical Processing 的簡稱，意思是對資料庫資料進行實時分析處理，例如，市場分析、創建財務報表、創建計劃等日常性商務作業，

KEYWORD

OLAP

視窗函式就是為了實作 OLAP 而添加的標準 SQL 功能 ^[2]，

專欄

視窗函式的支持情況

很多資料庫相關作業者過去都會有這樣的想法：“好不容易將業務資料插入到了資料庫中，如果能夠使用 SQL 對其進行實時分析的話，一定會很方便吧，”但是關系資料庫提供支持 OLAP 用途的功能僅>僅只有 10 年左右的時間，

其中的理由有很多，這里我們就不一一介紹了，大家需要注意的是，還有一部分 DBMS 并不支持這樣的新功能，

本節將要介紹的視窗函式也是其中之一，截至 2016 年 5 月，Oracle、SQL Server、DB2、PostgreSQL 的最新版本都已經支持了該功能，但是 MySQL 的最新版本 5.7 還是不支持該功能，

通過前面的學習，我們已經知道各個 DBMS 都有自己支持的特定語法和不支持的語法，標準 SQL 添加新功能的時候也會遇到同樣的問題 ^[3]，

二、視窗函式的語法

接下來，就讓我們通過示例來學習視窗函式吧，視窗函式的語法有些復雜，

語法 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 等專用視窗函式

KEYWORD

專用視窗函式

② 中的函式是標準 SQL 定義的 OLAP 專用函式，本教程將其統稱為“專用視窗函式”，從這些函式的名稱可以很容易看出其 OLAP 的用途，

其中 ① 的部分是我們在對表進行聚合查詢中學過的聚合函式，將聚合函式書寫在“語法 1”的“<視窗函式>”中，就能夠當作視窗函式來使用了，總之，聚合函式根據使用語法的不同，可以在聚合函式和視窗函式之間進行轉換，

三、語法的基本使用方法——使用 `RANK` 函式

首先讓我們通過專用視窗函式 RANK 來理解一下視窗函式的語法吧，正如其名稱所示，RANK 是用來計算記錄排序的函式，

KEYWORD

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，也就是升序進行排序，本例中就省略了上述關鍵字 ^[4]，

KEYWORD

PARTITION BY 子句

ORDER BY 子句

通過圖 1，我們就很容易理解 PARTITION BY 和 ORDER BY 的作用了，如圖所示，PARTITION BY 在橫向上對表進行分組，而 ORDER BY 決定了縱向排序的規則，

視窗函式兼具之前我們學過的 GROUP BY 子句的分組功能以及 ORDER BY 子句的排序功能，但是，PARTITION BY 子句并不具備 GROUP BY 子句的匯總功能，因此，使用 RANK 函式并不會減少原表中記錄的行數，結果中仍然包含 8 行資料，

法則 1

視窗函式兼具分組和排序兩種功能，

通過 PARTITION BY 分組后的記錄集合稱為視窗，此處的視窗并非“窗戶”的意思，而是代表范圍，這也是“視窗函式”名稱的由來，^[5]

KEYWORD

視窗

法則 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 位……

KEYWORD

RANK 函式

DENSE_RANK 函式

ROW_NUMBER 函式

除此之外，各 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 子句中使用，^[6]

雖然我們可以把它當作一種規則死記硬背下來，但是為什么視窗函式只能在 SELECT 子句中使用呢（也就是不能在 WHERE 子句或者 GROUP BY 子句中使用）？下面我們就來簡單說明一下其中的理由，

其理由就是，在 DBMS 內部，視窗函式是對 WHERE 子句或者 GROUP BY 子句處理后的“結果”進行的操作，大家仔細想一想就會明白，在得到用戶想要的結果之前，即使進行了排序處理，結果也是錯誤的，在得到排序結果之后，如果通過 WHERE 子句中的條件除去了某些記錄，或者使用 GROUP BY 子句進行了匯總處理，那好不容易得到的排序結果也無法使用了，^[7]

正是由于這樣的原因，在 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 的升序進行排列，計算出商品編號“小于自己”的商品的銷售單價的合計值，因此，計算該合計值的邏輯就像金字塔堆積那樣，一行一行逐漸添加計算物件，在按照時間序列的順序，計算各個時間的銷售額總額等的時候，通常都會使用這種稱為累計的統計方法，

KEYWORD

累計

使用其他聚合函式時的操作邏輯也和本例相同，例如，使用 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 的計算方法確實是計算平均值的方法，但作為統計物件的卻只是“排在自己之上”的記錄，像這樣以“自身記錄（當前記錄）”作為基準進行統計，就是將聚合函式當作視窗函式使用時的最大特征，

KEYWORD

當前記錄

八、計算移動平均

視窗函式就是將表以視窗為單位進行分割，并在其中進行排序的函式，其實其中還包含在視窗中指定更加詳細的匯總范圍的備選功能，該備選功能中的匯總范圍稱為框架，

KEYWORD

框架

其語法如代碼清單 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 行”，

KEYWORD

ROWS 關鍵字

PRECEDING 關鍵字

自身（當前記錄）
之前 1 行的記錄
之前 2 行的記錄

也就是說，由于框架是根據當前記錄來確定的，因此和固定的視窗不同，其范圍會隨著當前記錄的變化而變化，

如果將條件中的數字變為“ROWS 5 PRECEDING”，就是“截止到之前 5 行”（最靠近的 6 行）的意思，

這樣的統計方法稱為移動平均（moving average），由于這種方法在希望實時把握“最近狀態”時非常方便，因此常常會應用在對股市趨勢的實時跟蹤當中，

使用關鍵字 FOLLOWING（“之后”）替換 PRECEDING，就可以指定“截止到之后~ 行”作為框架了（圖 3），

KEYWORD

移動平均

FOLLOWING 關鍵字

8.2 將當前記錄的前后行作為匯總物件

如果希望將當前記錄的前后行作為匯總物件時，就可以像代碼清單 7 那樣，同時使用 PRECEDING（“之前”）和 FOLLOWING（“之后”）關鍵字來實作，

代碼清單 7　將當前記錄的前后行作為匯總物件

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 行的記錄

如果能夠熟練掌握框架功能，就可以稱為視窗函式高手了，

九、兩個 `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

將聚合函式作為視窗函式使用時，會以當前記錄為基準來決定匯總物件的記錄，

請參閱

視窗函式
GROUPING 運算子

（完）

在 Oracle 和 SQL Server 中稱為分析函式， ??
目前 MySQL 還不支持視窗函式，詳細資訊請參考專欄“視窗函式的支持情況”， ??
隨著時間推移，標準 SQL 終將能夠在所有的 DBMS 中進行使用， ??
其所要遵循的規則與 SELECT 陳述句末尾的 ORDER BY 子句完全相同， ??
從詞語意思的角度考慮，可能“組”比“視窗”更合適一些，但是在 SQL 中，“組”更多的是用來特指使用 GROUP BY 分割后的記錄集合，因此，為了避免混淆，使用 PARTITION BY 時稱為視窗， ??
語法上，除了 SELECT 子句，ORDER BY 子句或者 UPDATE 陳述句的 SET 子句中也可以使用，但因為幾乎沒有實際的業務示例，所以開始的時候大家只要記得“只能在 SELECT 子句中使用”就可以了， ??
反之，之所以在 ORDER BY 子句中能夠使用視窗函式，是因為 ORDER BY 子句會在 SELECT 子句之后執行，并且記錄保證不會減少， ??

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/374448.html

標籤：SQL Server

上一篇：mysql與事務、恢復相關的引數決議

下一篇：SQL GROUPING 運算子

SQL 視窗函式簡介

一、什么是視窗函式

二、視窗函式的語法

2.1 能夠作為視窗函式使用的函式

三、語法的基本使用方法——使用 RANK 函式

四、無需指定 PARTITION BY

五、專用視窗函式的種類

六、視窗函式的適用范圍

七、作為視窗函式使用的聚合函式

八、計算移動平均

8.1 指定框架（匯總范圍）

8.2 將當前記錄的前后行作為匯總物件

九、兩個 ORDER BY

請參閱

三、語法的基本使用方法——使用 `RANK` 函式

四、無需指定 `PARTITION BY`

九、兩個 `ORDER BY`