函数 over() 的含义

目录

一、开窗函数和聚合函数的含义

1、开窗函数的定义

2、开窗函数

二、开窗函数的具体介绍---聚合开窗函数和排序开窗函数

1、聚合开窗函数

2、排序开窗函数

---

一、开窗函数和聚合函数的含义

1、开窗函数的定义

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

     但是与聚合函数不同的是，开窗函数在聚合函数后增加了一个OVER关键字。

2、开窗函数

①、格式  

             函数名(列) OVER(选项)

②、分类 

第一大类：聚合开窗函数====》聚合函数(列) OVER (选项)，这里的选项可以是PARTITION BY子句，但不可是ORDER BY子句

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

二、开窗函数的具体介绍---聚合开窗函数和排序开窗函数

1、聚合开窗函数

        OVER 关键字表示把聚合函数当成聚合开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做聚合开窗函数。在上边的例子中，开窗函数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

2、排序开窗函数

       对于排序开窗函数来讲，它支持的开窗函数分别为：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  

       ①、对于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升序的方式来排序，然后6等分成6个组吗，并显示所在组的序号。

排序函数和聚合开窗函数类似，也支持在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

参考：sql中的 开窗函数over() 聚合函数 排名函数