作者：张洪举，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所示。
SELECT ClassID, StudentName, Achievement,
RANK() OVER(PARTITION BY ClassID ORDER BY Achievement DESC) AS SortRank,
DENSE_RANK() OVER(PARTITION BY ClassID ORDER BY Achievement DESC) AS SortDense
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
|
padding-right: 5.4pt; border-top: #f0f0f0; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #f0f0f0; width: 87.2pt; padding-top: 0cm;
分享到：
Global site tag (gtag.js) - Google Analytics
|
相关推荐
利用Microsoft SQL Server 2008实现灵活的商业智能解决方案使用Microsoft 完善的BI工具构建B0解决方案的必备指南，使用SQLServer 2008设计、开发和部署更有效的数据集成、报表、分析解决方案所需的权威操作指南。...
第1章 RDBMS基础：SQLServer数据库的构成 1.1 数据库对象概述 1.1.1 数据库对象 1.1.2 事务日志 1.1.3 最基本的数据库对象：表 1.1.4 文件组 1.1.5 数据库关系图 1.1.6 视图 1.1.7 存储过程 1.1.8 用户自定义函数 ...
利用Microsoft SQL Server 2008实现灵活的商业智能解决方案使用Microsoft 完善的BI工具构建B0解决方案的必备指南，使用SQLServer 2008设计、开发和部署更有效的数据集成、报表、分析解决方案所需的权威操作指南。...
第1章 RDBMS基础：SQLServer数据库的构成 1.1 数据库对象概述 1.1.1 数据库对象 1.1.2 事务日志 1.1.3 最基本的数据库对象：表 1.1.4 文件组 1.1.5 数据库关系图 1.1.6 视图 1.1.7 存储过程 1.1.8 用户自定义函数 ...
当您在运行 Windows 7 的计算机上安装 SQL Server 2008 FIX： 错误消息：调用或 BeginInvoke 之前不能调用控件上已创建窗口句柄 - Mozilla Firefox.pdf当您在运行 Windows 7 的计算机上安装 SQL Server 2008 FIX： ...
1、在开始->运行中输入... 您可能感兴趣的文章:SQL Server 2008图文安装教程SQL Server 2008 安装和配置图解教程(附官方下载地址)Microsoft SQL Server 2008安装图解教程(Windows 7)sql server 2008安装过程中服务器配
利用Microsoft SQL Server 2008实现灵活的商业智能解决方案使用Microsoft 完善的BI工具构建B0解决方案的必备指南，使用SQLServer 2008设计、开发和部署更有效的数据集成、报表、分析解决方案所需的权威操作指南。...
利用Microsoft SQL Server 2008实现灵活的商业智能解决方案使用Microsoft 完善的BI工具构建B0解决方案的必备指南，使用SQLServer 2008设计、开发和部署更有效的数据集成、报表、分析解决方案所需的权威操作指南。...
教你安装SQL Server 2005示例数据库 安装SQL server 2005时，如果你选择的是默认安装，就不会安装AdventureWorks 数据库。因为许多教程和例子都需要用到AdventureWorks 数据库，所以安装AdventureWorks 数据库非常...
SQLServer2012之后对窗口函数进行了极大的加强，但对于很多开发人员来说，对窗口函数却不甚了解，导致了这样强大的功能被浪费，因此本篇文章主要谈一谈SQL Server中窗口函数的概念。窗口函数，也可以被称为OLAP函数...
分类： SQL Server Windows server2003 + sql server2005 集群配置安装 一：环境 软硬件环境 虚拟3台windows server 2003主机。其中一台做域控DC，另外两台作为节点win1 win2. 域控DC网络设置： Hostname ...
在 "安装选择" 窗口，选择 "创建新的SQL Server实例..."。对于初次安装的用户，应选用这一安装模式，不需要使用 "高级选项" 进行安装。 "高级选项" 中的内容均可在安装完成后进行调整。点击下一步，出现下面对话框...
5、实验环境 Visual studio 2016至2019版本 C#, SQLServer 2008至2018版本 通过示例用短时间学习数据库的访问方式和程序代码，让初学者轻轻松松的熟悉和使用C#数据库编程，不必再去翻阅大量的资料，在这个基础...
（1）将TM\05\MyQQ\MyQQServer\MyQQServer\DataBase文件夹中的扩展名为db_MyQQData_Data.MDF和db_MyQQData_Log.LDF的两个文件拷贝到SQL Server安装路径下的Data文件夹中。 （2）打开SQL Server 2000中的“企业管理器...
SQL Server 2008是微软的SQL Server数据库中的最新版本，在该版本的数据库产品中融入了更多商业智能的内容。本书中也使用了一篇专门讲解了与商业智能有关的一些内容。 本书总分为5篇，共21章。第一篇主要讲解数据库...
压缩包内有详细使用说明，已通过验收，可以放心使用。 本系统的主要功能有： 1.登记进场，输入车牌号（不允许重复...点击更新，可以把特定的新增记录显示在窗口； 6.登录系统需要密码，有3个管理员，可以新增管理员。
SQL Server 2005中的窗口函数帮助你迅速查看不同级别的聚合，通过它可以非常方便地累计总数、移动平均值、以及执行其它计算。
4.可以设置调度,类似于 SQL Server 的导入导出 5.每个任务的所有设置都保存在一个dst的配置文件里，数据库连接密码 进行加密。加密过程是自己写的。以下是某个任务的配置文件 6.主窗口 根据任务的配置文件计算 任务...
+增加内存管理页，控制SQL Server内存占用，与企业管理器相同。 1.2.1 ^未开启服务时，关闭窗口会退出程序，开启服务时，关窗窗口则是隐藏； *附加数据库时，修正目标目录与源目录相同时无法附加的问题。 1.2.0 ...