參考：
一、mysql窗口函數(shù)簡(jiǎn)介
MySQL從8.0開始支持窗口函數(shù)，這個(gè)功能在大多商業(yè)數(shù)據(jù)庫(kù)和部分開源數(shù)據(jù)庫(kù)中早已支持，有的也叫分析函數(shù)。
窗口：記錄的集合
窗口函數(shù)：在滿足某種條件的記錄集合上執(zhí)行的特殊函數(shù) 要和聚合函數(shù)進(jìn)行區(qū)分
分類：
- 靜態(tài)窗口函數(shù)： 函數(shù)隨著記錄不同，窗口大小都是固定的
- 動(dòng)態(tài)窗口函數(shù)： 不同的記錄對(duì)應(yīng)著不同的窗口，這種動(dòng)態(tài)變化的窗口叫滑動(dòng)窗口
窗口函數(shù)和普通聚合函數(shù)也很容易混淆，二者區(qū)別如下：
- 聚合函數(shù)是將多條記錄聚合為一條；而窗口函數(shù)是每條記錄都會(huì)執(zhí)行，有幾條記錄執(zhí)行完還是幾條。
- 聚合函數(shù)也可以用于窗口函數(shù)中
按照功能劃分，可以把MySQL支持的窗口函數(shù)分為如下幾類：
序號(hào)函數(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í)行時(shí)GROUP BY的聚合過程已經(jīng)完成了，所以不會(huì)再產(chǎn)生數(shù)據(jù)聚合。
一個(gè)窗口函數(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)登陸的用戶的平均時(shí)間間隔
lead(log_time)over(partition user_id order by log_time) 是將log_time 按每個(gè)id分組按登陸時(shí)間排序前置一項(xiàng) 這樣就可以得到每個(gè)用戶每次登陸與前一次的差值
二、窗口函數(shù)的基本用法：
函數(shù)名（[expr]） over子句
其中，over是關(guān)鍵字，用來指定函數(shù)執(zhí)行的窗口范圍，如果后面括號(hào)中什么都不寫，則意味著窗口包含滿足where條件的所有行，窗口函數(shù)基于所有行進(jìn)行計(jì)算；如果不為空，則支持以下四種語法來設(shè)置窗口：
- window_name：給窗口指定一個(gè)別名，如果SQL中涉及的窗口較多，采用別名可以看起來更清晰易讀。上面例子中如果指定一個(gè)別名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子句：窗口按照那些字段進(jìn)行分組，窗口函數(shù)在不同的分組上分別執(zhí)行。上面的例子就按照用戶id進(jìn)行了分組。在每個(gè)用戶id上，按照order by的順序分別生成從1開始的順序編號(hào)。
order by子句：按照哪些字段進(jìn)行排序，窗口函數(shù)將按照排序后的記錄順序進(jìn)行編號(hào)?？梢院蚿artition子句配合使用，也可以單獨(dú)使用。上例中二者同時(shí)使用，如果沒有partition子句，則會(huì)按照所有用戶的登陸時(shí)間排序來生成序號(hào)。
-
frame子句：frame是當(dāng)前分區(qū)的一個(gè)子集，子句用來定義子集的規(guī)則，通常用來作為滑動(dòng)窗口使用。比如要根據(jù)每個(gè)訂單動(dòng)態(tài)計(jì)算包括本訂單和按時(shí)間順序前后兩個(gè)訂單的平均訂單金額，則可以設(shè)置如下frame子句來創(chuàng)建滑動(dòng)窗口：
從結(jié)果可以看出，order_id為5訂單屬于邊界值，沒有前一行，因此平均訂單金額為(900+800)/2=850；order_id為4的訂單前后都有訂單，所以平均訂單金額為（900+800+300）/3=666.6667，以此類推就可以得到一個(gè)基于滑動(dòng)窗口的動(dòng)態(tài)平均訂單值。此例中，窗口函數(shù)用到了傳統(tǒng)的聚合函數(shù)avg()，用來計(jì)算動(dòng)態(tài)的平均值。
基于行
通常使用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。
有的函數(shù)不管有沒有frame子句，它的窗口都是固定的，也就是前面介紹的靜態(tài)窗口，這些函數(shù)包括如下：
- CUME_DIST()
- DENSE_RANK()
- LAG()
- LEAD()
- NTILE()
- PERCENT_RANK()
- RANK()
- ROW_NUMBER()
三、序號(hào)函數(shù)
row_number() / rank() / dense_rank()。
用途：顯示分區(qū)中的當(dāng)前行號(hào)
使用場(chǎng)景：希望查詢每個(gè)用戶訂單金額最高的前三個(gè)訂單
ROW_NUMBER():順序排序——1、2、3
RANK():并列排序，跳過重復(fù)序號(hào)——1、1、3
DENSE_RANK():并列排序，不跳過重復(fù)序號(hào)——1、1、2
四、分布函數(shù)
說實(shí)話沒想到有啥用
分布函數(shù)——percent_rank()/cume_dist()。
percent_rank():
- 用途：每行按照公式(rank-1) / (rows-1)進(jìn)行計(jì)算。其中，rank為RANK()函數(shù)產(chǎn)生的序號(hào)，rows為當(dāng)前窗口的記錄總行數(shù)
cume_dist():
用途：分組內(nèi)小于等于當(dāng)前rank值的行數(shù)/分組內(nèi)總行數(shù)，這個(gè)函數(shù)比percen_rank使用場(chǎng)景更多。
應(yīng)用場(chǎng)景：大于等于當(dāng)前訂單金額的訂單比例有多少。
五、前后函數(shù):lag(expr,n),lead(expr,n)
這個(gè)在R與python中很常見
- 用途：返回位于當(dāng)前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
- 應(yīng)用場(chǎng)景：查詢前1名同學(xué)的成績(jī)和當(dāng)前同學(xué)成績(jī)的差值 或 查詢上一個(gè)訂單距離當(dāng)前訂單的時(shí)間間隔。
六、頭尾函數(shù)：FIRST_VALUE(expr),LAST_VALUE(expr)
用途：得到分區(qū)中的第一個(gè)/最后一個(gè)指定參數(shù)的值。
使用場(chǎng)景：查詢截止到當(dāng)前訂單，按照日期排序第一個(gè)訂單和最后一個(gè)訂單的訂單金額。
七、其他函數(shù)
其他函數(shù)——nth_value(expr,n)/nfile(n）。
其實(shí)與六類似
nth_value(expr,n)
用途：返回窗口中第N個(gè)expr的值，expr可以是表達(dá)式，也可以是列名。
應(yīng)用場(chǎng)景：每個(gè)用戶訂單中顯示本用戶金額排名第二和第三的訂單金額。
nfile(n)
- 用途：將分區(qū)中的有序數(shù)據(jù)分為n個(gè)桶，記錄桶號(hào)。
- 應(yīng)用場(chǎng)景：將每個(gè)用戶的訂單按照訂單金額分成3組。
八、聚合函數(shù)作為窗口函數(shù)
用途：在窗口中每條記錄動(dòng)態(tài)應(yīng)用聚合函數(shù)(sum/avg/max/min/count)，可以動(dòng)態(tài)計(jì)算在指定的窗口內(nèi)的各種聚合函數(shù)值。
應(yīng)用場(chǎng)景：每個(gè)用戶按照訂單id，截止到當(dāng)前的累計(jì)訂單金額/平均訂單金額/最大訂單金額/最小訂單金額/訂單數(shù)是多少？
