總結SQL Server視窗函數的簡單使用

前言：我一直十分喜歡使用SQL Server2005/2008的視窗函數，次序函數ROW_NUMBER()尤甚。今天晚上我在查看SQL Server開發的相關文檔，整理收藏夾發現了兩篇收藏已久的好文，後知後覺，讀後又有點收穫，順便再總結一下。

一、從一個熟悉的樣本說起我們熟知的資料庫分頁查詢，以這一篇介紹過的為例吧。分頁查詢Person表中的人，可以這麼寫SQL語句：

WITH Record AS (SELECTRow_Number() OVER (ORDER BY Id DESC) AS RecordNumber,Id,FirstName,LastName,Height,WeightFROMPerson (NOLOCK) )SELECT RecordNumber,(SELECT COUNT(0) FROM Record) AS TotalCount,Id,FirstName,LastName,Height,WeightFROM RecordWHERE RecordNumber BETWEEN 1 AND 10

其中，ROW_NUMBER()是次序函數，而緊隨其後的 OVER()函數就是視窗函數。你還在用二次top方式的分頁查詢嗎？可以考慮嘗試使用次序函數配合CTE實現分頁。 二、視窗函數本文介紹視窗函數，以下面的學產生績表為例：

CREATE TABLE [StudentScore]([Id] [int] IDENTITY(1,1) NOT NULL,[StudentId] [int] NOT NULL CONSTRAINT [DF_StudentScore_StudentId]  DEFAULT ((0)),[ClassId] [int] NOT NULL CONSTRAINT [DF_StudentScore_ClassId]  DEFAULT ((0)),[CourseId] [int] NOT NULL CONSTRAINT [DF_StudentScore_CourseId]  DEFAULT ((0)),[Score] [float] NOT NULL CONSTRAINT [DF_StudentScore_Score]  DEFAULT ((0)),[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_StudentScore_CreateDate]  DEFAULT (getdate())) ON [PRIMARY]

其中，Id是自增Id，CreateDate是錄入時間，StudentId 學生，ClassId 班級，CourseId  課程 ,Score  分數。錄入一些測試資料如下：

--CourseId 2:語文 4：數學 8：英語--1班學產生績INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,1,2,85)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,1,2,95.5)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,1,2,90)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,1,4,90)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,1,4,98)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,1,4,89)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,1,8,80)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,1,8,75.5)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,1,8,77)--2班學產生績INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,2,2,90)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,2,2,77)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,2,2,78)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (4,2,2,83)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,2,4,98)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,2,4,95)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,2,4,78)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (4,2,4,100)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,2,8,85)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,2,8,90)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,2,8,86)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (4,2,8,78.5)--3班學產生績INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,3,2,82)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,3,2,78)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,3,2,91)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,3,4,83)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,3,4,78)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,3,4,99)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,3,8,86)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,3,8,78)INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,3,8,97)

視窗函數是SQL Server2005新增的函數。下面就談談它的基本概念： 1、視窗函數的作用視窗函數是對一組值進行操作，不需要使用GROUP BY 子句對資料進行分組，還能夠在同一行中同時返回基礎行的列和彙總列。舉例來說，我們要得到一個年級所有班級所有學生的平均分，按照傳統的寫法，我們肯定是通過AVG彙總函式來實現求平均分。這樣帶來的”壞處“是我們不能輕鬆地返回基礎行的列（班級，學生等列），而只能得到彙總列。因為彙總函式的要點就是對一組值進行彙總，以GROUP BY 查詢作為操作的上下文，由於GROUP BY 操作對資料進行分組後，查詢為每個組只返回一行資料，因此，要限制所有運算式為每個組只返回一個值。而通過視窗函數，基礎列和彙總列的查詢都輕而易舉。 2、基本文法OVER([PARTITION BY value_expression,..[n] ] <ORDER BY BY_Clause>)
視窗函數使用OVER函數實現，OVER函數分帶參和不帶參兩種。其中選擇性參數PARTITION BY用於將資料按照特定欄位分組。 3、簡單樣本查詢學產生績表的基本欄以及所有班級所有學生的語文平均分：

SELECT--Id,--CreateDate,StudentId,ClassId,CourseId,Score,   CAST(AVG(Score) OVER() AS decimal(5,2) )AS  '語文平均分'FROMStudentScoreWHERE CourseId=2

結果如下： 4、PARTITION BY如果我們需要查詢每一個班級的語文平均分，可以根據PARTION BY來進行分組：

SELECTId,CreateDate,StudentId,ClassId,CourseId,Score,   CAST(AVG(Score) OVER(PARTITION BY ClassId ) AS decimal(5,2) )AS  '語文平均分'FROMStudentScoreWHERE CourseId=2

查詢結果如下：圖可能不清楚，三個班級的語文平均分是不同的。到這裡，其實你可能已經體會到使用OVER函數的好處了：a、OVER子句的優點就是能夠在返回基本欄的同時，在同一行對它們進行彙總
b、可以在運算式中混合使用基本欄和彙總列如果我們使用傳統的GROUP BY分組查詢，直接擷取基本欄和彙總列就不是這麼簡單一句SQL了。如你所知，我們知道的很多彙總函式，如SUM,AVG,MAX,MIN等彙總函式都支援視窗函數的運算。 二、讓人愛不釋手的次序函數SQL Server提供了4個次序函數:ROW_NUMBER(), RANK(),DENSE_RANK()和NTILE()。下面通過樣本重點談談這四個函數的使用。 1、ROW_NUMBER()返回結果集分區內行的序號，每個分區的第一行從 1 開始。ORDER BY 子句可確定在特定分區中為行分配唯一 ROW_NUMBER 的順序。下面的查詢按照數學成績逆序排列：

SELECTId,--    CreateDate,ROW_NUMBER() OVER(ORDER BY Score DESC) AS '序號', StudentId,ClassId,CourseId,ScoreFROMStudentScoreWHERE CourseId=8

結果如下：據我所知，此函數在SQL Server分頁查詢中幾乎已經普及應用。Good job。 2、RANK()和DENSE_RANK()（1）、 RANK()函數返回結果集的分區內每行的排名。行的排名是相關行之前的排名數加一。如果兩個或多個行與一個排名關聯，則每個關聯線將得到相同的排名。

SELECTId,--    CreateDate,RANK() OVER(ORDER BY Score DESC) AS '序號', StudentId,ClassId,CourseId,ScoreFROMStudentScoreWHERE CourseId=8

結果如下：注意，它和ROW_NUMBER()的異同點，您應該已經知道了：a、RANK函數和ROW_NUMBER函數類似，它們都是用來對結果進行排序。
b、不同的是，ROW_NUMBER函數為每一個值產生唯一的序號，而RANK函數為相同的值產生相同的序號。
中，兩個86分的學生對應的序號都是3,而接著排在它們下面的序號直接變成了5。（2）、 DENSE_RANK()函數返回結果集分區中行的排名，在排名中沒有任何間斷。行的排名等於所討論行之前的所有排名數加一。如果有兩個或多個行受同一個分區中排名的約束，則每個約束行將接收相同的排名。

SELECTId,--    CreateDate,DENSE_RANK() OVER(ORDER BY Score DESC) AS '序號', StudentId,ClassId,CourseId,ScoreFROMStudentScoreWHERE CourseId=8

查詢結果如下：中，兩個86分的學生對應的序號都是3,而接著排在它們下面的序號是4（也就是說DENSE_RANK()函數查詢的序號是類似ROW_NUMBER()那樣連續的，但是對於相同值的行產生相同的序號，從這一點上來說，對於相同查詢條件和排序的查詢，ROW_NUMBER()函數查詢的結果集是DENSE_RANK()函數查詢的結果的子集）。這也是我們可以總結出的RANK和DENSE_RANK()這兩個函數的最大的不同點。 3、NTILE()NTILE函數把結果中的行關聯到組，並為每一行分配一個所屬的組的編號，編號從一開始。對於每一個行，NTILE 將返回此行所屬的組的編號。
如果分區的行數不能被 integer_expression 整除，則將導致一個成員有兩種大小不同的組。按照 OVER 子句指定的順序，較大的組排在較小的組前面。

SELECTId,--    CreateDate,NTILE(6) OVER(ORDER BY ClassId DESC) AS '組編號', StudentId,ClassId,CourseId,ScoreFROMStudentScoreWHERE CourseId=8

查詢的結果如下： 本文的介紹和樣本都很基礎，但是通過視窗函數，確實可以幫我們最佳化很多複雜查詢。上面的SQL語句看上去每一個都很簡單，但是現在的簡單都隱藏著背後的複雜。需要提醒的是，分組概念雖然基礎卻很重要，你必須掌握；而熟練應用了視窗函數，你的SQL查詢就如虎添翼更上層樓了。最後，我一直擔心對于海量資料，SQL Server的效能問題。因為近期的開發碰巧遇到海量資料的查詢，最多的過億，資料量最少的一個表，也過5000萬，不知道用了分區表效能有沒有明顯提升。參考文章：http://msdn.microsoft.com/zh-cn/library/ms189461.aspxhttp://www.cnblogs.com/aierong/archive/2008/08/26/1273890.htmlhttp://www.cnblogs.com/aierong/archive/2008/08/18/1269407.html