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

前言:我一直十分喜欢使用SQL Server2005/2008的窗口函数,排名函数ROW_NUMBER()尤甚。今天晚上我在查看SQL Server开发的相关文档,整理收藏夹发现了两篇收藏已久的好文,后知后觉,读后又有点收获,顺便再总结一下。
一、从一个熟悉的示例说起
我们熟知的数据库分页查询,以 这一篇介绍过的为例吧。分页查询Person表中的人,可以这么写SQL语句:
 
01WITH Record AS (
02        SELECT
03        Row_Number() OVER (ORDER BY Id DESC) AS RecordNumber,
04        Id,
05        FirstName,
06        LastName,
07        Height,
08        Weight
09    FROM
10        Person (NOLOCK) 
11    )
12    SELECT 
13    RecordNumber,
14    (SELECT COUNT(0) FROM Record) AS TotalCount,
15    Id,
16        FirstName,
17        LastName,
18        Height,
19        Weight
20    FROM Record
21    WHERE RecordNumber BETWEEN 1 AND 10
其中, ROW_NUMBER()是排名函数,而紧随其后的 OVER()函数就是窗口函数。
你还在用二次top方式的分页查询吗?可以考虑尝试使用排名函数配合CTE实现分页。
 
二、窗口函数
本文介绍窗口函数,以下面的学生成绩表为例:
 
1CREATE TABLE [StudentScore](
2    [Id] [int] IDENTITY(1,1) NOT NULL,
3    [StudentId] [int] NOT NULL CONSTRAINT [DF_StudentScore_StudentId]  DEFAULT ((0)),
4    [ClassId] [int] NOT NULL CONSTRAINT [DF_StudentScore_ClassId]  DEFAULT ((0)),
5    [CourseId] [int] NOT NULL CONSTRAINT [DF_StudentScore_CourseId]  DEFAULT ((0)),
6    [Score] [float] NOT NULL CONSTRAINT [DF_StudentScore_Score]  DEFAULT ((0)),
7    [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_StudentScore_CreateDate]  DEFAULT (getdate())
8) ON [PRIMARY]
其中,Id是自增Id,CreateDate是录入时间, StudentId 学生,ClassId 班级,CourseId  课程 ,Score  分数
录入一些测试数据如下:
 
01--CourseId 2:语文 4:数学 8:英语
02  
03--1班学生成绩
04INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,1,2,85)
05INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,1,2,95.5)
06INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,1,2,90)
07  
08INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,1,4,90)
09INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,1,4,98)
10INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,1,4,89)
11  
12INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,1,8,80)
13INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,1,8,75.5)
14INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,1,8,77)
15  
16  
17--2班学生成绩
18INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,2,2,90)
19INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,2,2,77)
20INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,2,2,78)
21INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (4,2,2,83)
22  
23INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,2,4,98)
24INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,2,4,95)
25INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,2,4,78)
26INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (4,2,4,100)
27  
28INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,2,8,85)
29INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,2,8,90)
30INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,2,8,86)
31INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (4,2,8,78.5)
32  
33--3班学生成绩
34INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,3,2,82)
35INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,3,2,78)
36INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,3,2,91)
37  
38INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,3,4,83)
39INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,3,4,78)
40INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,3,4,99)
41  
42INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,3,8,86)
43INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,3,8,78)
44INSERT 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] ] )
窗口函数使用OVER函数实现,OVER函数分带参和不带参两种。其中可选参数PARTITION BY用于将数据按照特定字段分组。
3、简单示例
查询学生成绩表的基本列以及所有班级所有学生的语文平均分:
 
01SELECT
02    --Id,
03    --CreateDate,
04    StudentId,
05    ClassId,
06    CourseId,
07    Score,
08   CAST(AVG(Score) OVER() AS decimal(5,2) )AS  '语文平均分'
09FROM
10    StudentScore
11    WHERE CourseId=2
结果如下:
example1
4、PARTITION BY
如果我们需要查询每一个班级的语文平均分,可以根据PARTION BY来进行分组:
 
01SELECT
02    Id,
03    CreateDate,
04    StudentId,
05    ClassId,
06    CourseId,
07    Score,
08   CAST(AVG(Score) OVER(PARTITION BY ClassId ) AS decimal(5,2) )AS  '语文平均分'
09FROM
10    StudentScore
11    WHERE CourseId=2
查询结果如下:
example2
图可能不清楚,三个班级的语文平均分是不同的。
到这里,其实你可能已经体会到使用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 的顺序。
下面的查询按照数学成绩逆序排列:
 
01SELECT
02    Id,
03--    CreateDate,
04    ROW_NUMBER() OVER(ORDER BY Score DESC) AS '序号',
05    StudentId,
06    ClassId,
07    CourseId,
08    Score
09FROM
10    StudentScore
11    WHERE CourseId=8
结果如下:
example3
据我所知,此函数在SQL Server分页查询中几乎已经普及应用。Good job。
 
2、RANK()和DENSE_RANK()
(1)、 RANK()函数
返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。如果两个或多个行与一个排名关联,则每个关联行将得到相同的排名。
 
01SELECT
02    Id,
03--    CreateDate,
04    RANK() OVER(ORDER BY Score DESC) AS '序号',
05    StudentId,
06    ClassId,
07    CourseId,
08    Score
09FROM
10    StudentScore
11    WHERE CourseId=8
结果如下:
example5
注意,它和ROW_NUMBER()的异同点,您应该已经知道了:
a、RANK函数和ROW_NUMBER函数类似,它们都是用来对结果进行排序。
b、不同的是,ROW_NUMBER函数为每一个值生成唯一的序号,而RANK函数为相同的值生成相同的序号。
上图中,两个86分的学生对应的序号都是 3,而接着排在它们下面的序号直接变成了 5
(2)、 DENSE_RANK()函数
返回结果集分区中行的排名,在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。如果有两个或多个行受同一个分区中排名的约束,则每个约束行将接收相同的排名。
 
01SELECT
02    Id,
03--    CreateDate,
04    DENSE_RANK() OVER(ORDER BY Score DESC) AS '序号',
05    StudentId,
06    ClassId,
07    CourseId,
08    Score
09FROM
10    StudentScore
11    WHERE CourseId=8
查询结果如下:
example6
上图中,两个86分的学生对应的序号都是 3,而接着排在它们下面的序号是 4(也就是说DENSE_RANK()函数查询的序号是类似ROW_NUMBER()那样连续的,但是对于相同值的行生成相同的序号,从这一点上来说,对于相同查询条件和排序的查询,ROW_NUMBER()函数查询的结果集是DENSE_RANK()函数查询的结果的子集)。这也是我们可以总结出的RANK和DENSE_RANK()这两个函数的最大的不同点。
3、NTILE()
NTILE函数把结果中的行关联到组,并为每一行分配一个所属的组的编号,编号从一开始。对于每一个行,NTILE 将返回此行所属的组的编号。
如果分区的行数不能被 integer_expression 整除,则将导致一个成员有两种大小不同的组。按照 OVER 子句指定的顺序,较大的组排在较小的组前面。
 
01SELECT
02    Id,
03--    CreateDate,
04    NTILE(6) OVER(ORDER BY ClassId DESC) AS '组编号',
05    StudentId,
06    ClassId,
07    CourseId,
08    Score
09FROM
10    StudentScore
11    WHERE CourseId=8
查询的结果如下:
example4
 
本文的介绍和示例都很基础,但是通过窗口函数,确实可以帮我们优化很多复杂查询。上面的SQL语句看上去每一个都很简单,但是现在的简单都隐藏着背后的复杂。需要提醒的是,分组概念虽然基础却很重要,你必须掌握;而熟练应用了窗口函数,你的SQL查询就如虎添翼更上层楼了。
最后,我一直担心对于海量数据,SQL Server的性能问题。因为近期的开发碰巧遇到海量数据的查询,最多的过亿,数据量最少的一个表,也过5000万,不知道用了分区表性能有没有明显提升。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-681763/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/16436858/viewspace-681763/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值