建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
窗口函數(shù)（Window Function） 是 SQL2003 標(biāo)準(zhǔn)中定義的一項(xiàng)新特性，并在 SQL2011、SQL2016 中又加以完善，添加了若干處拓展。窗口函數(shù)不同于我們熟悉的普通函數(shù)和聚合函數(shù)，它為每行數(shù)據(jù)進(jìn)行一次計(jì)算：輸入多行（一個(gè)窗口）、返回一個(gè)值。在報(bào)表等分析型查詢(xún)中，窗口函數(shù)能優(yōu)雅地表達(dá)某些需求，發(fā)揮不可替代的作用。
專(zhuān)業(yè)領(lǐng)域包括成都網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)、商城網(wǎng)站建設(shè)、微信營(yíng)銷(xiāo)、系統(tǒng)平臺(tái)開(kāi)發(fā)， 與其他網(wǎng)站設(shè)計(jì)及系統(tǒng)開(kāi)發(fā)公司不同，創(chuàng)新互聯(lián)公司的整合解決方案結(jié)合了幫做網(wǎng)絡(luò)品牌建設(shè)經(jīng)驗(yàn)和互聯(lián)網(wǎng)整合營(yíng)銷(xiāo)的理念，并將策略和執(zhí)行緊密結(jié)合，為客戶(hù)提供全網(wǎng)互聯(lián)網(wǎng)整合方案。
本文首先介紹窗口函數(shù)的定義及基本語(yǔ)法，之后將介紹在 DBMS 和大數(shù)據(jù)系統(tǒng)中是如何實(shí)現(xiàn)高效計(jì)算窗口函數(shù)的，包括窗口函數(shù)的優(yōu)化、執(zhí)行以及并行執(zhí)行。
什么是窗口函數(shù)？
窗口函數(shù)出現(xiàn)在 SELECT 子句的表達(dá)式列表中，它最顯著的特點(diǎn)就是 OVER 關(guān)鍵字。語(yǔ)法定義如下：
- window_function (expression) OVER (
- [ PARTITION BY part_list ]
- [ ORDER BY order_list ]
- [ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] )
其中包括以下可選項(xiàng)：
- PARTITION BY 表示將數(shù)據(jù)先按 part_list 進(jìn)行分區(qū)
- ORDER BY 表示將各個(gè)分區(qū)內(nèi)的數(shù)據(jù)按 order_list 進(jìn)行排序
Figure 1. 窗口函數(shù)的基本概念
最后一項(xiàng)表示 Frame 的定義，即：當(dāng)前窗口包含哪些數(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] 這個(gè)范圍內(nèi)的行，cc 為當(dāng)前行的值
Figure 2. Rows 窗口和 Range 窗口
邏輯語(yǔ)義上說(shuō)，一個(gè)窗口函數(shù)的計(jì)算“過(guò)程”如下：
- 按窗口定義，將所有輸入數(shù)據(jù)分區(qū)、再排序（如果需要的話(huà)）
- 對(duì)每一行數(shù)據(jù)，計(jì)算它的 Frame 范圍
- 將 Frame 內(nèi)的行集合輸入窗口函數(shù)，計(jì)算結(jié)果填入當(dāng)前行
舉個(gè)例子：
- 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
上述查詢(xún)中，rank 列表示在當(dāng)前經(jīng)銷(xiāo)商下，該雇員的銷(xiāo)售排名；avgsales 表示當(dāng)前經(jīng)銷(xiāo)商下所有雇員的平均銷(xiāo)售額。查詢(xún)結(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 |
- +------------+-----------------+--------+------+---------------+
注：語(yǔ)法中每個(gè)部分都是可選的：
- 如果不指定 PARTITION BY，則不對(duì)數(shù)據(jù)進(jìn)行分區(qū)；換句話(huà)說(shuō)，所有數(shù)據(jù)看作同一個(gè)分區(qū)
- 如果不指定 ORDER BY，則不對(duì)各分區(qū)做排序，通常用于那些順序無(wú)關(guān)的窗口函數(shù)，例如 SUM()
- 如果不指定 Frame 子句，則默認(rèn)采用以下的 Frame 定義：
- 若不指定 ORDER BY，默認(rèn)使用分區(qū)內(nèi)所有行 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
- 若指定了 ORDER BY，默認(rèn)使用分區(qū)內(nèi)第一行到當(dāng)前值 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
最后，窗口函數(shù)可以分為以下 3 類(lèi)：
- 聚合（Aggregate）：AVG(), COUNT(), MIN(), MAX(), SUM()...
- 取值（Value）：FIRST_VALUE(), LAST_VALUE(), LEAD(), LAG()...
- 排序（Ranking）：RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()...
受限于篇幅，本文不去探討各個(gè)窗口函數(shù)的含義。關(guān)注公眾號(hào)Java技術(shù)棧，在后臺(tái)回復(fù)：面試，可以獲取我整理的 MySQL 系列面試題和答案，非常齊全。
注：Frame 定義并非所有窗口函數(shù)都適用，比如 ROW_NUMBER()、RANK()、LEAD() 等。這些函數(shù)總是應(yīng)用于整個(gè)分區(qū)，而非當(dāng)前 Frame。
窗口函數(shù) VS. 聚合函數(shù)
從聚合這個(gè)意義上出發(fā)，似乎窗口函數(shù)和 Group By 聚合函數(shù)都能做到同樣的事情。但是，它們之間的相似點(diǎn)也僅限于此了！這其中的關(guān)鍵區(qū)別在于：窗口函數(shù)僅僅只會(huì)將結(jié)果附加到當(dāng)前的結(jié)果上，它不會(huì)對(duì)已有的行或列做任何修改。而 Group By 的做法完全不同：對(duì)于各個(gè) Group 它僅僅會(huì)保留一行聚合結(jié)果。
有的讀者可能會(huì)問(wèn)，加了窗口函數(shù)之后返回結(jié)果的順序明顯發(fā)生了變化，這不算一種修改嗎？因?yàn)?SQL 及關(guān)系代數(shù)都是以 multi-set 為基礎(chǔ)定義的，結(jié)果集本身并沒(méi)有順序可言，ORDER BY 僅僅是最終呈現(xiàn)結(jié)果的順序。
另一方面，從邏輯語(yǔ)義上說(shuō)，SELECT 語(yǔ)句的各個(gè)部分可以看作是按以下順序“執(zhí)行”的：
Figure 3. SQL 各部分的邏輯執(zhí)行順序
注意到窗口函數(shù)的求值僅僅位于 ORDER BY 之前，而位于 SQL 的絕大部分之后。這也和窗口函數(shù)只附加、不修改的語(yǔ)義是呼應(yīng)的——結(jié)果集在此時(shí)已經(jīng)確定好了，再依此計(jì)算窗口函數(shù)。別再 select * 了，送你 12 個(gè)查詢(xún)技巧，推薦看下。
窗口函數(shù)的執(zhí)行
窗口函數(shù)經(jīng)典的執(zhí)行方式分為排序和函數(shù)求值這 2 步。
Figure 4. 一個(gè)窗口函數(shù)的執(zhí)行過(guò)程，通常分為排序和求值 2 步
窗口定義中的 PARTITION BY 和 ORDER BY 都很容易通過(guò)排序完成。例如，對(duì)于窗口 PARTITION BY a, b ORDER BY c, d，我們可以對(duì)輸入數(shù)據(jù)按 (a,b,c,d)(a,b,c,d) 或 (b,a,c,d)(b,a,c,d) 做排序，之后數(shù)據(jù)就排列成 Figure 1 中那樣了。
接下來(lái)考慮：如何處理 Frame？
- 對(duì)于整個(gè)分區(qū)的 Frame（例如 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING），只要對(duì)整個(gè)分區(qū)計(jì)算一次即可，沒(méi)什么好說(shuō)的；
- 對(duì)于逐漸增長(zhǎng)的 Frame（例如 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），可以用 Aggregator 維護(hù)累加的狀態(tài)，這也很容易實(shí)現(xiàn)；
- 對(duì)于滑動(dòng)的 Frame（例如 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING）相對(duì)困難一些。一種經(jīng)典的做法是要求 Aggregator 不僅支持增加還支持刪除（Removable），這可能比你想的要更復(fù)雜，例如考慮下 MAX() 的實(shí)現(xiàn)。
窗口函數(shù)的優(yōu)化
對(duì)于窗口函數(shù)，優(yōu)化器能做的優(yōu)化有限。這里為了行文的完整性，仍然做一個(gè)簡(jiǎn)要的說(shuō)明。
通常，我們首先會(huì)把窗口函數(shù)從 Project 中抽取出來(lái)，成為一個(gè)獨(dú)立的算子稱(chēng)之為 Window。
Figure 5. 窗口函數(shù)的優(yōu)化過(guò)程
有時(shí)候，一個(gè) SELECT 語(yǔ)句中包含多個(gè)窗口函數(shù)，它們的窗口定義（OVER 子句）可能相同、也可能不同。顯然，對(duì)于相同的窗口，完全沒(méi)必要再做一次分區(qū)和排序，我們可以將它們合并成一個(gè) Window 算子。
對(duì)于不同的窗口，最樸素地，我們可以將其全部分成不同的 Window，如上圖所示。實(shí)際執(zhí)行時(shí)，每個(gè) Window 都需要先做一次排序，代價(jià)不小。
那是否可能利用一次排序計(jì)算多個(gè)窗口函數(shù)呢？某些情況下，這是可能的。例如本文例子中的 2 個(gè)窗口函數(shù)：
- ... ROW_NUMBER() OVER (PARTITION BY dealer_id ORDER BY sales) AS rank,
- AVG(sales) OVER (PARTITION BY dealer_id) AS avgsales ...
雖然這 2 個(gè)窗口并非完全一致，但是 AVG(sales) 不關(guān)心分區(qū)內(nèi)的順序，完全可以復(fù)用 ROW_NUMBER() 的窗口。
窗口函數(shù)的并行執(zhí)行
現(xiàn)代 DBMS 大多支持并行執(zhí)行。對(duì)于窗口函數(shù)，由于各個(gè)分區(qū)之間的計(jì)算完全不相關(guān)，我們可以很容易地將各個(gè)分區(qū)分派給不同的節(jié)點(diǎn)（線(xiàn)程），從而達(dá)到分區(qū)間并行。
但是，如果窗口函數(shù)只有一個(gè)全局分區(qū)（無(wú) PARTITION BY 子句），或者分區(qū)數(shù)量很少、不足以充分并行時(shí)，怎么辦呢？上文中我們提到的 Removable Aggregator 的技術(shù)顯然無(wú)法繼續(xù)使用了，它依賴(lài)于單個(gè) Aggregator 的內(nèi)部狀態(tài)，很難有效地并行起來(lái)。
TUM 的這篇論文中提出使用線(xiàn)段樹(shù)（Segment Tree）實(shí)現(xiàn)高效的分區(qū)內(nèi)并行。線(xiàn)段樹(shù)是一個(gè) N 叉樹(shù)數(shù)據(jù)結(jié)構(gòu)，每個(gè)節(jié)點(diǎn)包含當(dāng)前節(jié)點(diǎn)下的部分聚合結(jié)果。
下圖是一個(gè)使用二叉線(xiàn)段樹(shù)計(jì)算 SUM() 的例子。例如下圖中第三行的 1212，表示葉節(jié)點(diǎn) 5+75+7 的聚合結(jié)果；而它上方的 2525 表示葉節(jié)點(diǎn) 5+7+3+105+7+3+10 的聚合結(jié)果。
Figure 6. 使用線(xiàn)段樹(shù)計(jì)算給定范圍的總和
假設(shè)當(dāng)前 Frame 是第 2 到第 8 行，即需要計(jì)算 7+3+10+...+47+3+10+...+4 區(qū)間之和。有了線(xiàn)段樹(shù)以后，我們可以直接利用 7+13+207+13+20 （圖中紅色字體）計(jì)算出聚合結(jié)果。
線(xiàn)段樹(shù)可以在 O(nlogn)O(nlog?n) 時(shí)間內(nèi)構(gòu)造，并能在 O(logn)O(log?n) 時(shí)間內(nèi)查詢(xún)?nèi)我鈪^(qū)間的聚合結(jié)果。更棒的是，不僅查詢(xún)可以多線(xiàn)程并發(fā)互不干擾，而且線(xiàn)段樹(shù)的構(gòu)造過(guò)程也能被很好地并行起來(lái)。
最后，關(guān)注公眾號(hào)Java技術(shù)棧，在后臺(tái)回復(fù)：面試，可以獲取我整理的 MySQL 系列面試題和答案，非常齊全。
分享文章：SQL窗口函數(shù)是什么？漲見(jiàn)識(shí)了！
分享網(wǎng)址：http://m.5511xx.com/article/dhsdico.html
