总结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万，不知道用了分区表性能有没有明显提升。

本文转自JeffWong博客园博客，原文链接：http://www.cnblogs.com/jeffwongishandsome/archive/2010/12/04/1896672.html，如需转载请自行联系原作者

总结SQL Server窗口函数的简单使用

一、从一个熟悉的示例说起

二、窗口函数

二、让人爱不释手的排名函数

热门文章

最新文章

相关课程

相关电子书

相关实验场景