SQL: OVER子句的应用

一、OVER子句介绍

1) 在相关联的开窗函数被应用之前，OVER子句用于对行集进行分区和排序。

2) OVER子句应用于：排名开窗函数(Ranking window functions)和聚合开窗函数(Aggregate window functions)。

3) 语法：

Ranking Window Functions

< OVER_CLAUSE > :: =

OVER ( [ PARTITION BY value_expression , ... [ n ] ]

<ORDER BY_Clause> )

Aggregate Window Functions

< OVER_CLAUSE > :: =

OVER ( [ PARTITION BY value_expression , ... [ n ] ] )

4) 下面来举两个例子：

有两个表格，一个表格为学生表格(student)，包含学号(stdid)，姓名(stdname)，班级(class);

另一个表格为课程表格(course)，包含课程ID(crsid)，学生学号(stdid)，课程名(crsname),成绩(score)。

· 将学生表的记录按学生姓名进行排序（按班级分区），同时返回一列rownum显示序列号。

SELECT ROW_NUMBER() OVER(PARTITION BY s.class ORDER BY s.stdname) as rownum,

s.stdid,s.stdname, s.class

FROM dbo.student as s;

· 计算每个学生的总成绩，并按总成绩排序

SELECT DISTINCT S.stdid,S.stdname,

SUM(C.score) OVER(PARTITION BY C.stdid) as Totalscore

FROM dbo.student AS S

JOIN dbo.course AS C

on C.stdid=S.stdid

ORDER BY Totalscore DESC;

二、相关知识

因为正在学习SQL的知识，在学习OVER子句前对行集函数和排名函数都不了解，于是多做了一些搜索，以下为我在了解OVER子句时的预备知识。

1. SQL内置函数

SQL内置函数包含四类：行集函数(Rowset Functions)，聚合函数(Aggregate Functions)，排名函数(Ranking Functions)和标量函数(Scalar Functions)。

1） SQL:聚合函数（Aggregate Functions）

聚合函数通过对一组数据进行计算而返回一个单个值，经常与SELECT语句的GROUP BY子句一起使用。

Transact-SQL提供以下聚合函数：

AVG; CHECKSUM_AGG; COUNT_BIG; COUNT; GROUPING; GROUPING_ID; MAX; MIN; ROWCOUNT_BIG; STDEV; STDEVP; SUM; VAR; VARP等

注意：除了COUNT外，所有聚合函数都会忽略空值(null)。

聚合函数只能在以下位置作为表达式使用：

· SELECT 语句的选择列表（子查询或外部查询）。

· COMPUTE 或 COMPUTE BY 子句。

· HAVING 子句

2） SQL:排名函数(Ranking Functions)

排名函数为分区中的每一行返回一个排名值。

Transact-SQL提供以下排名函数：RANK, DENSE_RANK,NTILE,ROW_NUMBER等。

参考资料：

http://msdn.microsoft.com/zh-cn/library/ms174318.aspx

http://msdn.microsoft.com/zh-cn/library/ms173454.aspx

http://msdn.microsoft.com/zh-cn/library/ms189798.aspx

http://msdn.microsoft.com/en-us/library/ms189461.aspx