SQL SERVER开窗函数

SQL SERVER开窗函数 - csdbfans - 博客园 (cnblogs.com)

SQL Server聚合函数与聚合开窗函数 - Brambling - 博客园 (cnblogs.com)

 

先推荐一本书《程序员的SQL金典》

今天将要介绍SQL Sever的开窗函数，何谓开窗函数，不懂吧。
反正对于我来说，我是摸不着头脑了，第一次听说过。那么，什么是开窗函数，其实可以理解为是聚合函数的一个加强版。
因为使用聚合函数的话（不包括子查询的情况），整个查询都只能是聚合列返回值，而不能有基础行的返回值。
那么对于需要基础行的返回值的话，就需要使用复杂的子查询或者是存储过程等才可以解决。
但是使用开窗函数，它能够在同一行中同时返回基础行的列和聚合列。
在ISO SQL规定了这样的函数为开窗函数，在Oracle中则被称为分析函数，而在DB2中则被称为OLAP函数

 

开窗函数与聚合函数一样，都是对行的集合组进行聚合计算。
它用于为 "行" 定义一个窗口(这里的窗口是指运算将要操作的行的集合)，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。
反正我理解这个函数 已经使用好子查询或者是其它方式求得聚合列的值==>给我合并。

以书中的例子一步一步来介绍，假设要计算所有人员的总数，我们可以执行下面的SQL语句：

SELECT COUNT(FName) FROM T_Person

这种方式比较直接，只返回一个聚合列的值，没有任何基础行的列的值。但是有时需要从不在聚合函数中的行的列中访问这些聚合计算的值（即基础行的列）。比如我们想查询每个工资小于5000元的员工信息（城市以及年龄），并且在每行中都显示所有工资小于5000 元的员工个数，尝试编写下面的SQL语句：

SELECT FName, FCITY, FAGE, FSalary, COUNT(FName)
FROM T_Person
WHERE FSALARY<5000

执行上面的SQL以后我们会得到下面的错误信息：
选择列表中的列'T_Person.FCity' 无效，因为该列没有包含在聚合函数或GROUP BY 子句中。
这是因为所有不包含在聚合函数中的列必须声明在GROUP BY 子句中，使用子查询的方式是可以解决：

SELECT FName, FCITY, FAGE, FSalary,
(
SELECT COUNT(FName) FROM T_Person WHERE FSALARY<5000
)
FROM T_Person
WHERE FSALARY<5000

可以看到与聚合函数不同的是，开窗函数在聚合函数后增加了一个OVER关键字

格式为:函数名(列) OVER(选项) 

我这里使用的是SQL Server 2008 R2,不知道从什么时候开始，SQL SERVER也支持开窗函数中使用ORDER BY子句（注：书本中说MSSQLServer中是不支持开窗函数中使用ORDER BY子句）。
也正因为开窗函数支持了ORDER BY子句之后，开窗函数被分为两大类。

聚合开窗函数====》聚合函数(列) OVER (选项)，这里的选项可以是PARTITION BY子句，但不可是ORDER BY子句
排序开窗函数====》排序函数(列) OVER(选项)，这里的选项可以是ORDER BY子句，也可以是　OVER（PARTITION BY子句　ORDER BY子句），但不可以是PARTITION BY子句

 聚合开窗函数

OVER 关键字表示把聚合函数当成聚合开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做聚合开窗函数。
在上边的例子中，
开窗函数COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。
OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。
如果OVER关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算

PARTITION BY 子句

开窗函数的OVER关键字后括号中的可以使用PARTITION BY子句来 "定义行的分区" 来供进行聚合计算。
与GROUP BY 子句不同,
PARTITION BY 子句创建的分区是独立于结果集的,创建的分区只是供进行聚合计算的,
而且不同的开窗函数所创建的分区也不互相影响.

比如下面的SQL语句用于显示每一个人员的信息、所属城市的人员数以及同龄人的人数：

SELECT FName,FCITY, FAGE, FSalary,
COUNT(FName) OVER(PARTITION BY FCITY),
COUNT(FName) OVER(PARTITION BY FAGE)
FROM T_Person

 排序开窗函数

对于排序开窗函数来讲，它支持的开窗函数分别为：ROW_NUMBER（行号）、RANK（排名）、DENSE_RANK（密集排名）和NTILE(分组排名)

select  FName, FSalary, FCity, FAge,  
row_number() over(order by FSalary) as rownum,  
rank() over(order by FSalary) as rank,  
dense_rank() over(order by FSalary) as dense_rank,  
ntile(6) over(order by FSalary)as ntile 
from  T_Person 
order by  FName

下面的是运行结果： 

 

 

 

看到上面的结果了吧，下面来介绍下相关的内容。我们得到的最终结果是按照FName进行升序显示的。

对于row_number() over(order by FSalary) as rownum来说，
这个排序开窗函数是按FSalary升序的方式来排序，并得出排序结果的序号

对于rank() over(order by FSalary) as rank来说，
这个排序形容函数是按FSalary升序的方式来排序，并得出排序结果的排名号。
这个函数求出来的排名结果可以排列，并列排名之后的排名将是并列的排名加上并列数
（简单说每个人只有一种排名，然后出现两个并列第一名的情况，
  这时候排在两个第一名后面的人将是第三名，没有了第二名，但是有两个第一名）

对于dense_rank() over(order by FSalary) as dense_rank来说，
这个排序函数是按FSalary升序的方式来排序，并得出排序结果的排名号。
这个函数与rand()函数不同在于，
并列排名之后的排名只是并列排名加１
（简单说每个人只有一种排名，然后出现两个并列第一名的情况，
 排在两个第一名后面的人将是第二名，也就是两个第一名，一个第二名）

对于ntile(6) over(order by FSalary)as ntile 来说，
这个排序函数是按FSalary升序的方式来排序，并得出排序结果的分组数。

 排序函数和聚合开窗函数类似，也支持在OVER子句中使用PARTITION BY语句 

select  FName, FSalary, FCity, FAge,  
row_number() over(partition by FName  order by FSalary) as rownum,  
rank() over(partition by FName order by FSalary) as rank,  
dense_rank() over(partition by FName order by FSalary) as dense_rank,  
ntile(6) over(partition by FName order by FSalary)as ntile 
from  T_Person 
order by  FName

 关于PARTITION BY子句，请看上面的介绍，这里就不再累赘了。

注意的是，在排序开窗函数中使用PARTITION BY子句需要放置在ORDER　BY子句之前