一、什么是窗口函數

窗口函數又叫做開窗函數，是用于解決復雜報表統計需求的功能強大的一種分析函數。窗口函數通常用于計算基于組的某種聚合值，它和聚合函數的不同之處在于：窗口函數對于每個組返回多行，而聚合函數對于每個組只返回一行。

二、窗口函數基本語句形式

窗口函數帶有一個開窗函數over()，包含三個分析子句:

三、窗口函數類別

1、排名函數

SQL標準支持4種用于排名計算的窗口函數。分別為：ROW_NUMBER、NTILE、以及RANK和DENSE_RANK。

在SQL標準中，前兩個是一類，后兩個是另一類。

ROW_NUMBER：表示根據col1分組，在分組內部根據col2排序，而此函數計算的值就表示每組內部排序后的順序編號(組內是連續且唯一的)。

NTILE函數把窗口分區里的數據行分成數量大致相等的塊(根據輸入的塊數和指定的窗口排序)。

RANK(排名)與DENSE_RANK(密集排名)函數的計算和ROW_NUMBER函數類似，唯一不同之處在于，它們在窗口分區內生成的值不必是唯一的。

如果窗口排序方向是升序，那么RANK函數計算分區內排序值比當前行小的行的數量，在次數量上加1.就是當前行的排名;

DENSE_RANK函數計算分區內相異的(distinct)排序值比當前行小的行的數量，在此數量上加1.就是當前行的排名。

如果窗口排序方向是降序，那么RANK函數計算分區內排序屬性比當前行大的行的數量，在此數量上加1.就是當前行的排名;

DENSE_RANK函數計算分區內相異的(distinct)排序值比當前行大的行的數量，在此數量上加1.就是當前行的排名。

2、分布函數

窗口分布函數主要是為靜態統計服務提供數據的分布情況。SQL Server 2012引入了兩種窗口分布函數的支持：排名分布函數和逆分布函數。

排名分布函數有PERCENT_RANK(百分位排名)和CUME_DIST(累積分布)兩種，逆分布函數也有兩個，分別是：PERCENT_CONT(百分位連續)和PERCENTILE_DISC(百分位離散)。

根據標準SQL，分布函數計算數據行在窗口分區中的相對排名，將它表示為介于0~1之間的比值——通常它看做百分比。

假設rk 為數據行的RANK值，RANK函數的窗口描述和分布函數的窗口描述是相同的。假設nr為窗口分區內數據行的行數，np為領先或與當前行的排序值相同的行的數目(為比當前rk減1大的最小rk值，如果當前rk是最大值，則np等于nr)。

PERCENT_RANK(百分位排名)計算公式：(rk-1)/(nr-1)

PERCENT_RANK(百分位排名)的計算公式：np/nr。

逆分布函數，一般叫做百分位，通常會將它執行的計算當作是排名分布函數的倒數。

PERCENTILE_DISC(百分位離散)函數，其中DISC為離散分布模型，返回組中第一個符合條件的值，條件為：其累計分布(CUME_DIST函數)>=輸入值。

PERCENT_CONT(百分位連續)函數，其中CONT為連續分布函數。

3、偏移函數

偏移函數分為兩種類型，一種是偏移量是相對于當前行的，LAG和LEAD函數;另一個偏移函數的偏移量是相對于窗口框架的開始和結尾的，包括FIRST_VALUE、LAST_VALUE和NTH_VALUE。

LAG和LEAD函數支持窗口分區子句以及窗口排序子句。允許我們從窗口分區中，根據給定的相對于當前行的前偏移量(LAG)和后偏移量(LEAD)，返回對應行的值。如果沒有指定，偏移量默認為1.

第二類的偏移函數(FIRST_VALUE、LAST_VALUE和NTH_VALUE)在支持窗口分區子句和排序子句的基礎上，還可以支持窗口框架子句。

FIRST_VALUE和FIRST_VALUE分別返回框架的第一行和最后一行所有查詢的值。NTH_VALUE函數作用是中的相對窗口框架第一行或最后一行的偏移量，使得我們可以取得對應這個偏移量的記錄值。