簡介
SQL Server 2012之後對窗口函數進行了極大的加強,但對於很多開發人員來說,對窗口函數卻不甚了解,導致了這 樣強大的功能被浪費,因此本篇文章主要談一談SQL Server中窗口函數的概念。
什麼是窗口函數
窗口函數,也可以被稱為OLAP函數或分析函數。理解窗口函數可以從理解聚合函數開始,我們知道聚合函數的概念, 就是將某列多行中的值按照聚合規則合並為一行,比如說Sum、AVG等等,簡單的概念如圖1所示。
圖1.聚合函數
因此,通常來說,聚合後的行數都要小於聚合前的行數。而對於窗口函數來說,輸入結果等於輸出結果,舉一個簡單 的例子,如果你計算產品類型A和產品類型B,A產品分5小類,B產品分2小類,應用了窗口函數的結果後可以還是7行,對 窗口函數應用了Count後,附加在每一行上,比如說“A產品,A小類1,5“,而B小類則變為”B產品,B 小類1,2”最後一列就是應用了窗口函數的結果。
現在我們對窗口函數有了初步的概覽,文章後我會提供一些具體的例子來讓對窗口函數的概念更加深刻,窗口函數除 了上面提到的輸入行等於輸出行之外,還有如下特性和好處:
類似Group By的聚合
非順序的訪問數據
可以對於窗口函數使用分析函數、聚合函數和排名函數
簡化了SQL代碼(消除Join)
消除中間表
窗口函數是整個SQL語句最後被執行的部分,這意味著窗口函數是在SQL查詢的結果集上進行的,因此不會受到Group By, Having,Where子句的影響。
窗口函數的典型范例是我們在SQL Server 2005之後用到的排序函數,比如代碼清單1所示。
Row_Number() OVER (partition by xx ORDERBY xxx desc) RowNumber
代碼清單1.可用於分頁的排序函數
因此,我們可以把窗口函數的語法抽象出來,如代碼清單2所示。
函數() Over (PARTITION By 列1,列2,OrderBy 列3,窗口子句) AS 列別名
代碼清單2.窗口函數的語法
一個簡單的例子
下面我們來看一個簡單的例子,假如說我們希望將AdventureWorks示例數據庫中的Employee表按照性別進行聚合,比 如說我希望得到的結果是:“登錄名,性別,該性別所有員工的總數”,如果我們使用傳統的寫法,那一定 會涉及到子查詢,如代碼清單3所示。
SELECT [LoginID],gender, (SELECT COUNT(*) FROM [AdventureWorks2012].[HumanResources].[Employee] a WHERE a.Gender=b.Gender) AS GenderTotal FROM [AdventureWorks2012].[HumanResources].[Employee] b