SQLServer中OVER结合排名函数或聚合函数的使用

窗口函数是SQL Server2005新增的函数。下面就谈谈它的基本概念：

窗口函数的作用

窗口函数是对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，还能够在同一行中同时返回基础行的列和聚合列。举例来说，我们要得到一个年级所有班级所有学生的平均分，按照传统的写法，我们肯定是通过AVG聚合函数来实现求平均分。这样带来的”坏处“是我们不能轻松地返回基础行的列（班级，学生等列），而只能得到聚合列。因为聚合函数的要点就是对一组值进行聚合，以GROUP BY查询作为操作的上下文，由于GROUP BY操作对数据进行分组后，查询为每个组只返回一行数据，因此，要限制所有表达式为每个组只返回一个值。而通过窗口函数，基础列和聚合列的查询都轻而易举。

基本语法

OVER([PARTITION BY value_expression,..[n] ] <ORDER BY BY_Clause>)
窗口函数使用OVER函数实现，OVER函数分带参和不带参两种。其中可选参数PARTITION BY用于将数据按照特定字段分组。
适用范围：排名开窗函数和聚合开窗函数.也就是说窗口函数是结合排名开窗函数或者聚合开窗函数一起使用的，OVER子句前面必须是排名函数或者是聚合函数

注释：开窗函数是在ISO SQL标准中定义的，SQL Server提供排名开窗函数和聚合开窗函数。窗口是用户指定的一组行，开窗函数计算从窗口派生的结果集中各行的值，可以在单个查询中将多个排名或聚合开窗函数与单个FROM子句一起使用，但是每个函数的OVER子句在分区和排序上可能不同。OVER子句不能与CHECKSUM聚合函数结合使用。

例：表结构

CREATE  TABLE  StudentScore(

StudentId  int NOTNULL  ,

ClassId  int NOTNULL,

CourseId  int NOTNULL  ,

Score  int NOTNULL 

);

表数据
–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);
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);
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);
–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);
1，查询学生成绩表的基本列以及所有班级所有学生的语文平均分：
语句：
SELECT
studentid,
classid,
courseid,
score,
AVG(score) OVER() 
FROM StudentScore
WHERE courseid = 2
2，如果我们需要查询每一个班级的语文平均分，可以根据PARTION BY来进行分组
语句：
SELECT  studentid,
classid,
courseid,
score,
AVG(score) OVER(PARTITION BY classid) 
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 
ROW_NUMBER() OVER (ORDER BY score DESC) ,
studentid,
classid,
courseid,
score
FROM StudentScore
WHERE courseid = 8
2、RANK()和DENSE_RANK()
（1）RANK()函数
返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名
SELECT 
RANK() OVER (ORDER BY score DESC) ,
studentid,
classid,
courseid,
score
FROM StudentScore
WHERE courseid = 8
注意，它和ROW_NUMBER()的异同点是：
a、RANK函数和ROW_NUMBER函数类似，它们都是用来对结果进行排序。
b、不同的是，ROW_NUMBER函数为每一个值生成唯一的序号，而RANK函数为相同的值生成相同的序号。
（2）DENSE_RANK()函数
返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名
SELECT 
DENSE_RANK() OVER (ORDER BY score DESC) ,
studentid,
classid,
courseid,
score
FROM StudentScore
WHERE courseid = 8
DENSE_RANK()函数查询的序号是类似ROW_NUMBER()那样连续的，但是对于相同值的行生成相同的序号，从这一点上来说，对于相同查询条件和排序的查询，ROW_NUMBER()函数查询的结果集是DENSE_RANK()函数查询的结果的子集）。这也是RANK和DENSE_RANK()这两个函数的最大的不同点。
3、NTILE(integer_expression)
NTILE函数把结果中的行关联到组，并为每一行分配一个所属的组的编号，编号从1开始。对于每一个行，NTILE将返回此行所属的组的编号。
如果分区的行数不能被integer_expression整除，则将导致一个成员有两种大小不同的组。按照OVER子句指定的顺序，较大的组排在较小的组前面
SELECT 
NTILE(6) OVER (ORDER BY classid DESC) ,
studentid,
classid,
courseid,
score
FROM StudentScore
WHERE courseid = 8

对于四种排名函数查询出的结果希望大家自己亲自试验一下，自己体会每一种排名与其他的不同之处！