一、什么是開窗函數(shù)

開窗函數(shù)/分析函數(shù)：over()

開窗函數(shù)也叫分析函數(shù)，有兩類：

• 一類是聚合開窗函數(shù)

• 一類是排序開窗函數(shù)

分析函數(shù)用于計算基于組的某種聚合值，它和聚合函數(shù)的不同之處是：對于每個組返回多行，而聚合函數(shù)對于每個組只返回一行。

開窗函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口大小，這個數(shù)據(jù)窗口大小可能會隨著行的變化而變化！

開窗函數(shù)的調(diào)用格式為：

函數(shù)名(列名) OVER(partition by 列名 order by列名)

其中【partition by字段】和【order by 字段】是可選擇的

• partition by 字段：子句進行分組

• order by 字段：子句進行排序

OVER()函數(shù)不能單獨使用，必須跟在 排名函數(shù)（ ROW_NUMBER、DENSE_RANK、RANK、NTILE） 或 聚合函數(shù)（SUM、MAX、MIN、AVG、COUNT）

我們知道聚合函數(shù)對一組值執(zhí)行計算并返回單一的值，如sum()，count()，max()，min()， avg()等，這些函數(shù)常與group by子句連用。除了 COUNT 以外，聚合函數(shù)忽略空值。

但有時候一組數(shù)據(jù)只返回一組值是不能滿足業(yè)務邏輯需求，如各個地區(qū)的前幾名、各個班或各個學科的前幾名。這時候需要每一組返回多個值。用開窗函數(shù)解決這類問題非常方便。

開窗函數(shù)和聚合函數(shù)的區(qū)別：

（1）SQL 標準允許將所有聚合函數(shù)用作開窗函數(shù)，用OVER關(guān)鍵字區(qū)分開窗函數(shù)和聚合函數(shù)。

（2）聚合函數(shù)每組只返回一個值，開窗函數(shù)每組可返回多個值。

開窗函數(shù)與聚合函數(shù)一樣，也是對行集組進行聚合計算，但是它不像普通聚合函數(shù)那樣每組只返回一個值，開窗函數(shù)可以為每組返回多個值，因為開窗函數(shù)所執(zhí)行聚合計算的行集組是窗口。

注：在開窗函數(shù)出現(xiàn)之前存在著很多用 SQL 語句很難解決的問題，很多都要通過復雜的相關(guān)子查詢或者存儲過程來完成。為了解決這些問題，在2003年ISO SQL標準加入了開窗函數(shù)，開窗函數(shù)的使用使得這些經(jīng)典的難題可以被輕松的解決。目前在 MSSQLServer、Oracle、DB2 等主流數(shù)據(jù)庫中都提供了對開窗函數(shù)的支持， MYSQL 8.0后對開窗函數(shù)給予支持。

在ISO SQL規(guī)定了這樣的函數(shù)為開窗函數(shù)，在 Oracle中則被稱為分析函數(shù)，而在DB2中則被稱為OLAP函數(shù)。

二、使用及說明

1. 分區(qū)排序：row_number () over()

2. 幾個排序函數(shù)的區(qū)別

• row_number() over()、順序排序

• rank() over()、跳躍排序

• dense_rank() over()、連續(xù)排序

• ntile() over(), 分組排名

（1） row_number() over()：

對相等的值不進行區(qū)分，其實就是行號，相等的值對應的排名不同，序號從1到n連續(xù)。

ROW_NUMBER() 的應用場景非常多，比如獲取分組內(nèi)排序第一的記錄、獲取一個session中的第一條refer等。

（2） rank() over()：

相等的值排名相同，但若有相等的值，則序號從1到n不連續(xù)。如果有兩個人都排在第3名，則沒有第4名。

RANK（）為每一組的行生成一個序號，與ROW_NUMBER()不同的是如果按照ORDER BY的排序，如果有相同的值會生成相同的序號，并且接下來的序號是不連序的。

（3） dense_rank() over()：

對相等的值排名相同，但序號從1到n連續(xù)。如果有兩個人都排在第一名，則排在第2名（假設僅有1個第二名）的人是第3個人。

（4） ntile( n ) over()：

可以看作是把有序的數(shù)據(jù)集合平均分配到指定的數(shù)量n的桶中,將桶號分配給每一行，排序?qū)臄?shù)字為桶號，序號從1到n連續(xù)。如果不能平均分配，則較小桶號的桶分配額外的行，并且各個桶中能放的數(shù)據(jù)條數(shù)最多相差1。

說明：DENSE_RANK（）和RANK（）類似，不同的是如果有相同的序號，那么接下來的序號不會間斷。也就是說如果兩個相同的行生成序號3，那么接下來生成的序號還是4。

3. 執(zhí)行順序

在使用 row_number() over()函數(shù)時候，over()里頭的分組以及排序的執(zhí)行，晚于 where 、group by、 order by 的執(zhí)行。

4. 其他開窗函數(shù)

1、row_number() over(partition by … order by …)

增加一列,類似與增加偽列

2、rank() over(partition by … order by …)

3、dense_rank() over(partition by … order by …)

rank(): 跳躍排序，如果有兩個第一級時，接下來就是第三級。
dense_rank(): 連續(xù)排序，如果有兩個第一級時，接下來仍然是第二級。

4、count() over(partition by … order by …)

5、max() over(partition by … order by …)

6、min() over(partition by … order by …)

7、sum() over(partition by … order by …)

8、avg() over(partition by … order by …)

9、first_value() over(partition by … order by …)

10、last_value() over(partition by … order by …)

與函數(shù)的功能一致,只是是開窗函數(shù)

11、lag() over(partition by … order by …)

12、lead() over(partition by … order by …)

lag 和lead 可以 獲取結(jié)果集中，按一定排序所排列的當前行的上下相鄰若干offset 的某個行的某個列(不用結(jié)果集的自關(guān)聯(lián)）；
lag ，lead 分別是向前，向后；
lag 和lead 有三個參數(shù)，第一個參數(shù)是列名，第二個參數(shù)是偏移的offset，第三個參數(shù)是 超出記錄窗口時的默認值）

5. 開窗的窗口范圍：

over（order by salary range between 5 preceding and 5 following）：窗口范圍為當前行數(shù)據(jù)幅度減5加5后的范圍內(nèi)的。