窗口函数是SQL Server2005新增的函数。下面就谈谈它的基本概念:
窗口函数的作用
窗口函数是对一组值进行操作,不需要使用GROUP BY 子句对数据进行分组,还能够在同一行中同时返回基础行的列和聚合列。举例来说,我们要得到一个年级所有班级所有学生的平均分,按照传统的写法,我们肯定是通过AVG聚合函数来实现求平均分。这样带来的”坏处“是我们不能轻松地返回基础行的列(班级,学生等列),而只能得到聚合列。因为聚合函数的要点就是对一组值进行聚合,以GROUP BY 查询作为操作的上下文,由于GROUP BY 操作对数据进行分组后,查询为每个组只返回一行数据,因此,要限制所有表达式为每个组只返回一个值。而通过窗口函数,基础列和聚合列的查询都轻而易举。
基本语法
OVER([PARTITION BY value_expression,..[n] ] <ORDER BY BY_Clause>)
窗口函数使用OVER函数实现,OVER函数分带参和不带参两种。其中可选参数PARTITION BY用于将数据按照特定字段分组。
窗口函数使用OVER函数实现,OVER函数分带参和不带参两种。其中可选参数PARTITION BY用于将数据按照特定字段分组。
适用范围:
排名开窗函数和聚合开窗函数.
也就是说窗口函数是结合排名开窗函数或者聚合开窗函数一起使用
OVER子句前面必须是排名函数或者是聚合函数
注释:
开窗函数是在 ISO SQL 标准中定义的。SQL Server 提供排名开窗函数和聚合开窗函数。窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值。
可以在单个查询中将多个排名或聚合开窗函数与单个 FROM 子句一起使用。但是,每个函数的 OVER 子句在分区和排序上可能不同。OVER 子句不能与 CHECKSUM 聚合函数结合使用。
例:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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] [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]
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--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)
--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)
1,查询学生成绩表的基本列以及所有班级所有学生的语文平均分:
语句:
SELECT
studentid,
classid,
courseid,
score,
AVG(score) OVER() AS 'yuwen'
FROM StudentScore
WHERE courseid = 2
studentid,
classid,
courseid,
score,
AVG(score) OVER() AS 'yuwen'
FROM StudentScore
WHERE courseid = 2
2,如果我们需要查询每一个班级的语文平均分,可以根据PARTION BY来进行分组
语句:
SELECT studentid,
classid,
courseid,
score,
AVG(score) OVER(PARTITION BY classid) AS 'yuwen'
FROM StudentScore
WHERE courseid = 2
classid,
courseid,
score,
AVG(score) OVER(PARTITION BY classid) AS 'yuwen'
FROM StudentScore
WHERE courseid = 2
到这里,其实你可能已经体会到使用OVER函数的好处了:
a、OVER子句的优点就是能够在返回基本列的同时,在同一行对它们进行聚合
b、可以在表达式中混合使用基本列和聚合列
b、可以在表达式中混合使用基本列和聚合列
如果我们使用传统的GROUP BY分组查询,直接获取基本列和聚合列就不是这么简单一句SQL了。
如你所知,我们知道的很多聚合函数,如SUM,AVG,MAX,MIN等聚合函数都支持窗口函数的运算。