SQL开窗函数：格式、示例、PARTITION BY 子句、ORDER BY子句、常用聚合函数

一、开窗函数介绍

开窗函数（使用OVER字句定义）用于为行定义一个“窗口”，并使用窗口对行集合进行聚合计算。

使用开窗函数后，可以不需要使用GROUP BY子句对数据进行分组，就能够在同一行中同时返回聚合结果。

二、开窗函数格式

格式：函数名(列) OVER(选项)

• OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。

• OVER 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。

• 如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

三、简单例子

示例数据：

员工信息表，包括字段有：员工姓名、省份、城市、年龄、薪水

示例SQL：

按照PProvince（省份）、Pcity（城市）汇总PSalary（薪水）
select distinct PProvince,Pcity,sum(PSalary) over () 
from TMP_Person 

运行结果：

sum累加函数，over表示sum是开窗函数，over(）内选项为空，因此累加所有17条记录的PSalary

四、PARTITION BY 子句

开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。

与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。

在同一个SELECT语句中可以同时使用多个开窗函数，而且这些开窗函数并不会相互干扰。

示例：多个PARTITION BY

select PName,
       PProvince,
       Pcity,
       PAge,
       PSalary,
       sum(PSalary) over(partition by Pcity) 所属城市的总工资,
       count(*) over(partition by PAge) 同龄人个数
  from TMP_Person

五、ORDER BY子句

开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则，而且有的开窗函数还要求必须指定排序规则。

使用ORDER BY子句可以对结果集按照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。

“RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”是开窗函数中最常使用的定位框架，为了简化使用，如果使用的是这种定位框架，则可以省略定位框架声明部分。

示例1：默认ORDER BY

select PName,
       PProvince
       PCity,
       PAge,
       PSalary,
       sum(PSalary) over(order by PSalary ) 到当前行对应工资等级的工资求和
from TMP_Person

示例2：添加ORDER BY参数

select PName,
         PProvince
         PCity,
         PAge,
         PSalary,
         sum(PSalary) over(order by PSalary rows between unbounded preceding and current row) 到当前行的工资求和
  from TMP_Person

六、常用聚合函数搭配使用

常用聚合函数
（1）row_number() over(partition by … order by …)增加一列,类似与增加伪列
（2）rank() over(partition by … order by …)
（3）dense_rank() over(partition by … order by …)
rank(): 跳跃排序，如果有两个第一级时，接下来就是第三级。
dense_rank(): 连续排序，如果有两个第一级时，接下来仍然是第二级。
（4）count() over(partition by … order by …)
（5）max() over(partition by … order by …)
（6）min() over(partition by … order by …)
（7）sum() over(partition by … order by …)
（8）avg() over(partition by … order by …)
（9）first_value() over(partition by … order by …)
（10）last_value() over(partition by … order by …)
与函数的功能一致,只是是开窗函数
（11）lag() over(partition by … order by …)
（12）lead() over(partition by … order by …)
lag 和lead 可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）；
lag ，lead 分别是向前，向后；
lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值）