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
Michael  
72.00
1
2
Robert   
91.00
2
2
Steven   
86.00
3
3
Ann      
94.00
1
3
Ina      
80.00
2
3
Ken      
92.00
3
3
Laura    
75.00
4

为了理解SQL Server中排名函数的工作原理，我们来看一下查询优化器为查询生成的执行计划，如图1所示。

图1 为ROW_NUMBER( )生成的执行计划

由上图可以看出，为了计算排名，优化器首先按分区列排序，然后再对分区内行按ORDER BY子句指定的列排序。如果事先为表创建了符合该排序条件的索引，则会直接扫描该索引文件，不再进行排序。

“序列射影”运算符的工作是负责计算排名，“段”运算符用于确定分组边界。二者相互协调工作，来确定每一行的排名值。

“段”运算符在内存中会保留一行，用来与下一行的PARTITION BY列值进行比较。对于表中的第一行，“段”运算符自然会发送true信号。对于后面的行，直到PARTITION BY列值有变化之前，会一直发送false信号。如果PARTITION BY列值发生了变化，说明已经到了下一个分区，“段”运算符会再次发送true信号。“序列射影”运算符在接收到true信号后，会重置排名值。

如果“序列射影”运算符接收到的是false信号，它会确认当前输入行的排序值是否不同于上一行，如果不同，则按排名函数所指示的递增排名值。自然，在该示例中，由于ROW_NUMBER( )函数需要为每一行递增值。因此，这个排序值比较步骤在该示例中是不存在的。但是，对于像RANK( )和DENSE_RANK( )函数，在执行计划中还会有另外一个“段”运算符，用于比较排序值是否有变化，以确定是否递增排名值。此问题我们在下面还会有介绍。

(2)RANK( )和DENSE_RANK( )函数
ROW_NUMBER( )函数用于编号，它与排名具有不同的概念。例如，由表1可以看出，班级1中的Grace和Andrew的成绩相同，都是99分。如果使用ROW_NUMBER( )函数编号，有两种编号方案可供选择：一种是Grace第1、Andrew第2，另一种是Andrew第1、Grace第2。这虽然都是正确的，它具有不确定性。

而排名则不同了，它具有确定性，相同的排序值总是被分配相同的排名值。Grace和Andrew在排名的情况下都应当是第1，也就是我们常说的并列第1。那他们两人之后的名次是什么呢？是第2还是第3呢？从两人并列第1的角度讲，他们两人之后的名次应当是第2，这也是DENSE_RANK( )函数的排名方式；前面已经有2个人99分了，他们后面的人应当是第3个高分者，从这个角度理解，后面的名次应当是第3，这也是RANK( )的排名方式。DENSE_RANK( )函数的排名方式我们称之为密集排名，因为它的名次之间没有间隔。

下面的语句演示了RANK( )和DENSE_RANK( )的排名方式，查询结果如表3所示。

隐藏行号 复制代码 ？  

1. PrFont34Bin0BinSub0Frac0Def1Margin0Margin0Jc1Indent1440Lim0Lim1SELECT ClassID, StudentName, Achievement, 
   

3.        RANK() OVER(PARTITION BY ClassID ORDER BY Achievement DESC) AS SortRank, 
   

5.        DENSE_RANK() OVER(PARTITION BY ClassID ORDER BY Achievement DESC) AS SortDense 
   

7. FROM Students;
   

 

 

表3                            按班级和考试成绩分别使用RANK( )和DENSE_RANK( )排名

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

下面是为语句生成的执行计划，与ROW_NUMBER( )相比，执行计划中多出了一个“段”运算符。右边段的分组依据是ClassID，左边段的分组依据是ClassID和Achievement，这是多出的“段”。右边的“段”用于分区操作，在到达下一个分区时发送true信号，“序列射影”运算符会重置排名值。而左边的“段”用于比较排序值是否有变化，如果有变化，则通知“序列射影”运算符递增排名值，递增方式则按RANK( )和DENSE_RANK( )函数的规则进行。

图2 为RANK( )和DENSE_RANK( )生成的执行计划

在SQL Server 2005之前，也可以使用子查询的方式实现排名计算。语句的原理就是查询出比当前成绩高的个数，再加上1，就是该成绩的排名。例如，在第1个班级中，比99分高的成绩为0，加上1后，该成绩就是第1名。下面语句的执行结果表3所示相同，但是由于对于每个成绩都要执行两次子查询，在性能方面与RANK( )和DENSE_RANK( )函数相差很远。

SELECT ClassID, StudentName, Achievement,

       (SELECT COUNT(*) FROM Students AS S2

        WHERE S2.ClassID = S1.ClassID AND S2.Achievement > S1.Achievement)+1 AS SortRank,

       (SELECT COUNT(DISTINCT achievement) FROM Students AS S2

        WHERE S2.ClassID = S1.ClassID AND S2.Achievement > S1.Achievement)+1 AS SortDense

FROM Students AS S1

ORDER BY ClassID, Achievement DESC;

(3)NTILE( )函数
NTILE( )函数用于把行分发到指定数目的组中。各个组有编号，编号从1开始。对于每一个行，NTILE将返回此行所属的组的编号。

NTILE( )函数可以接受一个代表组数量的参数，分组的方式“均分”原则。例如，假设一个表有10行，需要分成2组，则每个组都会有5行。如果表有11行，需要分成3个组，这时候是无法均分的。它分配方法是先得到一个能够整除的基组大小（11/3=3），每组应当分配3行，剩余的2行（11-9）会被再次均分到前面的2组中。

例如，下面的语句指定将Students表按学生成绩划分为3个组，并且Students表恰好也是11行，分组结果如表4所示。

SELECT ClassID, StudentName, Achievement,

       NTILE(3) OVER(ORDER BY Achievement DESC) AS Tile

FROM Students;

表4                                                                         分组结果

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

也可以先分区，再分组。例如，下面的语句将每个班级的成绩划分为高、低两组，查询结果如表5所示。可以看出，包含4名学生的班级，每组是2人；包含3名学生的班级，第1组是2人，第2组是1人。

SELECT ClassID, StudentName, Achievement,

       CASE NTILE(2) OVER(PARTITION BY ClassID ORDER BY Achievement DESC)

         WHEN 1 THEN '高'

         WHEN 2 THEN '低'

       END AS Tile

FROM Students;  

表5                                                       按班级分区再按成绩分组结果

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

2.基于窗口的聚合计算
在进行聚合计算时，OVER子句中不再需要ORDER BY子句。因此，语法简化成如下格式：

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

(1)分区聚合计算与联接的比较
通过OVER子句，可以对每个分区内的数据进行聚合计算。仍旧使用表1所示的Students表的数据，现假设我们需要计算每名学生成绩与本班级平均成绩的差异。在之前，我们需要先计算每个班级的平均成绩，然后通过联接的方式将平均成绩关联到相应的学生成绩行，再计算差异。如：

SELECT S1.ClassID,

       S1.StudentName,

       S1.Achievement,

       S2.AvgAch ,       

       S1.Achievement - S2.AvgAch AS Diff

FROM Students AS S1

  LEFT OUTER JOIN (SELECT ClassID, AVG(Achievement) AS AvgAch

                   FROM Students

                   GROUP BY ClassID) AS S2   --计算每个班级的平均成绩

    ON S1.ClassID = S2.ClassID;

查询结果如表6所示。

表6                                           查询每名学生成绩与本班级平均成绩的差异

ClassID
StudentName
Achievement
AvgAch
Diff
1
Grace    
99.00
90.500000
8.500000
1
Andrew   
99.00
90.500000
8.500000
1
Janet    
75.00
90.500000
-15.500000
1
Margaret 
89.00
90.500000
-1.500000
2
Steven   
86.00
83.000000
3.000000
2
Michael  
72.00
83.000000
-11.000000
2
Robert   
91.00
83.000000
8.000000
3
Laura    
75.00
85.250000
-10.250000
3
Ann      
94.00
85.250000
8.750000
3
Ina      
80.00
85.250000
-5.250000
3
Ken      
92.00
85.250000
6.750000

在使用OVER子句的情况下，查询语句会简洁许多，下面语句的查询结果与表6相同。

SELECT ClassID,

       StudentName,

       Achievement,

       AVG(Achievement) OVER(PARTITION BY ClassID) AS AvgAch,

       Achievement - AVG(Achievement) OVER(PARTITION BY ClassID) AS Diff

FROM Students;

虽然语句有所简洁，但是在性能方面该语句不如上面的联接方式。查询优化器为该语句生成的查询计划比较复杂，与联接语句在同一个批中执行时，含有OVER子句的查询开销占了66%。

图3 联接方式与OVER子句的性能比较

(2)对不同类型分区的聚合计算
当在一个语句中需要计算多个不同类型的分区聚合时，OVER子句有着更明显的优势。例如，假设我们既要计算与本班级平均成绩的差异，又要计算与全部学生平均成绩的差异，含有OVER子句的查询变化不大，而使用联结方式则需要增加一个联接。参考下面的语句：

SELECT ClassID,

       StudentName,

       Achievement,

       AVG(Achievement) OVER(PARTITION BY ClassID) AS AvgAch,

       Achievement - AVG(Achievement) OVER(PARTITION BY ClassID) AS Diff,

       AVG(Achievement) OVER() AS AvgAllAch,    -- 所有学生的平均成绩

       Achievement - AVG(Achievement) OVER() AS DiffAll  

FROM Students;  

SELECT S1.ClassID,

       S1.StudentName,

       S1.Achievement,

       S2.AvgAch ,       

       S1.Achievement - S2.AvgAch AS Diff,

       S3.AvgAllAch,

       S1.Achievement - S3.AvgAllAch AS DiffAll

FROM Students AS S1

  LEFT OUTER JOIN (SELECT ClassID, AVG(Achievement) AS AvgAch

                   FROM Students

                   GROUP BY ClassID) AS S2  

    ON S1.ClassID = S2.ClassID

  CROSS JOIN (SELECT AVG(Achievement) AS AvgAllAch

                   FROM Students) AS S3;    --增加了一个联接

对于语句中所包含的OVER子句数量，对于查询的影响不大。例如，下面的第一条语句仅含有1个OVER子句，而第二条语句则含有4个OVER子句，但是优化器为它们生成的执行计划完全相同。

SELECT ClassID,

       StudentName,

       Achievement,

       AVG(Achievement) OVER(PARTITION BY ClassID) AS AvgAch

FROM Students;

SELECT ClassID,

       StudentName,

       Achievement,

       AVG(Achievement) OVER(PARTITION BY ClassID) AS AvgAch,

       SUM(Achievement) OVER(PARTITION BY ClassID) AS SumAch,

       MIN(Achievement) OVER(PARTITION BY ClassID) AS MinAch,

       MAX(Achievement) OVER(PARTITION BY ClassID) AS MaxAch

FROM Students;

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zhanghongju/archive/2009/03/05/3960696.aspx