开窗函数

一、背景

有时用开窗函数可以少些很多代码，故特意整理了一下。

参考：https://www.cnblogs.com/lihaoyang/p/6756956.html

大家可以看这位大佬的博客，我这篇博客是以大佬博客为蓝本，模仿写的。

 

二、数据准备

2.1 数据库

SQL SERVER

这些代码在其他数据库应该是通用的。

2.2 数据准备

2.2.1 建表

 

CREATE TABLE 
test.cnblogs.win_func (fname VARCHAR(200),fcity VARCHAR(200),fage INT,fsalary INT)

 

 2.2.2 插入数据

INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('Tom','BeiJing',20,3000);
INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('Tim','ChengDu',21,4000);
INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('Jim','BeiJing',22,3500);
INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('Lily','London',21,2000);
INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('John','NewYork',22,1000);
INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('YaoMing','BeiJing',20,3000);
INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('Swing','London',22,2000);
INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('Guo','NewYork',20,2800);
INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('YuQian','BeiJing',24,8000);
INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('Ketty','London',25,8500);
INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('Kitty','ChengDu',25,3000);
INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('Merry','BeiJing',23,3500);
INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('Smith','ChengDu',30,3000);
INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('Bill','BeiJing',25,2000);
INSERT INTO test.cnblogs.win_func(FName,FCity,FAge,FSalary)
VALUES('Jerry','NewYork',24,3300);

 

 

 2.2.2 查询数据

select * from test.cnblogs.win_func

 

  

三、基本格式

3.1  函数名(列) OVER(选项)

3.1.1 解释

OVER 关键字表示把函数当成开窗函数而不是聚合函数。

SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。

聚合函数有：COUNT、SUM、MAX、MIN、AVG

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

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

 

3.1.2 例子

求fsalary小于5000的人数和明细

select *,count(*) over()  AS 'fsalary小于五千人数' 
from test.cnblogs.win_func
where fsalary < 5000

 

 

  

如果未用开窗函数，代码如下

如果碰到排序，不用开窗函数代码将会更加复杂与恶心。

select *,(select  count(*)  from test.cnblogs.win_func where fsalary < 5000 )AS 'fsalary小于五千人数' 
from test.cnblogs.win_func
where fsalary < 5000

  

3.2  PARTITION BY 子句

3.2.1 解释

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

与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，

不同的开窗函数所创建的分区也不互相影响,创建分区的字段是可以有多个的，即partition by 后面跟的字段也可以有多个

3.2.2 例子

3.2.2.1 下面的 SQL 语句用于显示每一个人员的信息以及所属城市的人员数 

select fname,fcity,fage,fsalary,count(*) over(partition by fcity)  as '所在城市人数' from test.cnblogs.win_func

 

 

 

 可以看到：先按照fcity进行分区，BeiJing中有6行记录，所以所在城市人数为6，

 

3.2.2.2 不同的开窗函数所创建的分区也不互相影响

select fname,fcity,fage,fsalary,
count(*) over(partition by fcity)  as '所在城市人数', 
count(*) over(partition by fage)  as '同龄人个数',
count(*) over(partition by fcity,fage)  as '同龄人个数'
from test.cnblogs.win_func

  

  

可以看到：3种分区方式互不影响，并且创建分区的方式，即partition by 后面跟的字段也可以有多个。

 

3.3 ORDER BY子句

如果发现排序和我的不应，可以在语句的最后加上order by语句

3.3.1 解释

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

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

ORDER BY子句的语法为：

ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2

 

RANGE表示按照值的范围进行范围的定义，而ROWS表示按照行的范围进行范围的定义；边界规则的可取值见下表：

可取值	说明	示例
CURRENT ROW	当前行
N PRECEDING	向前偏移N行	3 PRECEDING
UNBOUNDED PRECEDING	一直到第一条记录
N FOLLWING	向后偏移N行	3 FOLLWING
UNBOUNDED FOLLOWING	一直到最后一条记录

 

“RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2” 部分用来定位聚合计算范围，这个子句又被称为定位框架。

 

3.3.2 例子

3.2.2.1 查询从第一行到当前行的工资总和

select fname,fcity,fage, fsalary,
sum(fsalary) over(order by fsalary rows between unbounded preceding and current row) '到当前行工资求和'
from test.cnblogs.win_func

 

 

 

 这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)”

表示按照FSalary进行排序，然后计算从第一行（UNBOUNDED PRECEDING）
到当前行（CURRENT ROW）的和，这样的计算结果就是按照工资进行排序的工资值的累积和。

 

3.2.2.2 查询从第一行到当前范围的工资总和

把例子3.2.2.1的row换成了range，是按照范围进行定位

select fname,fcity,fage, fsalary,
sum(fsalary) over(order by fsalary rows between unbounded preceding and current row) '到当前行工资求和',
sum(fsalary) over(order by fsalary range between unbounded preceding and current row) '到当前范围工资求和',
sum(fsalary) over(order by fsalary ) '简写工资求和'
from test.cnblogs.win_func

  

 “ROWS” 是按照行数进行范围定位的，而“RANGE”则是按照值范围进行定位的，这两个不同的定位方式 主要用来处理并列排序的情况。

比如 Lily、Swing、Bill这三个人的工资都是2000元，如果按照 “ROWS”进行范围定位，则计算从第一条到当前行的累积和，

而如果 如果按照 “RANGE”进行 范围定位，则仍然计算从第一条到当前行的累积和，不过由于等于2000元的工资有三个人，所 以计算的累积和为从第一条到2000元工资的人员结，

所以对 Lily、Swing、Bill这三个人进行开 窗函数聚合计算的时候得到的都是7000（ “ 1000+2000+2000+2000 ”）。

另外：简写形式与到当前范围工资求和结果相同

 

3.2.2.3  偏移

有兴趣可以看一下，但是应用场景应该不多

3.2.2.3.1 N PRECEDING AND N FOLLOWING

SELECT fname,fsalary,
SUM(fsalary) OVER(ORDER BY fsalary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) as '前二后二和'
FROM test.cnblogs.win_func

 

 

以Excel的方式进行说明

 

 

  

 

这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)”表示按照FSalary进行排序，

然后计算从当前行前偏移两行，向后偏移两行，加上自己的一行，共计五行，进行SUM求和，

注意对于第一条和第二条而言它们 的“前两行”是不存在或者不完整的，因此计算的时候也是要按照前两行是不存在或者不完整进 行计算，

同样对于最后两行数据而言它们的“后两行”也不存在或者不完整的，同样要进行类似 的处理。

 

3.2.2.3.2  其他偏移

select fname,fcity,fage, fsalary,
sum(fsalary) over(order by fsalary rows between unbounded preceding and 3 preceding) as '从第一条记录前偏3行',
sum(fsalary) over(order by fsalary rows between unbounded preceding and 3 following) as '从第一条记录后偏3行',
sum(fsalary) over(order by fsalary rows between 3 preceding and unbounded following) as '从前偏3行到最后一行',
sum(fsalary) over(order by fsalary rows between 3 following and unbounded following) as '从后偏3行到最后一行',
sum(fsalary) over(order by fsalary rows between 3 preceding and 5 preceding) as '前偏3到前偏5',
sum(fsalary) over(order by fsalary rows between 3 following and 5 following) as '后偏3到后偏5',
sum(fsalary) over(order by fsalary rows between 5 preceding and 3 preceding) as '前偏5到前偏3',
sum(fsalary) over(order by fsalary rows between 5 following and 3 following) as '后偏5到后偏3'
from test.cnblogs.win_func
order by fsalary

  

  

 理论基础与3.2.2.3.1相同，太多了就不用EXCEL在加以说明了。

从前偏3到前偏5所有都是空，可以这样理解假如没有负数：从1到3经过了2个数，但是从3到1又经过了几个数呢，因为没有负数，我们就可以定位NULL。

3.2.2.4  算工资排名

SELECT fname, fsalary,
COUNT(*) OVER(ORDER BY fsalary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as '工资排名_count',
ROW_NUMBER() OVER(ORDER BY fsalary ) as '工资排名_row_nomber'
FROM test.cnblogs.win_func

 

我个人更喜欢用row_number(),后面会有涉及。

 

3.2.2.5  算工资最值，均值，分区值

SELECT fname, fsalary,fage,
MAX(fsalary) OVER(ORDER BY fage) AS '此行之前最大值',
MIN(fsalary) OVER(ORDER BY fage) AS '此行之前最小值',
AVG(fsalary) OVER(ORDER BY fage) AS '此行之前均值',
MAX(fsalary) OVER(PARTITION BY fage ORDER BY fage) AS '年龄分区后此行之前最大值',
MAX(FSalary) OVER(PARTITION BY fage order by fsalary) AS '同龄人最高工资'
FROM test.cnblogs.win_func
order by fage

 

 

四、高级开窗函数

4.1 排名的实现ROW_NUMBER();rank() ,dense_rank()

SELECT fname, fsalary,fage,
RANK() OVER(ORDER BY fsalary desc) as 'f_RANK',
DENSE_RANK() OVER(ORDER BY fsalary desc) as 'f_DENSE_RANK',
ROW_NUMBER() OVER(ORDER BY fsalary desc) as 'f_ROW_NUMBER'
FROM test.cnblogs.win_func
order by fsalary desc

  

【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。

【参数】dense_rank与rank()用法相当，

【区别】dence_rank在并列关系是，相关等级不会跳过。rank则跳过 rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内） dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。

 

4.2 ROW_NUMBER() 详解

https://www.cnblogs.com/qianslup/p/13226404.html