一、視窗函數的使用場景
作為IT人士，日常工作中經常會遇到類似這樣的需求：
醫院看病，怎樣知道上次就醫距現在的時間？環比如何計算？怎麼樣得到各部門工資排名前N名員工清單?查找各部門每人工資占部門總工資的百分比？
對于這樣的需求，使用傳統的SQL實作起來比較困難。這類需求都有一個共同的特點，需要在單表中滿足某些條件的記錄集内部做一些函數操作，不是簡單的表連接配接，也不是簡單的聚合可以實作的，通常會讓寫SQL的同學焦頭爛額、絞盡腦汁，費了大半天時間寫出來一堆長長的晦澀難懂的自連接配接SQL，且性能低下，難以維護。
要解決此類問題，最友善的就是使用視窗函數。
二、MySQL視窗函數簡介
MySQL從8.0開始支援視窗函數，這個功能在大多商業資料庫和部分開源資料庫中早已支援，有的也叫分析函數。
什麼叫視窗?
視窗的概念非常重要，它可以了解為記錄集合，視窗函數也就是在滿足某種條件的記錄集合上執行的特殊函數。對于每條記錄都要在此視窗内執行函數，有的函數随着記錄不同，視窗大小都是固定的，這種屬于靜态視窗；有的函數則相反，不同的記錄對應着不同的視窗，這種動态變化的視窗叫滑動視窗。
視窗函數和普通聚合函數也很容易混淆，二者差別如下：
- 聚合函數是将多條記錄聚合為一條；而視窗函數是每條記錄都會執行，有幾條記錄執行完還是幾條。
- 聚合函數也可以用于視窗函數中，這個後面會舉例說明。
下面是一個視窗函數的簡單例子：
上面例子中，row_number()over(partition by user_no order by amount desc)這部分都屬于視窗函數，它的功能是顯示每個使用者按照訂單金額從大到小排序的序号。
按照功能劃分，可以把MySQL支援的視窗函數分為如下幾類：
- 序号函數：row_number() / rank() / dense_rank()
- 分布函數：percent_rank() / cume_dist()
- 前後函數：lag() / lead()
- 頭尾函數：first_val() / last_val()
- 其他函數：nth_value() / nfile()
三、視窗函數如何使用
視窗函數的基本用法如下：
函數名（[expr]） over子句
其中，over是關鍵字，用來指定函數執行的視窗範圍，如果後面括号中什麼都不寫，則意味着視窗包含滿足where條件的所有行，視窗函數基于所有行進行計算；如果不為空，則支援以下四種文法來設定視窗：
- window_name：給視窗指定一個别名，如果SQL中涉及的視窗較多，采用别名可以看起來更清晰易讀。上面例子中如果指定一個别名w，則改寫如下：
select * from
(
select row_number()over w as row_num,
order_id,user_no,amount,create_date
from order_tab
WINDOW w AS (partition by user_no order by amount desc)
)t ; - partition子句：視窗按照那些字段進行分組，視窗函數在不同的分組上分别執行。上面的例子就按照使用者id進行了分組。在每個使用者id上，按照order by的順序分别生成從1開始的順序編号。
- order by子句：按照哪些字段進行排序，視窗函數将按照排序後的記錄順序進行編号。可以和partition子句配合使用，也可以單獨使用。上例中二者同時使用，如果沒有partition子句，則會按照所有使用者的訂單金額排序來生成序号。
- frame子句：frame是目前分區的一個子集，子句用來定義子集的規則，通常用來作為滑動視窗使用。比如要根據每個訂單動态計算包括本訂單和按時間順序前後兩個訂單的平均訂單金額，則可以設定如下frame子句來建立滑動視窗：
從結果可以看出，order_id為5訂單屬于邊界值，沒有前一行，是以平均訂單金額為(900+800)/2=850；order_id為4的訂單前後都有訂單，是以平均訂單金額為（900+800+300）/3=666.6667，以此類推就可以得到一個基于滑動視窗的動态平均訂單值。此例中，視窗函數用到了傳統的聚合函數avg()，用來計算動态的平均值。
對于滑動視窗的範圍指定，有兩種方式，基于行和基于範圍，具體差別如下：
基于行：
通常使用BETWEEN frame_start AND frame_end文法來表示行範圍，frame_start和frame_end可以支援如下關鍵字，來确定不同的動态行記錄：
CURRENT ROW 邊界是目前行，一般和其他範圍關鍵字一起使用
UNBOUNDED PRECEDING 邊界是分區中的第一行
UNBOUNDED FOLLOWING 邊界是分區中的最後一行
expr PRECEDING 邊界是目前行減去expr的值
expr FOLLOWING 邊界是目前行加上expr的值
比如，下面都是合法的範圍：
rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 視窗範圍是目前行、前一行、後一行一共三行記錄。
rows UNBOUNDED FOLLOWING 視窗範圍是目前行到分區中的最後一行。
rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 視窗範圍是目前分區中所有行，等同于不寫。
基于範圍：
和基于行類似，但有些範圍不是直接可以用行數來表示的，比如希望視窗範圍是一周前的訂單開始，截止到目前行，則無法使用rows來直接表示，此時就可以使用範圍來表示視窗：INTERVAL 7 DAY PRECEDING。Linux中常見的最近1分鐘、5分鐘負載是一個典型的應用場景。
有的函數不管有沒有frame子句，它的視窗都是固定的，也就是前面介紹的靜态視窗，這些函數包括如下：
- CUME_DIST()
- DENSE_RANK()
- LAG()
- LEAD()
- NTILE()
- PERCENT_RANK()
- RANK()
- ROW_NUMBER()
接下來我們以上例的訂單表為例，來介紹每個函數的使用方法。表中各字段含義按順序分别為訂單号、使用者id、訂單金額、訂單建立日期。
四、序号函數
序号函數——row_number() / rank() / dense_rank()。
- 用途：顯示分區中的目前行号
- 使用場景：希望查詢每個使用者訂單金額最高的前三個訂單
此時可以使用ROW_NUMBER()函數按照使用者進行分組并按照訂單日期進行由大到小排序，最後查找每組中序号<=3的記錄。
對于使用者‘002’的訂單，大家發現訂單金額為800的有兩條，序号随機排了1和2，但很多情況下二者應該是并列第一，而訂單為600的序号則可能是第二名，也可能為第三名，這時候，row_number就不能滿足需求，需要rank和dense_rank出場。
這兩個函數和row_number()非常類似，隻是在出現重複值時處理邏輯有所不同。
上面例子我們稍微改一下，需要查詢不同使用者的訂單中，按照訂單金額進行排序，顯示出相應的排名序号，SQL中用row_number() / rank() / dense_rank()分别顯示序号，我們看一下有什麼差别：
上面紅色粗體顯示了三個函數的差別，row_number()在amount都是800的兩條記錄上随機排序，但序号按照1、2遞增，後面amount為600的的序号繼續遞增為3，中間不會産生序号間隙；rank()/dense_rank()則把amount為800的兩條記錄序号都設定為1，但後續amount為600的需要則分别設定為3（rank）和2（dense_rank）。即rank（）會産生序号相同的記錄，同時可能産生序号間隙；而dense_rank（）也會産生序号相同的記錄，但不會産生序号間隙。
五、分布函數
分布函數——percent_rank()/cume_dist()。
percent_rank()
-
用途：和之前的RANK()函數相關，每行按照如下公式進行計算：
(rank - 1) / (rows - 1)
其中，rank為RANK()函數産生的序号，rows為目前視窗的記錄總行數。
- 應用場景：沒想出來……感覺不太常用，看個例子吧↓
從結果看出，percent列按照公式(rank - 1) / (rows - 1)帶入rank值（row_num列）和rows值（user_no為‘001’和‘002’的值均為5）。
cume_dist()
- 用途：分組内小于等于目前rank值的行數/分組内總行數，這個函數比percen_rank使用場景更多。
- 應用場景：大于等于目前訂單金額的訂單比例有多少。
SQL如下：
列cume顯示了預期的資料分布結果。
六、前後函數
前後函數——lead(n)/lag(n)。
- 用途：分區中位于目前行前n行（lead）/後n行(lag)的記錄值。
- 使用場景：查詢上一個訂單距離目前訂單的時間間隔。
SQL如下：
内層SQL先通過lag函數得到上一次訂單的日期，外層SQL再将本次訂單和上次訂單日期做差得到時間間隔diff。
七、頭尾函數
頭尾函數——first_val(expr)/last_val(expr)。
- 用途：得到分區中的第一個/最後一個指定參數的值。
- 使用場景：查詢截止到目前訂單，按照日期排序第一個訂單和最後一個訂單的訂單金額。
SQL如下：
結果和預期一緻，比如order_id為4的記錄，first_amount和last_amount分别記錄了使用者‘001’截止到時間2018-01-03 00:00:00為止，第一條訂單金額100和最後一條訂單金額800，注意這裡是按時間排序的最早訂單和最晚訂單，并不是最小金額和最大金額訂單。
八、其他函數
其他函數——nth_value(expr,n)/nfile(n）。
nth_value(expr,n)
- 用途：傳回視窗中第N個expr的值，expr可以是表達式，也可以是列名。
- 應用場景：每個使用者訂單中顯示本使用者金額排名第二和第三的訂單金額。
SQL如下：
nfile(n)
- 用途：将分區中的有序資料分為n個桶，記錄桶号。
- 應用場景：将每個使用者的訂單按照訂單金額分成3組。
SQL如下：
此函數在資料分析中應用較多，比如由于資料量大，需要将資料平均配置設定到N個并行的程序分别計算，此時就可以用NFILE(N)對資料進行分組，由于記錄數不一定被N整除，是以資料不一定完全平均，然後将不同桶号的資料再配置設定。
九、聚合函數作為視窗函數
- 用途：在視窗中每條記錄動态應用聚合函數(sum/avg/max/min/count)，可以動态計算在指定的視窗内的各種聚合函數值。
- 應用場景：每個使用者按照訂單id，截止到目前的累計訂單金額/平均訂單金額/最大訂單金額/最小訂單金額/訂單數是多少？
SQL如下：