SQL Sever开窗函数

数据分析且徐行 2021-09-15

892

我们进行数据处理的时候经常会涉及到各种聚合函数的应用，比如计数（count）、求和（sum）这类聚合函数，需要分组统计的时候我们很容易就能想到用group by进行分组。

但是使用聚合函数，会按分组条件将多行记录聚合成一行，每组只返回一个值；并且在使用聚合函数后，如果要显示相关列必须将其加入到group by语句中。这样就会导致有些查询语句只能通过编写复杂的子查询或者存储过程来完成。

于是2003年ISO SQL标准加入了开窗函数（Window Function），与聚合函数一样，开窗函数也是对组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值并在每一行的最后一列添加聚合函数的结果，使用起来非常方便。

这类函数在SQL Sever里被称为开窗函数，在Oracle中则被称为分析函数，而在DB2中被称为OLAP（On-Line Analytical Processing）函数。

开窗函数基本语法：

<开窗函数>

over ([partition by <用于分组的字段>]

order by <用于排序的字段>)

重点注意：

PARTITION BY用于将结果集进行分组，ODER BY 指定按哪个字段进行排序；
在同一个SELECT语句中，可以同时使用多个开窗函数，而且这些开窗函数并不会相互干扰。

SQL Sever的开窗函数（Window Function）分为4大类：

聚合开窗函数
排名开窗函数
定位开窗函数
分布开窗函数

▍新建临时测试数据表Temp_Test

CREATE TABLE Temp_Test (UserName VARCHAR(20),City VARCHAR(20),Age INT,Salary INT)
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('Tom','BeiJing',20,3000);
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('Tim','ChengDu',21,4000);
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('Jim','BeiJing',22,3500);
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('Lily','London',21,2000);
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('John','NewYork',22,1000);
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('YaoMing','BeiJing',20,3000);
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('Swing','London',22,2000);
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('Guo','NewYork',20,2800);
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('YuQian','BeiJing',24,8000);
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('Ketty','London',25,8500);
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('Kitty','ChengDu',25,3000);
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('Merry','BeiJing',23,3500);
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('Smith','ChengDu',30,3000);
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('Bill','BeiJing',25,2000);
INSERT INTO Temp_Test(UserName,City,Age,Salary) VALUES('Jerry','NewYork',24,3300);

得到的数据如下：

▍聚合开窗函数

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

示例1：按照 Salary逆序，并累计值。

select UserName,City,Age,Salary,
sum(Salary) over(order by Salary desc) '工资'
from Temp_Test

示例2：先按City分组，组内再按照Salary逆序并累计值。

select UserName,City,Age,Salary,
sum(Salary) over(partition by City order by Salary desc) '工资'
from Temp_Test

▍排名开窗函数

ROW_NUMBER（唯一行号）

为每一组的N行按顺序生成唯一的序号（1，2，3...，N）

示例1：按Salary降序生成工资排名

select UserName,City,Age,Salary,
ROW_NUMBER() over(order by Salary desc) '工资排名'
from Temp_Test

示例2：先按City分组，组内再按照Salary降序排名

select UserName,City,Age,Salary,
ROW_NUMBER() over(partition by City order by Salary desc) '城市工资排名'
from Temp_Test

RANK（排名）

RANK()为每一组的行生成一个序号，但是却与ROW_NUMBER()有点不同ROW_NUMBER()按照ORDER BY的排序，如果有相同的值，会生成不同的序号；

RANK()按照ORDER BY的排序，如果有相同的值，会生成不同的序号并且接下来的序号是不连续的。

例如两个相同的行生成序号2，那么接下来会生成序号4。

DENSE_RANK（密集排名）

和RANK类似，不同的是如果有相同的序号，那么接下来的序号不会间断。例如两个相同的行生成序号2，那么接下来生成的序号还是3。

Salary	ROW_NUMBER	RANK	DENSE_RANK
3000	1	1	1
5000	2	2	2
5000	3	2	2
8000	4	4	3

NTILE (分组排名)

按指定的组数进行平均分组，并为每一组生成一个序号。

示例：先按City分组，组内再按照Salary降序分成两个组

select UserName,City,Age,Salary,
NTILE(2) over(partition by City order by Salary desc) '组序号'
from Temp_Test

解析：上图中BeiJing有6个人，分成两组正好每组3个人；ChengDu只有3个人，分成两组后第一组2个人，第二组只有1个人。

那我们来看一下若是分成3组是如何分的：

再来看一下若是分成4组是如何分的：

再来看一下若是分成5组是如何分的：

我们可以看出NTILE()只是尽量平均的进行分组。

▍定位开窗函数

LAG()

LAG(COL，N，DEFAULT_VALUE) 用于统计窗口内往上第N行的值，第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（即往上第N行是NULL的时候，显示默认值，如不指定，则显示NULL）。

下面我们看个例子加深理解：

select UserName,City,Age,Salary,
LAG(Salary,2) over(partition by City order by Age) '未指定默认值',
LAG(Salary,2,999) over(partition by City order by Age) '指定默认值为999'
from Temp_Test

解析：先按City进行分组，组内按Age升序排序，在这个的基础上，取往上2行的数据，若往上两行没有数据且没有指定默认值则显示NULL，有指定默认值则显示默认值。

LEAD()

正好与上面的LAG()相反，LEAD()是统计窗口内往下第N行的值。

select UserName,City,Age,Salary,
LEAD(Salary,2) over(partition by City order by Age) '未指定默认值',
LEAD(Salary,2,999) over(partition by City order by Age) '指定默认值为999'
from Temp_Test

FIRST_VALUE

顾名思义，取分组内排序后，截止到当前行的第一个值。

select UserName,City,Age,Salary,
FIRST_VALUE(Salary) over(partition by City order by Age) '组内排序后第一个值'
from Temp_Test

LAST_VALUE

取分组内排序后，截止到当前行的最后一个值。

select UserName,City,Age,Salary,
LAST_VALUE(Salary) over(partition by City order by Age) '组内排序后的最后一个值'
from Temp_Test

解析：我们可能会有疑问，跟我们想象中的“组内排序后的最后一个值”似乎不太一样，我们想象中的BeiJing这个组内排序后的最后一个值应该是2000。

但是，其实上图中得出的结果确定是符合LAST_VALUE的逻辑的。

我们从第1行看起，第1行的Salary为3000，截止到当前行的最后一个值也为3000，第2行的Salary为3000，截止到当前行的最后一个值其实变成了第2行的Salary值3000，第3行的Salary值为3500，截止到当前行的最后一个值随着变成了第3行的值3500......。

我们再来看组间的过度，比如第六行的最后一个值为2000，第七行是ChengDu组内的第1行，截止到当前行的最后一个值也正是第一行的4000......。

这里我们要特别注意的是截止到当前行这个条件。

如果想取得分组内排序后的最后一个值，则需要变通一下：

select UserName,City,Age,Salary,
FIRST_VALUE(Salary) over(partition by City order by Age DESC) '组内排序后的最后一个值'
from Temp_Test
ORDER BY City,Age

▍分布开窗函数

PERCENT_RANK()

计算一个值在查询结果集或分区中的百分比排名。

PERCENT_RANK 计算的逻辑是：

(分组内当前行的RANK值-1)/(分组内总行数-1)，

返回值范围为[0,1]。

select UserName,City,Age,Salary,
PERCENT_RANK() over(partition by City order by Salary) '百分比排名'
from Temp_Test

CUME_DIST

计算某个值在一组值内的累积分布。

CUME_DIST计算的逻辑是：

小于等于当前值的行数/分组内总行数，

返回值范围为(0,1]。

比如现在想分析某个人薪资在本部门处于一个什么样的水平，比他薪资高的人占多少？比他薪资低的又占多少？我们就可以使用CUME_DIST函数实现。

select UserName,City,Age,Salary,
CUME_DIST() over(partition by City order by Salary) 'CUME_DIST'
from Temp_Test

解析：第3行Salary值为3000，BeiJing分区中有3行的值小于等于3000，BeiJing分区的总行数为6，因此CUME_DIST为3/6=0.5

----------------------

欢迎关注“数据分析且徐行”：

数据清洗-数据整理-数据分析-数据挖掘-数据可视化-报告呈现-汇报工作，数据分析这条漫漫长路，我陪您一起慢慢走，纵使道阻且长，也要且行且自在。

适用人员：企业管理者，数据分析师等

识别二维码，关注我吧

数据库

文章转载自数据分析且徐行，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL Sever开窗函数

评论