SQL补充：窗口函数

SQL窗口函数

结合order by关键词和limit关键词是可以解决很多的topN问题，比如从二手房数据集中查询出某个地区的最贵的10套房，从电商交易数据集中查询出实付金额最高的5笔交易，从学员信息表中查询出年龄最小的3个学员等。

但是，如果需求变成从二手房数据集中查询出各个地区最贵的10套房，从电商数据集中查询出每月实付金额最高的5笔交易，从学员信息表中查询出各个科系下年龄最小的3个学员，该如何解决呢？

其实这类问题的核心就是，筛选出组内的topN，而不是从全部数据集中挑选出topN。遇到这种既需要分组也需要排序的问题，直接上开窗函数就能解决了。

窗口函数适用背景

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励

什么是窗口函数？

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

窗口函数的基本语法

func_name(<parameter>) OVER([PARTITION BY <part_by_condition>]
						[ORDER BY <order_by_list> ASC|DESC])

语法解析：

• 函数名称（func_name），开窗函数的数量比较少，总共才11个开窗函数+聚合函数（所有的聚合函数都可以用作开窗函数）。根据函数的性质，有的需要写参数，有的不需要写参数
• over语句，over()是必须要写的，里面的参数都是非必须参数，可以根据需求有选择地使用
  • 第一个参数是partition by + 字段，含义是根据此字段将数据集分为多组
  • 第二个参数是order by + 字段，每个窗口的数据依据此字段进行升序或降序排列

<窗口函数>种类

<窗口函数>的位置，可以放以下两种函数：

1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2） 聚合函数，如sum. avg, count, max, min等

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

在上图这11个开窗函数中，实际工作中用的最多的当属**ROW_NUMBER()、RANK()、DENSE_RANK()**这三个排序函数了。

开窗函数 VS 聚合函数

开窗函数与分组聚合函数比较相似，都是通过指定字段将数据分成多份，区别在于：

• SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。
• 聚合函数每组只返回一个值，开窗函数每组可返回多个值

常用的窗口函数

专用窗口函数rank

要求：

在每个班级内按成绩排名

于是，得到下面的结果

select *,
   rank() over (partition by 班级
                 order by 成绩 desc) as ranking
from 班级表

代码解析：（上面代码可以分为两部分）

• 每个班级内：按班级分组

partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）

• 按成绩排名

order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。

通过下图，我们就可以理解partiition by（分组）和order by（在组内排序）的作用：

若省略了partition子句，则为

select *,
   rank() over (order by 成绩 desc) as ranking
from 班级表

注意：

上面代码就失去了窗口函数的功能，所以一般不要这么使用

思考：既然窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？

这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。

总结

窗口函数的功能：

1）同时具有分组和排序的功能

2）不减少原表的行数

3）语法如下：

func_name(<parameter>) OVER([PARTITION BY <part_by_condition>]
						[ORDER BY <order_by_list> ASC|DESC])

其他专业窗口函数

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表

代码解析：

• rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。
• dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。
• row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

注意：

在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。

练习

准备数据集

# 首先创建虚拟的业务员销售数据
CREATE TABLE Sales(
idate date,
iname char(2),
sales int);

# 向表中插入数据
INSERT INTO Sales VALUES
('2021/1/1', '丁一', 200),
('2021/2/1', '丁一', 180),
('2021/2/1', '李四', 100),
('2021/3/1', '李四', 150),
('2021/2/1', '刘猛', 180),
('2021/3/1', '刘猛', 150),
('2021/1/1', '王二', 200),
('2021/2/1', '王二', 180),
('2021/3/1', '王二', 300),
('2021/1/1', '张三', 300),
('2021/2/1', '张三', 280),
('2021/3/1', '张三', 280);

# 数据查询
SELECT * FROM Sales;

查询各月中销售业绩最差的业务员

SELECT *
FROM (SELECT month(idate),iname,sales,
		ROW_NUMBER() OVER(PARTITION BY month(idate) ORDER BY sales) as
		sales_order
	FROM Sales) as t
WHERE sales_order=1;

代码解析：

• 子查询：将Sales表根据月份作为组进行分组，对每组的销量进行升序排列，最后返回各组销量最差的记录（包括月份呢、名字、销量、排序）
• 从 子查询范围的虚拟表中返回排序=1的各组记录

聚合函数作为窗口函数

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

select *,
   sum(成绩) over (order by 学号) as current_sum,
   avg(成绩) over (order by 学号) as current_avg,
   count(成绩) over (order by 学号) as current_count,
   max(成绩) over (order by 学号) as current_max,
   min(成绩) over (order by 学号) as current_min
from 班级表

结果导读：

聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果：

• 比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和；
• 若是0005号，则结果是0001号~0005号成绩的求和，以此类推。

不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算。

作用

聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

开窗函数的应用场景

求用户连续登录天数、连续签到天数等

计算连续登录天数通常会有以下三种情况：

• 查看每位用户连续登录的情况
• 查看每位用户最大连续登录的天数
• 查看在某个时间段里连续登录天数超过N天的用户

数据准备

# 首先创建虚拟的用户登录表，并插入数据
create table user_login(
user_id varchar(100),
login_time datetime);

insert into user_login values
(1,'2020-11-25 13:21:12'),
(1,'2020-11-24 13:15:22'),
(1,'2020-11-24 10:30:15'),
(1,'2020-11-24 09:18:27'),
(1,'2020-11-23 07:43:54'),
(1,'2020-11-10 09:48:36'),
(1,'2020-11-09 03:30:22'),
(1,'2020-11-01 15:28:29'),
(1,'2020-10-31 09:37:45'),
(2,'2020-11-25 13:54:40'),
(2,'2020-11-24 13:22:32'),
(2,'2020-11-23 10:55:52'),
(2,'2020-11-22 06:30:09'),
(2,'2020-11-21 08:33:15'),
(2,'2020-11-20 05:38:18'),
(2,'2020-11-19 09:21:42'),
(2,'2020-11-02 00:19:38'),
(2,'2020-11-01 09:03:11'),
(2,'2020-10-31 07:44:55'),
(2,'2020-10-30 08:56:33'),
(2,'2020-10-29 09:30:28');

# 查看数据
SELECT * FROM user_login;

针对第一种情况：查看每位用户连续登录的情况

根据实际经验，我们知道在一段时间内，用户可能出现多次连续登录，这些信息我们都要输出，所以最后结果输出的字段可以是用户ID、首次登录日期、结束登录日期、连续登录天数这四个

# 数据预处理：由于统计的窗口期是天数，所以可以对登录时间字段进行格式转换，将其变成日期格式然后再去重（去掉用户同一天内多次登录的情况）
# 为方便后续代码查看，将处理结果放置新表中，一步一步操作
create table user_login_date(select distinct user_id, date(login_time) login_date from user_login);
# 处理后的数据如下：
select * from user_login_date;

# 第一种情况：查看每位用户连续登陆的情况
# 对用户登录数据进行排序
create table user_login_date_1(
select *,rank() over(partition by user_id order by login_date) as irank from user_login_date);
-- 在原始数据集的基础上增加了每组（根据究user_id进行分组)的内部排序

#查看结果
select * from user_login_date_1;

# 增加辅助列，观察每个用户的user_login_date_1的值是否一样，帮助判断用户是否连续登录
create table user_login_date_2(
	select *,date_sub(login_date, interval irank day) as idate -- irank为每组内容排序
	from user_login_date_1);
# 查看结果
select * from user_login_date_2;

# 计算每位用户连续登录天数
select user_id,
	min(login_date) as start_date,
	max(login_date) as end_date,
	count(login_date) as days
from user_login_date_2
group by user_id,idate;

# ===============【整合代码，解决用户连续登录问题】===================
select user_id,
	min(login_date) as start_date,
	max(login_date) as end_date,
	count(login_date) as days
	from (select *,date_sub(login_date, interval irank day) as idate
		from (select *,rank() over(partition by user_id order by login_date) as irank
			from (select distinct user_id, date(login_time) as login_date
				from user_login) as a) 
          as b) 
      as c
group by user_id,idate;
-- 根据用户id和是否连续登录字段来进行排序

针对第二种情况：查看每位用户最大连续登录的天数

只需要在第一种情况的基础上，对用户进行分组，然后提取每组的用户ID和最大连续登录天数即可

# 计算每个用户最大连续登录天数
select user_id, max(days) from(
	select user_id,
		min(login_date) as start_date,
		max(login_date) as end_date,
		count(login_date) as days
		from (select *,date_sub(login_date, interval irank day) as idate
			from (select *,rank() over(partition by user_id order by login_date) as irank
				from (select distinct user_id, date(login_time) as login_date
					from user_login) as a) 
			  as b) 
		  as c
	group by user_id,idate)as d
group by user_id;

针对第三种情况：查看在某个时间段里连续登录天数超过N天的用户

假如说，我们的需求是查看10/29-11/25在这段时间内连续登录天数≥5天的用户。这个需求也可以用第一种情况查询的结果进行筛选

• 法一

select distinct user_id from(
	select user_id,
		min(login_date) as start_date,
		max(login_date) as end_date,
		count(login_date) as days
		from (select *,date_sub(login_date, interval irank day) as idate
			from (select *,rank() over(partition by user_id order by login_date) as irank
				from (select distinct user_id, date(login_time) as login_date
					from user_login) as a) 
			  as b where login_date between '2020/10/29' and '2020/11/25') 
		  as c
	group by user_id,idate
	having days >= 5) as d;

• 法二

引用一个新的静态窗口函数lead()

-- 局部代码
-- 计算每个用户第5次登录日期与当天的差值
select *,
lead(login_date,4) over(partition by user_id order by login_date) as idate5
from user_login_date;

代码解读：

1. lead函数有三个参数:

   • 第一个参数是指定的列（这里用登陆日期）
   • 第二个参数是当前行向后几行的值，这里用的是4，也就是第五次登录的日期
   • 第三个参数是如果返回的空值可以用指定值替代，这里没有使用第三个参数。

2. over语句里面是针对user_id分窗，每个窗口针对登录日期升序。

   用第五次登录日期 - login_date+1，如果等于5，说明是连续登录五天的，如果得到空值或者大于5，说明没有连续登录五天

-- 计算第5次登录日期与当天的差值
select *, datediff(idate5,login_date) as days
from (
    select *,lead(login_date,4) over(partition by user_id order by login_date) as idate5
	from user_login_date) 
as a;

-- 找出相差天数为5的记录
select distinct user_id
from (
    select *,datediff(idate5,login_date)+1 as days
	from (
        select *,lead(login_date,4) over(partition by user_id order by login_date)  as idate5
		from user_login_date) as a)
	as b
where days = 5;

总结

窗口函数语法

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

<窗口函数>的位置，可以放以下两种函数：

1） 专用窗口函数，比如rank, dense_rank, row_number等

2） 聚合函数，如sum. avg, count, max, min等

窗口函数的功能

1）同时具有分组（partition by）和排序（order by）的功能

2）不减少原表的行数，所以经常用来在每组内排名

注意事项

窗口函数原则上只能写在select子句中

窗口函数使用场景

业务需求“在每组内排名”，比如：

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励