一般的商業(yè)數(shù)據(jù)庫（其實(shí)也就是DB2，Oracle，SQL Server）都具備窗口函數(shù)這個(gè)功能，只不過名稱不同，我比較熟悉的Oracle叫做分析函數(shù)，DB2好像叫做OLAP函數(shù)？

MySQL以前是不支持這個(gè)的，很多時(shí)候要實(shí)現(xiàn)一些功能就會(huì)非常麻煩，不過在8.0加入了這個(gè)功能，而且還比其他數(shù)據(jù)庫多了一些有趣的支持模式。

以下內(nèi)容部分參考：
https://zhuanlan.zhihu.com/p/49082967

窗口的概念

什么叫窗口?

窗口的概念非常重要，它可以理解為記錄集合，窗口函數(shù)也就是在滿足某種條件的記錄集合上執(zhí)行的特殊函數(shù)，對(duì)于每條記錄都要在此窗口內(nèi)執(zhí)行函數(shù)，有的函數(shù)，隨著記錄不同，窗口大小都是固定的，這種屬于靜態(tài)窗口；有的函數(shù)則相反，不同的記錄對(duì)應(yīng)著不同的窗口，這種動(dòng)態(tài)變化的窗口叫滑動(dòng)窗口。

窗口函數(shù)和普通聚合函數(shù)也很容易混淆，二者區(qū)別如下：

聚合函數(shù)是將多條記錄聚合為一條；而窗口函數(shù)是每條記錄都會(huì)執(zhí)行，有幾條記錄執(zhí)行完還是幾條。
聚合函數(shù)也可以用在窗口函數(shù)中，這個(gè)后面會(huì)舉例說明。

窗口函數(shù)的格式：

函數(shù)名（[expr]） over子句

其中，over是關(guān)鍵字，用來指定函數(shù)執(zhí)行的窗口范圍，如果后面括號(hào)中什么都不寫，則意味著窗口包含滿足where條件的所有行，窗口函數(shù)基于所有行進(jìn)行計(jì)算；如果不為空，則支持以下四種語法來設(shè)置窗口：

window_name：給窗口指定一個(gè)別名，如果SQL中涉及的窗口較多，采用別名可以看起來更清晰易讀（主要是方便），例如：

SELECT
    rank ( ) over w1 
FROM
    employee window w1 AS ( PARTITION BY Company ORDER BY Salary DESC )

很明顯，from table后面加了一個(gè)關(guān)鍵字WINDOW，后面跟了一個(gè)子句，這樣在select中就可以使用這個(gè)w1作為窗口了。

partition子句：窗口按照那些字段進(jìn)行分組，窗口函數(shù)在不同的分組上分別執(zhí)行。比如上面例子中，就是用Company字段分組。（跟group by一個(gè)意思，但是這里用PARTITION BY）

order by子句：按照哪些字段進(jìn)行排序，窗口函數(shù)將按照排序后的記錄順序進(jìn)行編號(hào)?？梢院蚿artition子句配合使用，也可以單獨(dú)使用。

frame子句：frame是當(dāng)前分區(qū)的一個(gè)子集，子句用來定義子集的規(guī)則，通常用來作為滑動(dòng)窗口使用，這是個(gè)很有意思的特性，不同于Oracle，例如：

SELECT
    avg( Salary ) over w1,
    Company,
    Salary 
FROM
    employee window w1 AS ( PARTITION BY Company ORDER BY Salary DESC rows BETWEEN 1 preceding AND unbounded following )

這條語句的意思是，以Company分組窗口，Salary排序，從當(dāng)前行的前一行到該組的最后一行，取平均Salary（工資）。
則有如下圖：

frame子句

注意Microsoft中的平均Salary，第一個(gè)行是60000，他沒有前一行，最后一行是50000（因?yàn)槭歉鶕?jù)Company分組了的），那么平均Salary就是
(60000+60000+55000+50000)/4 = 56250。
而第二行也是60000，他的上一行是60000，到最后一行求平均值就是：
(60000+60000+55000+50000)/4 = 56250（不要混淆）
第三行是55000，他的上一行是60000，到最后一行，平均值就是：
(660000+55000+50000)/3 = 55000.

這種窗口范圍的限定，因?yàn)槭请S著當(dāng)前查詢行的變化而變化的，就叫做動(dòng)態(tài)窗口，對(duì)于動(dòng)態(tài)窗口的范圍指定，有兩種方式，基于行和基于范圍，具體區(qū)別如下：

基于行：通常使用BETWEEN frame_start AND frame_end語法來表示行范圍，frame_start和frame_end可以支持如下關(guān)鍵字，來確定不同的動(dòng)態(tài)行記錄
CURRENT ROW 邊界是當(dāng)前行，一般和其他范圍關(guān)鍵字一起使用

UNBOUNDED PRECEDING 邊界是分區(qū)中的第一行

UNBOUNDED FOLLOWING 邊界是分區(qū)中的最后一行

expr PRECEDING 邊界是當(dāng)前行減去expr的值

expr FOLLOWING 邊界是當(dāng)前行加上expr的值

比如，下面都是合法的范圍：

rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗口范圍是當(dāng)前行、前一行、后一行一共三行記錄。

rows UNBOUNDED FOLLOWING 窗口范圍是當(dāng)前行到分區(qū)中的最后一行

rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 窗口范圍是當(dāng)前分區(qū)中所有行，等同于不寫。

基于范圍：和基于行類似，但有些范圍不是直接可以用行數(shù)來表示的，比如希望窗口范圍是一周前的訂單開始，截止到當(dāng)前行，則無法使用rows來直接表示，此時(shí)就可以使用范圍來表示窗口：INTERVAL 7 DAY PRECEDING。Linux中常見的最近1分鐘、5分鐘負(fù)載是一個(gè)典型的應(yīng)用場(chǎng)景。

有的函數(shù)不管有沒有frame子句，它的窗口都是固定的，也就是前面介紹的靜態(tài)窗口，這些函數(shù)包括如下：

CUME_DIST()
DENSE_RANK()
LAG()
LEAD()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

常用函數(shù)

窗口函數(shù)的核心還是在over前面的函數(shù)上，除了常用的聚合函數(shù)之外，還可以用一些特定的窗口函數(shù)，下面舉例：

序號(hào)函數(shù)--row_number()/rank()/dense_rank()

用途：顯示分區(qū)中的當(dāng)前行號(hào)

SELECT
    row_number ( ) over w1,
    Company,
    Salary 
FROM
    employee window w1 AS ( PARTITION BY Company ORDER BY Salary DESC rows BETWEEN 1 preceding AND unbounded following )

row_number

可以看到，根據(jù)Company進(jìn)行分組之后，按照Salary倒序排列，給了行號(hào)，依次是1，2，3，4，如果有相同的，那么隨機(jī)處理。

rank和dense_rank就是用來處理有相同情況的：

SELECT
    row_number ( ) over w1,
    rank ( ) over w1,
    dense_rank ( ) over w1,
    Company,
    Salary 
FROM
    employee window w1 AS ( PARTITION BY Company ORDER BY Salary DESC rows BETWEEN 1 preceding AND unbounded following )

image.png

還是關(guān)注Microsoft，可以看到，rank是把相同Salary的取了同樣的排名，再下一位的，按照實(shí)際行號(hào)直接取排名（于是變成了3），而dense_rank則是依次順延，下一個(gè)是第二高的Salary，那么就是2.

dense_rank，密集排序，可以理解為需要更密集一點(diǎn)展示排名，所以就不按照行號(hào)來排。

分布函數(shù)--percent_rank()/cume_dist()

percent_rank()

用途：和之前的RANK()函數(shù)相關(guān)，每行按照如下公式進(jìn)行計(jì)算：
(rank - 1) / (rows - 1)
其中，rank為RANK()函數(shù)產(chǎn)生的序號(hào)，rows為當(dāng)前窗口的記錄總行數(shù)
該函數(shù)可以用來計(jì)算分位數(shù)。

例子：(這里沒有使用窗口的別名，感受一下正常使用這個(gè)函數(shù)的格式)

SELECT
    percent_rank ( ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

結(jié)果有：

percent_rank

很明顯，按照倒序排列，統(tǒng)計(jì)學(xué)中有個(gè)分位數(shù)的概念（有序數(shù)列中的百分之多少的數(shù)）。
總共5行，第2行就是(2-1)/(5-1) = 0.25，正好是25分位數(shù)，以此類推。

cume_dist()

用途：分組內(nèi)小于等于當(dāng)前rank值的行數(shù)/分組內(nèi)總行數(shù)。

SELECT
    cume_dist ( ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

cume_dist

注意是按照rank值來求值，而不是實(shí)際大小。

以第二行為例，rank值小于等于他的有第一行和第二行，總行數(shù)是5，那么有：2/5=0.4，所以就是0.4.

前后函數(shù)--lead(字段，n)/lag(字段，n)

這個(gè)是帶參數(shù)的，要注意。
用途：分區(qū)中位于當(dāng)前行前n行（lead）/后n行(lag)的記錄值

SELECT
    lag ( salary, 1 ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

lag

從圖中可以看到，lag取的是當(dāng)前行的上一個(gè)行的對(duì)應(yīng)字段的值，當(dāng)然我們也可以對(duì)lag中的字段進(jìn)行計(jì)算（非聚合）。
lead就是當(dāng)前行的下一個(gè)行的值，雖然有點(diǎn)難理解，但是只能記住，如下圖：

SELECT
    lead ( salary - 10000, 1 ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

lead

頭尾函數(shù)--first_value (expr)/last_value(expr)

用途：得到分區(qū)中的第一個(gè)/最后一個(gè)指定參數(shù)的值

很好理解，就是求當(dāng)前分組的第一個(gè)值和最后一個(gè)值（這個(gè)有order by決定。），注意求的是對(duì)應(yīng)的expr（字段名）的值，而不是其他的值。

SELECT
    first_value ( id ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

first_value

意思就是，當(dāng)前分組中，按照Salary倒序排列，第一行是70000，這行對(duì)應(yīng)的id是12.

SELECT
    last_value ( Salary ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

last_value

而last中需要注意，指的是最后一行，是當(dāng)前分組的最后一行，所以會(huì)出現(xiàn)如上的情況。

所以不能用于最大值最小值判斷。

nth_value(expr,n)/ntile(n）

nth_value(expr,n)

用途：返回窗口中第N個(gè)expr的值，expr可以是表達(dá)式，也可以是列名

SELECT
    nth_value ( Salary,3 ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

nth_value

第三個(gè)是60000的薪水，那么就顯示60000，注意是分組內(nèi)的每一行都有值。

ntile(n)

用途：將分區(qū)中的有序數(shù)據(jù)分為n個(gè)桶，記錄桶號(hào)。

SELECT
    ntile ( 3 ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

ntile

數(shù)據(jù)分桶，根據(jù)先后順序，把行分為n個(gè)桶，優(yōu)先滿足前面的行，圖中要分為3個(gè)桶，因此最后一桶只有1行。
桶號(hào)就是1，2，3……

聚合函數(shù)作為窗口函數(shù)

用途：在窗口中每條記錄動(dòng)態(tài)應(yīng)用聚合函數(shù)(sum/avg/max/min/count)，可以動(dòng)態(tài)計(jì)算在指定的窗口內(nèi)的各種聚合函數(shù)值。
這就很好理解了，以avg為例：

SELECT
    avg( Salary ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

avg( Salary )

按照分組的每一行，求累計(jì)的平均值。

通過這個(gè)，應(yīng)該能明白窗口的含義。（根據(jù)over子句分組之后，挨個(gè)行進(jìn)行select，并執(zhí)行over前面的函數(shù)，因?yàn)閛ver子句已經(jīng)把記錄變成了符合條件的一些行集，所以select的方式就改變了。）

窗口函數(shù)還是非常常用的，簡單總結(jié)一下，備忘。

窗口函數(shù)

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

MySQL-窗口函數(shù)

MySQL-窗口函數(shù)

窗口的概念

常用函數(shù)

序號(hào)函數(shù)--row_number()/rank()/dense_rank()

分布函數(shù)--percent_rank()/cume_dist()

前后函數(shù)--lead(字段，n)/lag(字段，n)

頭尾函數(shù)--first_value (expr)/last_value(expr)

nth_value(expr,n)/ntile(n）

聚合函數(shù)作為窗口函數(shù)

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

MySQL-窗口函數(shù)

窗口的概念

常用函數(shù)

序號(hào)函數(shù)--row_number()/rank()/dense_rank()

分布函數(shù)--percent_rank()/cume_dist()

前后函數(shù)--lead(字段，n)/lag(字段，n)

頭尾函數(shù)--first_value (expr)/last_value(expr)

nth_value(expr,n)/ntile(n）

聚合函數(shù)作為窗口函數(shù)

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

前后函數(shù)--lead(字段，n)/lag(字段，n)