SQL Server中的窗口函數

簡介

    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 ORDER BY xxx desc) RowNumber

代碼清單1.可用于分頁的排序函數

    因此，我們可以把窗口函數的語法抽象出來，如代碼清單2所示。

函數() Over (PARTITION By 列1，列2，Order By 列3，窗口子句) AS 列別名

一個簡單的例子

    下面我們來看一個簡單的例子，假如說我們希望將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

代碼清單3.傳統的寫法

    如果我們使用了窗口函數，代碼瞬間就變得簡潔，不再需要子查詢或Join，如圖2所示。

圖2.使用窗口函數

    除此之外，窗口函數相比傳統寫法而言，還會有更好的性能，我們可以通過比較執行計劃得出如圖3所示。

圖3.通過比較執行計劃，看出窗口函數擁有更好的性能

    假如我們考慮更復雜的例子，在Over子句加上了Order By，來完成一個平均數累加，如果不使用窗口函數，那一定是游標，循環等麻煩的方式，如果使用了窗口函數，則一切就變得非常輕松，如圖4所示。

圖4.窗口函數

Partition By

    代碼清單2展示了窗口函數的語法，其中Over子句之后第一個提到的就是Partition By。Partition By子句也可以稱為查詢分區子句，非常類似于Group By，都是將數據按照邊界值分組，而Over之前的函數在每一個分組之內進行，如果超出了分組，則函數會重新計算，比如圖2中的例子，我們將數據分為男性和女性兩部分，前面的Count()函數針對這兩組分別計算值（男性206，女性84）。

   針對Partition By可以應用的函數不僅僅是我們所熟知的聚合函數，以及一些其他的函數，比如說Row_Number()。

Order By

    Order By子句是另一類子句，會讓輸入的數據強制排序（文章前面提到過，窗口函數是SQL語句最后執行的函數，因此可以把SQL結果集想象成輸入數據）。Order By子句對于諸如Row_Number()，Lead()，LAG()等函數是必須的，因為如果數據無序，這些函數的結果就沒有任何意義。因此如果有了Order By子句，則Count()，Min()等計算出來的結果就沒有任何意義。

    下面我們看一個很有代表性的ROW_NUMBER()函數，該函數通常被用于分頁，該函數從1開始不斷遞增，可以和Partition By一起使用，當穿越分區邊界時，Row_Number重置為1，一個簡單的例子如圖5所示，我們根據請假小時數對員工進行排序。

圖5.Row_Number函數示例

    另一個比較有趣的分析函數是LEAD()和LAG()，這兩個分析函數經過Order By子句排序后，可以在當前行訪問上N行（LAG）或下N行（LEAD）的數據，下面是一個例子，如圖6所示。

圖6.訪問上一行的LAG函數

    另一個分析函數是RANK函數，與Row_Number不同的是，Rank函數中如果出現了相同的值，不會像Row_Number那樣疊加計數，而是同樣的值計數一樣，比如說 1 1 3 4 5 5 7，而不是Row_Number的1 2 3 4 5 6 7。這里就不細說了。另外如果希望并列排名的不影響下一個排名，則考慮使用Dense_Rank函數。有關其他的諸如First_value和Last_Value之類的函數可以參看：http://technet.microsoft.com/zh-cn/library/hh213234.aspx。

窗口子句

    前面窗口的函數的作用范圍是整個表，或是整個Partition by后面的分區。但是使用了窗口子句我們可以控制輸入到窗口函數的數據集（前面說過，窗口函數是整個語句中最后執行的）的范圍。下面我們從一個例子開始看，假如我希望找出公司每一個層級休病假最長的人，我們可以執行圖7中的語句。

圖7.找出每個層級休假最多的人

    但是如果我們希望把輸入數據集的粒度由Partition變為更細的話，我們可以使用窗口子句，讓窗口函數僅僅根據當前行的前N行和后N行計算結果，那我們可以使用窗口子句，如圖8所示，圖8中，我們排序后，僅僅根據當前行的前一行和后一行以及當前行來計算這3個人當中請病假最長時間的人。

圖8.在三行之內找到休假時間最長的人

    我們也可以使用Range來指定Partition內的范圍，比如說我們希望從當前行和之前行中找到第一行，則使用如圖9所示的用法。

圖9.

小結

    本文從窗口函數組成的三部分簡單介紹了窗口函數的概念，并給出了一些例子。更多可以在窗口上使用的函數，可以參照MSDN（http://technet.microsoft.com/zh-cn/library/ms189461.aspx）。在使用這些函數的時候，還要注意版本要求，很多函數是只有在SQL Server 2012中才被支持的。