Mysql窗口函數(shù)

參考：

MySQL 8.0窗口函數(shù)：用非常規(guī)思維簡易實現(xiàn)SQL需求
數(shù)分面試-SQL篇

一请敦、mysql窗口函數(shù)簡介

MySQL從8.0開始支持窗口函數(shù)，這個功能在大多商業(yè)數(shù)據(jù)庫和部分開源數(shù)據(jù)庫中早已支持，有的也叫分析函數(shù)。

窗口：記錄的集合
窗口函數(shù)：在滿足某種條件的記錄集合上執(zhí)行的特殊函數(shù) 要和聚合函數(shù)進行區(qū)分
分類：

靜態(tài)窗口函數(shù)：函數(shù)隨著記錄不同复唤，窗口大小都是固定的
動態(tài)窗口函數(shù)：不同的記錄對應著不同的窗口夭问，這種動態(tài)變化的窗口叫滑動窗口

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

聚合函數(shù)是將多條記錄聚合為一條晃痴；而窗口函數(shù)是每條記錄都會執(zhí)行残吩，有幾條記錄執(zhí)行完還是幾條。
聚合函數(shù)也可以用于窗口函數(shù)中

按照功能劃分倘核，可以把MySQL支持的窗口函數(shù)分為如下幾類：

序號函數(shù)：row_number() / rank() / dense_rank()
分布函數(shù)：percent_rank() / cume_dist()
前后函數(shù)：lag() / lead()
頭尾函數(shù)：first_val() / last_val()
其他函數(shù)：nth_value() / nfile()

原因就在于窗口函數(shù)的執(zhí)行順序（邏輯上的）是在FROM泣侮，JOIN，WHERE紧唱，GROUP　BY活尊，HAVING之后，在ORDER　BY漏益，LIMIT蛹锰，SELECT　DISTINCT之前。它執(zhí)行時GROUP　BY的聚合過程已經(jīng)完成了绰疤，所以不會再產(chǎn)生數(shù)據(jù)聚合铜犬。

一個窗口函數(shù)的例子

select user_id,avg(diff)
from
(
    select user_id,lead(log_time)over(partition user_id order by log_time) - log_time as diff
    from user_log
)t
where datediff(now(),t.log_time)<=30
group by user_id

以上代碼是得到30天之內(nèi)登陸的用戶的平均時間間隔
lead(log_time)over(partition user_id order by log_time) 是將log_time 按每個id分組按登陸時間排序前置一項這樣就可以得到每個用戶每次登陸與前一次的差值

二、窗口函數(shù)的基本用法：

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

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

window_name：給窗口指定一個別名呐粘，如果SQL中涉及的窗口較多满俗，采用別名可以看起來更清晰易讀。上面例子中如果指定一個別名w作岖，則改寫如下：

select user_id,avg(diff)
from
(
    select user_id,lead(log_time) over w - log_time as diff
    from user_log
    WINDOW w AS(partition user_id order by log_time)
)t
where datediff(now(),t.log_time)<=30
group by user_id

partition子句：窗口按照那些字段進行分組唆垃，窗口函數(shù)在不同的分組上分別執(zhí)行。上面的例子就按照用戶id進行了分組痘儡。在每個用戶id上辕万，按照order by的順序分別生成從1開始的順序編號。
order by子句：按照哪些字段進行排序沉删，窗口函數(shù)將按照排序后的記錄順序進行編號渐尿。可以和partition子句配合使用矾瑰，也可以單獨使用砖茸。上例中二者同時使用，如果沒有partition子句殴穴，則會按照所有用戶的登陸時間排序來生成序號凉夯。
frame子句：frame是當前分區(qū)的一個子集，子句用來定義子集的規(guī)則采幌，通常用來作為滑動窗口使用劲够。比如要根據(jù)每個訂單動態(tài)計算包括本訂單和按時間順序前后兩個訂單的平均訂單金額，則可以設(shè)置如下frame子句來創(chuàng)建滑動窗口：

從結(jié)果可以看出休傍，order_id為5訂單屬于邊界值征绎，沒有前一行，因此平均訂單金額為(900+800)/2=850磨取；order_id為4的訂單前后都有訂單人柿，所以平均訂單金額為（900+800+300）/3=666.6667，以此類推就可以得到一個基于滑動窗口的動態(tài)平均訂單值寝衫。此例中顷扩，窗口函數(shù)用到了傳統(tǒng)的聚合函數(shù)avg()，用來計算動態(tài)的平均值慰毅。

基于行
通常使用BETWEEN frame_start AND frame_end語法來表示行范圍隘截，frame_start和frame_end可以支持如下關(guān)鍵字，來確定不同的動態(tài)行記錄：

CURRENT ROW 邊界是當前行汹胃，一般和其他范圍關(guān)鍵字一起使用
UNBOUNDED PRECEDING 邊界是分區(qū)中的第一行
UNBOUNDED FOLLOWING 邊界是分區(qū)中的最后一行
expr PRECEDING 邊界是當前行減去expr的值
expr FOLLOWING 邊界是當前行加上expr的值

例如：

rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗口范圍是當前行婶芭、前一行、后一行一共三行記錄着饥。

rows  UNBOUNDED FOLLOWING 窗口范圍是當前行到分區(qū)中的最后一行犀农。

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

上圖的例子就是以上第一行的情況

基于范圍
和基于行類似呵哨，但有些范圍不是直接可以用行數(shù)來表示的赁濒，比如希望窗口范圍是一周前的訂單開始，截止到當前行孟害，則無法使用rows來直接表示拒炎，此時就可以使用范圍來表示窗口：INTERVAL 7 DAY PRECEDING。

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

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

三谎柄、序號函數(shù)

row_number() / rank() / dense_rank()丁侄。

用途：顯示分區(qū)中的當前行號
使用場景：希望查詢每個用戶訂單金額最高的前三個訂單

ROW_NUMBER():順序排序——1、2朝巫、3
RANK():并列排序鸿摇，跳過重復序號——1、1捍歪、3
DENSE_RANK():并列排序户辱，不跳過重復序號——1、1糙臼、2

四、分布函數(shù)

說實話沒想到有啥用

分布函數(shù)——percent_rank()/cume_dist()恩商。

percent_rank():

用途：每行按照公式(rank-1) / (rows-1)進行計算变逃。其中，rank為RANK()函數(shù)產(chǎn)生的序號怠堪，rows為當前窗口的記錄總行數(shù)

cume_dist():

用途：分組內(nèi)小于等于當前rank值的行數(shù)/分組內(nèi)總行數(shù)揽乱，這個函數(shù)比percen_rank使用場景更多。
應用場景：大于等于當前訂單金額的訂單比例有多少粟矿。

五凰棉、前后函數(shù):lag(expr,n),lead(expr,n)

這個在R與python中很常見

用途：返回位于當前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
應用場景：查詢前1名同學的成績和當前同學成績的差值或查詢上一個訂單距離當前訂單的時間間隔。

六陌粹、頭尾函數(shù)：FIRST_VALUE(expr),LAST_VALUE(expr)

用途：得到分區(qū)中的第一個/最后一個指定參數(shù)的值撒犀。
使用場景：查詢截止到當前訂單，按照日期排序第一個訂單和最后一個訂單的訂單金額掏秩。

七或舞、其他函數(shù)

其他函數(shù)——nth_value(expr,n)/nfile(n）。
其實與六類似

nth_value(expr,n)

用途：返回窗口中第N個expr的值蒙幻，expr可以是表達式映凳，也可以是列名。
應用場景：每個用戶訂單中顯示本用戶金額排名第二和第三的訂單金額邮破。

nfile(n)

用途：將分區(qū)中的有序數(shù)據(jù)分為n個桶诈豌，記錄桶號仆救。
應用場景：將每個用戶的訂單按照訂單金額分成3組。

八矫渔、聚合函數(shù)作為窗口函數(shù)

用途：在窗口中每條記錄動態(tài)應用聚合函數(shù)(sum/avg/max/min/count)派桩，可以動態(tài)計算在指定的窗口內(nèi)的各種聚合函數(shù)值。
應用場景：每個用戶按照訂單id蚌斩，截止到當前的累計訂單金額/平均訂單金額/最大訂單金額/最小訂單金額/訂單數(shù)是多少铆惑？