谈开窗函数

开窗函数简介

与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。在ISO SQL规定了这样的函数为开窗函数，在 Oracle中则被称为分析函数，而在DB2中则被称为OLAP函数。

T_Person 表保存了人员信息
FName 字段为人员姓名
FCity 字段为人员所在的城市名
FAge 字段为人员年龄
FSalary字段为人员工资
然后执行下面的SQL语句向 T_Person表中插入一些演示数据：

--1、创建表：
create table T_Person(
    FName varchar(20),
    FCity varchar(20),
    FAge int,
    FSalary INT

--2、插入数据
INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
VALUES('Tom','BeiJing',20,3000);   
INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
VALUES('Tim','ChengDu',21,4000);   
INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
VALUES('Jim','BeiJing',22,3500);   
INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
VALUES('Lily','London',21,2000);   
INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
VALUES('John','NewYork',22,1000);   
INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
VALUES('YaoMing','BeiJing',20,3000);   
INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
VALUES('Swing','London',22,2000);   
INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
VALUES('Guo','NewYork',20,2800);   
INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
VALUES('YuQian','BeiJing',24,8000);   
INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
VALUES('Ketty','London',25,8500);   
INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
VALUES('Kitty','ChengDu',25,3000);   
INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
VALUES('Merry','BeiJing',23,3500);   
INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
VALUES('Smith','ChengDu',30,3000);   
INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
VALUES('Bill','BeiJing',25,2000);   
INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
VALUES('Jerry','NewYork',24,3300);  

1. 想查询每个工资小于 5000元的员工信息（城市以及年龄） ，并且在每行中都显示所有工资小于5000元的员工个数

可以通过子查询来解决这个问题，SQL如下：

select a.*,
(select count(1) from T_Person where FSalary < 5000) CNT
from T_Person as a
where a.FSalary < 5000

虽然使用子查询能够解决这个问题，但是子查询的使用非常麻烦，使用开窗函数则可以大大简化实现，下面的SQL语句展示了如果使用开窗函数来实现同样的效果：

select *,count(1) over() as CNT from T_Person a
where a.FSalary < 5000

可以看到与聚合函数不同的是，开窗函数在聚合函数后增加了一个OVER 关键字。开窗函数的调用格式为：
函数名(列) OVER(选项)
OVER关键字表示把函数当成开窗函数而不是聚合函数。SQL标准允许将所有聚合函数用做开窗函数，使用OVER 关键字来区分这两种用法。
在上边的例子中，开窗函数COUNT(*)OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果OVER关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

ROW_NUMBER 函数
ROW_NUMBER() 从1开始，按照顺序，生成分组内记录的序列

RANK 和DENSE_RANK 函数
RANK()生成数据项在分组中的排名，排名相等会在名次中留下空位
DENSE_RANK()生成数据项在分组中的排名，排名相等会在名次中不会留下空位

CUME_DIST 函数
cume_dist 返回小于等于当前值的行数/分组内总行数
比如，我们可以统计小于等于当前薪水的人数，所占总人数的比例

LAG 和 LEAD 函数
LAG(col,n,DEFAULT)用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

with tmp as(
select '1' id ,'aa' name ,22 age  union all
select '2' id ,'bb' name ,20 age union all
select '3' id ,'CC' name ,21 age 
)
select 
    a.*,  lag(age,1) over (order by id desc) lag,
    a.age - lag(age,1) over (order by id desc) lag1  
from tmp a

image.png

LEAD 函数则与 LAG相反：
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

FIRST_VALUE 和 LAST_VALUE 函数
FIRST_VALUE 取分组内排序后，截止到当前行，第一个值

LAST_VALUE函数则相反：
LAST_VALUE 取分组内排序后，截止到当前行，最后一个值
这两个函数还是经常用到的（往往和排序配合使用），比较实用！