聚合开窗函数SQLSERVER开窗函数
作为⼀名开发⼈员来讲，我感觉在职场⽩混了好多年，可能是⾃⼰真的没有进取的精神吧，看了《》这本电⼦书，真的让我学到了不少知识，真⼼喜欢这本电⼦书，书中讲解的内容⽐较好懂，也⽐较实⽤。谢谢作者的⾟勤汗⽔:)。
今天将要介绍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
虽然使⽤⼦查询能够解决这个问题，但是⼦查询的使⽤⾮常⿇烦，使⽤开窗函数则可以⼤⼤简化实现，下⾯的SQL语句展⽰了如果使⽤开窗函数来实现同样的效果：
SELECT FName, FCITY, FAGE, FSalary, COUNT(FName) OVER()
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 标准允许将所有聚合函数⽤做聚合开窗函数。
PARTITION BY ⼦句
排序开窗函数
在上边的例⼦中，开窗函数COUNT(*) OVER()对于查询结果的每⼀⾏都返回所有符合条件的⾏的条数。OVER 关键字后的括号中还经常添加选项⽤以改变进⾏聚合运算的窗⼝范围。如果OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有⾏进⾏聚合运算。
开窗函数的OVER 关键字后括号中的可以使⽤PARTITION BY ⼦句来定义⾏的分区来供进⾏聚合计算。与GROUP BY ⼦句不同，PARTITION BY ⼦句创建的分区是独⽴于结果集的，创建的分区只是供进⾏聚合计算的，⽽且不同的开窗函数所创建的分区也不互相影响。下⾯的SQL 语句⽤于显⽰每⼀个⼈员的信息以及所属城市的⼈员数：
SELECT  FName, FCITY, FAGE, FSalary,
COUNT (FName) OVER (PARTITION BY  FCITY)
FROM  T_Person
OVER (PARTITION BY  FCITY)表⽰对结果集按照FCITY 进⾏分区，并且计算当前⾏所属的组的聚合计算结果。在同⼀个SELECT 语句中可以同时使⽤多个开窗函数，⽽且这些开窗函数并不会相互⼲扰。⽐如下⾯的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 （分组排名）。
先看⼀段SQL 语句：
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_Personrank函数怎么排名
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⼦句之前。
⾄此本⽂完。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。