什么是SQL窗口函數(shù)

本篇內(nèi)容主要講解“什么是SQL窗口函數(shù)”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“什么是SQL窗口函數(shù)”吧!

窗口函數(shù)（Window Function） 是 SQL2003 標準中定義的一項新特性，并在 SQL2011、SQL2016 中又加以完善，添加了若干處拓展。窗口函數(shù)不同于我們熟悉的普通函數(shù)和聚合函數(shù)，它為每行數(shù)據(jù)進行一次計算：輸入多行（一個窗口）、返回一個值。在報表等分析型查詢中，窗口函數(shù)能優(yōu)雅地表達某些需求，發(fā)揮不可替代的作用。

什么是窗口函數(shù)？

窗口函數(shù)出現(xiàn)在 SELECT 子句的表達式列表中，它最顯著的特點就是 OVER 關(guān)鍵字。語法定義如下：

window_function (expression) OVER (     [ PARTITION BY part_list ]     [ ORDER BY order_list ]     [ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] )

其中包括以下可選項：

•  PARTITION BY 表示將數(shù)據(jù)先按 part_list 進行分區(qū)

•  ORDER BY 表示將各個分區(qū)內(nèi)的數(shù)據(jù)按 order_list 進行排序

Figure 1. 窗口函數(shù)的基本概念

最后一項表示 Frame 的定義，即：當前窗口包含哪些數(shù)據(jù)？

•  ROWS 選擇前后幾行，例如 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING 表示往前 3 行到往后 3 行，一共 7 行數(shù)據(jù)（或小于 7 行，如果碰到了邊界）

•  RANGE 選擇數(shù)據(jù)范圍，例如 RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING 表示所有值在 [c−3,c+3][c−3,c+3] 這個范圍內(nèi)的行，cc 為當前行的值

Figure 2. Rows 窗口和 Range 窗口

邏輯語義上說，一個窗口函數(shù)的計算“過程”如下：

•  按窗口定義，將所有輸入數(shù)據(jù)分區(qū)、再排序（如果需要的話）

•  對每一行數(shù)據(jù)，計算它的 Frame 范圍

•  將 Frame 內(nèi)的行集合輸入窗口函數(shù)，計算結(jié)果填入當前行

舉個例子：

SELECT dealer_id, emp_name, sales,         ROW_NUMBER() OVER (PARTITION BY dealer_id ORDER BY sales) AS rank,         AVG(sales) OVER (PARTITION BY dealer_id) AS avgsales   FROM sales

上述查詢中，rank 列表示在當前經(jīng)銷商下，該雇員的銷售排名；avgsales 表示當前經(jīng)銷商下所有雇員的平均銷售額。查詢結(jié)果如下：

+------------+-----------------+--------+------+---------------+  | dealer_id  | emp_name        | sales  | rank | avgsales      |  +------------+-----------------+--------+------+---------------+  | 1          | Raphael Hull    | 8227   | 1    | 14356         |  | 1          | Jack Salazar    | 9710   | 2    | 14356         |  | 1          | Ferris Brown    | 19745  | 3    | 14356         |  | 1          | Noel Meyer      | 19745  | 4    | 14356         |  | 2          | Haviva Montoya  | 9308   | 1    | 13924         |  | 2          | Beverly Lang    | 16233  | 2    | 13924         |  | 2          | Kameko French   | 16233  | 3    | 13924         |  | 3          | May Stout       | 9308   | 1    | 12368         |  | 3          | Abel Kim        | 12369  | 2    | 12368         |  | 3          | Ursa George     | 15427  | 3    | 12368         |  +------------+-----------------+--------+------+---------------+

注：語法中每個部分都是可選的：

•  如果不指定 PARTITION BY，則不對數(shù)據(jù)進行分區(qū)；換句話說，所有數(shù)據(jù)看作同一個分區(qū)

•  如果不指定 ORDER BY，則不對各分區(qū)做排序，通常用于那些順序無關(guān)的窗口函數(shù)，例如 SUM()

•  如果不指定 Frame 子句，則默認采用以下的 Frame 定義：

•   若不指定 ORDER BY，默認使用分區(qū)內(nèi)所有行 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

•   若指定了 ORDER BY，默認使用分區(qū)內(nèi)第一行到當前值 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

最后，窗口函數(shù)可以分為以下 3 類：

•  聚合（Aggregate）：AVG(), COUNT(), MIN(), MAX(), SUM()...

•  取值（Value）：FIRST_VALUE(), LAST_VALUE(), LEAD(), LAG()...

•  排序（Ranking）：RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()...

受限于篇幅，本文不去探討各個窗口函數(shù)的含義。關(guān)注公眾號Java技術(shù)棧，在后臺回復：面試，可以獲取我整理的 MySQL 系列面試題和答案，非常齊全。

注：Frame 定義并非所有窗口函數(shù)都適用，比如 ROW_NUMBER()、RANK()、LEAD() 等。這些函數(shù)總是應用于整個分區(qū)，而非當前 Frame。

窗口函數(shù) VS. 聚合函數(shù)

從聚合這個意義上出發(fā)，似乎窗口函數(shù)和 Group By 聚合函數(shù)都能做到同樣的事情。但是，它們之間的相似點也僅限于此了！這其中的關(guān)鍵區(qū)別在于：窗口函數(shù)僅僅只會將結(jié)果附加到當前的結(jié)果上，它不會對已有的行或列做任何修改。而 Group By 的做法完全不同：對于各個 Group 它僅僅會保留一行聚合結(jié)果。

有的讀者可能會問，加了窗口函數(shù)之后返回結(jié)果的順序明顯發(fā)生了變化，這不算一種修改嗎？因為 SQL 及關(guān)系代數(shù)都是以 multi-set 為基礎(chǔ)定義的，結(jié)果集本身并沒有順序可言，ORDER BY 僅僅是最終呈現(xiàn)結(jié)果的順序。

另一方面，從邏輯語義上說，SELECT 語句的各個部分可以看作是按以下順序“執(zhí)行”的：

Figure 3. SQL 各部分的邏輯執(zhí)行順序

注意到窗口函數(shù)的求值僅僅位于 ORDER BY 之前，而位于 SQL 的絕大部分之后。這也和窗口函數(shù)只附加、不修改的語義是呼應的——結(jié)果集在此時已經(jīng)確定好了，再依此計算窗口函數(shù)。別再 select * 了，送你 12 個查詢技巧，推薦看下。

窗口函數(shù)的執(zhí)行

窗口函數(shù)經(jīng)典的執(zhí)行方式分為排序和函數(shù)求值這 2 步。

Figure 4. 一個窗口函數(shù)的執(zhí)行過程，通常分為排序和求值 2 步

窗口定義中的 PARTITION BY 和 ORDER BY 都很容易通過排序完成。例如，對于窗口 PARTITION BY a, b ORDER BY c, d，我們可以對輸入數(shù)據(jù)按 (a,b,c,d)(a,b,c,d) 或 (b,a,c,d)(b,a,c,d) 做排序，之后數(shù)據(jù)就排列成 Figure 1 中那樣了。

接下來考慮：如何處理 Frame？

•  對于整個分區(qū)的 Frame（例如 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING），只要對整個分區(qū)計算一次即可，沒什么好說的；

•  對于逐漸增長的 Frame（例如 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），可以用 Aggregator 維護累加的狀態(tài)，這也很容易實現(xiàn)；

•  對于滑動的 Frame（例如 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING）相對困難一些。一種經(jīng)典的做法是要求 Aggregator 不僅支持增加還支持刪除（Removable），這可能比你想的要更復雜，例如考慮下 MAX() 的實現(xiàn)。

窗口函數(shù)的優(yōu)化

對于窗口函數(shù)，優(yōu)化器能做的優(yōu)化有限。這里為了行文的完整性，仍然做一個簡要的說明。

通常，我們首先會把窗口函數(shù)從 Project 中抽取出來，成為一個獨立的算子稱之為 Window。

Figure 5. 窗口函數(shù)的優(yōu)化過程

有時候，一個 SELECT 語句中包含多個窗口函數(shù)，它們的窗口定義（OVER 子句）可能相同、也可能不同。顯然，對于相同的窗口，完全沒必要再做一次分區(qū)和排序，我們可以將它們合并成一個 Window 算子。

對于不同的窗口，最樸素地，我們可以將其全部分成不同的 Window，如上圖所示。實際執(zhí)行時，每個 Window 都需要先做一次排序，代價不小。

那是否可能利用一次排序計算多個窗口函數(shù)呢？某些情況下，這是可能的。例如本文例子中的 2 個窗口函數(shù)：

... ROW_NUMBER() OVER (PARTITION BY dealer_id ORDER BY sales) AS rank,      AVG(sales) OVER (PARTITION BY dealer_id) AS avgsales ...

雖然這 2 個窗口并非完全一致，但是 AVG(sales) 不關(guān)心分區(qū)內(nèi)的順序，完全可以復用 ROW_NUMBER() 的窗口。

窗口函數(shù)的并行執(zhí)行

現(xiàn)代 DBMS 大多支持并行執(zhí)行。對于窗口函數(shù)，由于各個分區(qū)之間的計算完全不相關(guān)，我們可以很容易地將各個分區(qū)分派給不同的節(jié)點（線程），從而達到分區(qū)間并行。

但是，如果窗口函數(shù)只有一個全局分區(qū)（無 PARTITION BY 子句），或者分區(qū)數(shù)量很少、不足以充分并行時，怎么辦呢？上文中我們提到的 Removable Aggregator 的技術(shù)顯然無法繼續(xù)使用了，它依賴于單個 Aggregator 的內(nèi)部狀態(tài)，很難有效地并行起來。

TUM 的這篇論文中提出使用線段樹（Segment Tree）實現(xiàn)高效的分區(qū)內(nèi)并行。線段樹是一個 N 叉樹數(shù)據(jù)結(jié)構(gòu)，每個節(jié)點包含當前節(jié)點下的部分聚合結(jié)果。

下圖是一個使用二叉線段樹計算 SUM() 的例子。例如下圖中第三行的 1212，表示葉節(jié)點 5+75+7 的聚合結(jié)果；而它上方的 2525 表示葉節(jié)點 5+7+3+105+7+3+10 的聚合結(jié)果。

Figure 6. 使用線段樹計算給定范圍的總和

假設(shè)當前 Frame 是第 2 到第 8 行，即需要計算 7+3+10+...+47+3+10+...+4 區(qū)間之和。有了線段樹以后，我們可以直接利用 7+13+207+13+20 （圖中紅色字體）計算出聚合結(jié)果。

線段樹可以在 O(nlogn)O(nlog?n) 時間內(nèi)構(gòu)造，并能在 O(logn)O(log?n) 時間內(nèi)查詢?nèi)我鈪^(qū)間的聚合結(jié)果。更棒的是，不僅查詢可以多線程并發(fā)互不干擾，而且線段樹的構(gòu)造過程也能被很好地并行起來。

到此，相信大家對“什么是SQL窗口函數(shù)”有了更深的了解，不妨來實際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢，關(guān)注我們，繼續(xù)學習！