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

作者: Jeff Wong 來源: 博客園發布時間: 2010-12-26 22:00 閱讀: 5489 次推薦: 5 原文鏈接 [收藏]

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

　　一、從一個熟悉的示例說起

　　我們熟知的數據庫分頁查詢，以這一篇介紹過的為例吧。分頁查詢Person表中的人，可以這么寫SQL語句：

WITH Record AS (
SELECT
Row_Number() OVER (ORDER BY Id DESC) AS RecordNumber,
Id,
FirstName,
LastName,
Height,
Weight
FROM
Person (NOLOCK)
)
SELECT
RecordNumber,
(SELECT COUNT(0) FROM Record) AS TotalCount,
Id,
FirstName,
LastName,
Height,
Weight
FROM Record
WHERE 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 '語文平均分'
FROM
StudentScore
WHERE CourseId=2

　　結果如下：

　　4、PARTITION BY

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

SELECT
Id,
CreateDate,
StudentId,
ClassId,
CourseId,
Score,
CAST(AVG(Score) OVER(PARTITION BY ClassId ) AS decimal(5,2) )AS '語文平均分'
FROM
StudentScore
WHERE 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 的順序。

　　下面的查詢按照數學成績逆序排列：

SELECT
Id,
-- CreateDate,
ROW_NUMBER() OVER(ORDER BY Score DESC) AS '序號',
StudentId,
ClassId,
CourseId,
Score
FROM
StudentScore
WHERE CourseId=8

　　結果如下：

　　據我所知，此函數在SQL Server分頁查詢中幾乎已經普及應用。Good job。

　　2、RANK()和DENSE_RANK()

　　（1）、RANK()函數

　　返回結果集的分區內每行的排名。行的排名是相關行之前的排名數加一。如果兩個或多個行與一個排名關聯，則每個關聯行將得到相同的排名。

SELECT
Id,
-- CreateDate,
RANK() OVER(ORDER BY Score DESC) AS '序號',
StudentId,
ClassId,
CourseId,
Score
FROM
StudentScore
WHERE CourseId=8

　　結果如下：

　　注意，它和ROW_NUMBER()的異同點，您應該已經知道了：

　　a、RANK函數和ROW_NUMBER函數類似，它們都是用來對結果進行排序。
　　b、不同的是，ROW_NUMBER函數為每一個值生成唯一的序號，而RANK函數為相同的值生成相同的序號。
　　上圖中，兩個86分的學生對應的序號都是3,而接著排在它們下面的序號直接變成了5。

　　（2）、DENSE_RANK()函數

　　返回結果集分區中行的排名，在排名中沒有任何間斷。行的排名等于所討論行之前的所有排名數加一。如果有兩個或多個行受同一個分區中排名的約束，則每個約束行將接收相同的排名。

SELECT
Id,
-- CreateDate,
DENSE_RANK() OVER(ORDER BY Score DESC) AS '序號',
StudentId,
ClassId,
CourseId,
Score
FROM
StudentScore
WHERE CourseId=8

　　查詢結果如下：

　　上圖中，兩個86分的學生對應的序號都是3,而接著排在它們下面的序號是4（也就是說DENSE_RANK()函數查詢的序號是類似ROW_NUMBER()那樣連續的，但是對于相同值的行生成相同的序號，從這一點上來說，對于相同查詢條件和排序的查詢，ROW_NUMBER()函數查詢的結果集是DENSE_RANK()函數查詢的結果的子集）。這也是我們可以總結出的RANK和DENSE_RANK()這兩個函數的最大的不同點。

　　3、NTILE()

　　NTILE函數把結果中的行關聯到組，并為每一行分配一個所屬的組的編號，編號從一開始。對于每一個行，NTILE 將返回此行所屬的組的編號。
　　如果分區的行數不能被 integer_expression 整除，則將導致一個成員有兩種大小不同的組。按照 OVER 子句指定的順序，較大的組排在較小的組前面。

SELECT
Id,
-- CreateDate,
NTILE(6) OVER(ORDER BY ClassId DESC) AS '組編號',
StudentId,
ClassId,
CourseId,
Score
FROM
StudentScore
WHERE CourseId=8

　　查詢的結果如下：

　　本文的介紹和示例都很基礎，但是通過窗口函數，確實可以幫我們優化很多復雜查詢。上面的SQL語句看上去每一個都很簡單，但是現在的簡單都隱藏著背后的復雜。需要提醒的是，分組概念雖然基礎卻很重要，你必須掌握；而熟練應用了窗口函數，你的SQL查詢就如虎添翼更上層樓了。

　　最后，我一直擔心對于海量數據，SQL Server的性能問題。因為近期的開發碰巧遇到海量數據的查詢，最多的過億，數據量最少的一個表，也過5000萬，不知道用了分區表性能有沒有明顯提升。
　　參考文章：
　　http://msdn.microsoft.com/zh-cn/library/ms189461.aspx
　　http://www.cnblogs.com/aierong/archive/2008/08/26/1273890.html
　　http://www.cnblogs.com/aierong/archive/2008/08/18/1269407.html