SQL SERVER開窗函數

標籤：

今天將要介紹SQL Sever的開窗函數，何謂開窗函數，不懂吧。反正對於我來說，我是摸不著頭腦了，第一次聽說過。那麼，什麼是開窗函數，其實可以理解為是彙總函式的一個加強版。因為使用彙總函式的話（不包括子查詢的情況），整個查詢都只能是彙總列傳回值，而不能有基礎行的傳回值。那麼對於需要基礎行的傳回值的話，就需要使用複雜的子查詢或者是預存程序等才可以解決。但是使用開窗函數就可以輕鬆解決，它能夠在同一行中同時返回基礎行的列和彙總列。在ISO SQL規定了這樣的函數為開窗函數，在Oracle中則被稱為分析函數，而在DB2中則被稱為OLAP函數。

開窗函數與彙總函式一樣，都是對行的集合組進行彙總計算。它用於為行定義一個視窗（這裡的視窗是指運算將要操作的行的集合），它對一組值進行操作，不需要使用GROUP BY子句對資料進行分組，能夠在同一行中同時返回基礎行的列和彙總列。反正我理解這個函數已經使用好子查詢或者是其它方式求得彙總列的值給我合并。

以書中的例子一步一步來介紹，假設要計算所有人員的總數，我們可以執行下面的SQL語句：

SELECT COUNT(FName) FROM T_Person

這種方式比較直接，只返回一個彙總列的值，沒有任何基礎行的列的值。但是有時需要從不在彙總函式中的行的列中訪問這些彙總計算的值（即基礎行的列）。比如我們想查詢每個工資小於5000元的員工資訊（城市以及年齡），並且在每行中都顯示所有工資小於5000 元的員工個數，嘗試編寫下面的SQL語句：

SELECT FName, FCITY, FAGE, FSalary, COUNT(FName)FROM T_PersonWHERE FSALARY<5000

執行上面的SQL以後我們會得到下面的錯誤資訊：
挑選清單中的列‘T_Person.FCity‘ 無效，因為該列沒有包含在彙總函式或GROUP BY 子句中。
這是因為所有不包含在彙總函式中的列必須聲明在GROUP BY 子句中，使用子查詢的方式是可以解決：

SELECT FName, FCITY, FAGE, FSalary,(SELECT COUNT(FName) FROM T_PersonWHERE FSALARY<5000)FROM T_PersonWHERE FSALARY<5000

雖然使用子查詢能夠解決這個問題，但是子查詢的使用非常麻煩，使用開窗函數則可以大大簡化實現，下面的SQL語句展示了如果使用開窗函數來實現同樣的效果：

SELECT FName, FCITY, FAGE, FSalary, COUNT(FName) OVER()FROM T_PersonWHERE FSALARY<5000

可以看到與彙總函式不同的是，開窗函數在彙總函式後增加了一個OVER關鍵字。
開窗函數的調用格式為：函數名(列) OVER(選項)

我這裡使用的是SQL Server 2008 R2,不知道從什麼時候開始，SQL SERVER也支援開窗函數中使用ORDER BY子句（註：書本中說MSSQLServer中是不支援開窗函數中使用ORDER BY子句）。不管怎麼樣，這裡我整合了網上的相關內容。也正因為開窗函數支援了ORDER BY子句之後，開窗函數被分為兩大類。

第一大類：彙總開窗函數====》彙總函式(列) OVER (選項)，這裡的選項可以是PARTITION BY子句，但不可是ORDER BY子句

第二大類：排序開窗函數====》排序函數(列) OVER(選項)，這裡的選項可以是ORDER BY子句，也可以是　OVER（PARTITION BY子句　ORDER BY子句），但不可以是PARTITION BY子句

 

彙總開窗函數

 

OVER 關鍵字表示把彙總函式當成彙總開窗函數而不是彙總函式。SQL 標準允許將所有彙總函式用做彙總開窗函數。
在上邊的例子中，開窗函數COUNT(*) OVER()對於查詢結果的每一行都返回所有合格行的條數。OVER關鍵字後的括弧中還經常添加選項用以改變進行彙總運算的視窗範圍。如果OVER關鍵字後的括弧中的選項為空白，則開窗函數會對結果集中的所有行進行彙總運算。

 

PARTITION BY 子句

 

開窗函數的OVER關鍵字後括弧中的可以使用PARTITION BY 子句來定義行的分區來供進行彙總計算。與GROUP BY 子句不同，PARTITION BY 子句建立的分區是獨立於結果集的，建立的分區只是供進行彙總計算的，而且不同的開窗函數所建立的分區也不互相影響。下面的SQL語句用於顯示每一個人員的資訊以及所屬城市的人員數：

SELECT FName, FCITY, FAGE, FSalary,COUNT(FName) OVER(PARTITION BY FCITY)FROM T_Person

OVER(PARTITION BY FCITY)表示對結果集按照FCITY進行分區，並且計算當前行所屬的組的彙總計算結果。在同一個SELECT語句中可以同時使用多個開窗函數，而且這些開窗函數並不會相互幹擾。比如下面的SQL語句用於顯示每一個人員的資訊、所屬城市的人員數以及同齡人的人數：

SELECT FName,FCITY, FAGE, FSalary,COUNT(FName) OVER(PARTITION BY FCITY),COUNT(FName) OVER(PARTITION BY FAGE)FROM T_Person

 

排序開窗函數

 

對於排序開窗函數來講，它支援的開窗函數分別為：ROW_NUMBER（行號）、RANK（排名）、DENSE_RANK（密集排名）和NTILE（分組排名）。

先看一段SQL語句：

select  FName, FSalary, FCity, FAge,  row_number() over(order by FSalary) as rownum,  rank() over(order by FSalary) as rank,  dense_rank() over(order by FSalary) as dense_rank,  ntile(6) over(order by FSalary)as ntile from  T_Person order by  FName  

 執行的結果如下（對於想自己嘗試的朋友，那你得辛苦點，下載電子書或者是購買書來學習吧。因為我可是限於篇幅，省略去大部分內容哦）：

 

看到上面的結果了吧，下面來介紹下相關的內容。我們得到的最終結果是按照FName進行升序顯示的。

對於row_number() over(order by FSalary) as rownum來說，這個排序開窗函數是按FSalary升序的方式來排序，並得出排序結果的序號

對於rank() over(order by FSalary) as rank來說，這個排序形容函數是按FSalary升序的方式來排序，並得出排序結果的排名號。這個函數求出來的排名結果可以排列，並列排名之後的排名將是並列的排名加上並列數（簡單說每個人只有一種排名，然後出現兩個並列第一名的情況，這時候排在兩個第一名後面的人將是第三名，也就是沒有了第二名，但是有兩個第一名）

對於dense_rank() over(order by FSalary) as dense_rank來說，這個排序函數是按FSalary升序的方式來排序，並得出排序結果的排名號。這個函數與rand()函數不同在於，並列排名之後的排名只是並列排名加１（簡單說每個人只有一種排名，然後出現兩個並列第一名的情況，這時候排在兩個第一名後面的人將是第二名，也就是兩個第一名，一個第二名）

對於ntile(6) over(order by FSalary)as ntile 來說，這個排序函數是按FSalary升序的方式來排序，並得出排序結果的分組數。

排序函數和彙總開窗函數類似，也支援在OVER子句中使用PARTITION BY語句。例如：

select  FName, FSalary, FCity, FAge,  row_number() over(partition by FName  order by FSalary) as rownum,  rank() over(partition by FName order by FSalary) as rank,  dense_rank() over(partition by FName order by FSalary) as dense_rank,  ntile(6) over(partition by FName order by FSalary)as ntile from  T_Person order by  FName

 關於PARTITION BY子句，請看上面的介紹，這裡就不再累贅了。但是需要注意一點的是，在排序開窗函數中使用PARTITION BY子句需要放置在ORDER　BY子句之前。

SQL SERVER開窗函數