SQL Server 2008的窗口计算

作者：张洪举，MVP

应用于：SQL Server 2005，SQL Server 2008

日期：2009/3/5

在SQL Server中，窗口被定义为用户指定的一组行。例如，在下面表1所示的Students表中，包含有3个班级的学生成绩，则其中的每一个班级都可以被看作是一个数据窗口，或是分区。

 

ClassID	StudentName	Achievement
1	Grace	99.00
1	Andrew	99.00
1	Janet	75.00
1	Margaret	89.00
2	Steven	86.00
2	Michael	72.00
2	Robert	91.00
3	Laura	75.00
3	Ann	94.00
3	Ina	80.00
3	Ken	92.00

表1 Students表中的窗口

之所以要提出窗口这个概念，因为这种基于窗口或分区的重新计算在实际工作应用范围比较广泛。例如，假设我们要对每个班级中的学生按成绩进行排序，在对第1个班级排序完成后，对第2个班级进行排序时编号需要重新从1开始。在SQL Server 2000中，像这种排序方式实现起来是比较烦琐的。可以说，对新窗口重新启动计算是窗口计算的重要特点。

为支持窗口计算，SQL Server提供了OVER子句和窗口函数。窗口函数在MSDN Library中被翻译为开窗函数。虽然“开窗函数”理解起来并不如“窗口函数”容易，但是它描述了数据窗口变化后重新启动计算这样一个动作，所以我们尊重MSDN Library中的翻译，在后续的介绍中将使用“开窗函数”这一名词。

窗口计算的两个主要应用就是对每组内的数据进行排序和聚合计算。因此，开窗函数也被分为排名开窗函数和聚合开窗函数。排名开窗函数如ROW_NUMBER( )、RANK( )，聚合开窗函数如AVG( )、SUM等。

 

 1.基于窗口的排名计算

进行排名计算时，OVER子句的语法格式如下：

OVER ( [ PARTITION BY value_expression , ... [ n ] ]

       <ORDER BY_Clause> )

PARTITION BY value_expression

指定对相应FROM子句生成的行集进行分区所依据的列。开窗函数分别应用于每个分区，并为每个分区重新启动计算。value_expression只能引用通过FROM子句可用的列，不能引用选择列表中的表达式或别名。value_expression可以是列表达式、标量子查询、标量函数或用户定义的变量。

<ORDER BY 子句>

指定应用排名开窗函数的排序顺序。只能引用通过FROM子句可用的列，但是不同通过指定整数来表示选择列表中列名称或列别名的位置。

下面我们将以表1所示的Students表为例，进行介绍。像Students表这样的数据结构设计，相对于数据库存储而言是比较合理的，因为我们不可能为每个班级创建一个表，但确实又存在像为每个班级中的学生成绩进行排序或为学生编号这样的实际需求，SQL Server的窗口计算技术就有效解决了二者之间的矛盾。

从SQL Server 2005开始，提供了4个排名函数，分别是：ROW_NUMBER( )、RANK( )、DENSE_RANK( )和NTILE( )，它们可以为分区中的每一行返回一个排名值。ROW_NUMBER( )用于按行进行编号，RANK( )和DENSE_RANK( )用于按指定顺序排名，NTILE( )用于对数据进行分区。

(1)ROW_NUMBER( )

ROW_NUMBER( )返回分区内行的序列号，每个分区的第一行从1开始。例如，下面的语句指定按ClassID进行分区，并按StudentName进行排序编号。查询结果如表2所示。

SELECT ClassID, StudentName, Achievement,

       ROW_NUMBER() OVER(PARTITION BY ClassID ORDER BY StudentName) AS RowNumber

FROM Students;

表2                                                   按班级分区、按学生姓名进行编号

ClassID	StudentName	Achievement	RowNumber
1	Andrew	99.00	1
1	Grace	99.00	2
1	Janet	75.00	3
1	Margaret	89.00	4
2