How to use windowing functions in MySQL

(1) Definition of windowing function

Windowing function is also called OLAP function (Online Analytical Processing, online analytical processing), which is mainly used to analyze and process data in real time. Before MySQL version 8.0, windowing functions were not supported, but support for windowing functions has been provided since this version.

# 开窗函数语法 
func_name(<parameter>) 
OVER([PARTITION BY <part_by_condition>] 
[ORDER BY <order_by_list> ASC|DESC])

Window function statement analysis:
The function is divided into two parts, one part is the function name, the number of window functions is relatively small, there are only 11 window function aggregate functions in total (all aggregate functions are Can be used as a windowing function). Depending on the nature of the function, some need to write parameters and some do not.

The other part is the over statement. over() must be written. The parameters inside are all optional and can be used selectively according to needs:

• Chapter One parameter is the partition by field, which means dividing the data set into multiple parts based on this field

• The second parameter is the order by field, the data of each window is based on this field Arrange in ascending or descending order

The windowing function is similar to the grouping aggregation function. They both divide the data into multiple parts by specifying fields. The difference is:

• The SQL standard allows all aggregate functions to be used as window functions, using the OVER keyword to distinguish window functions from aggregate functions.

• Aggregation functions only return one value per group, while window functions can return multiple values ​​per group.

Among these 11 windowing functions, the three sorting functions ROW_NUMBER(), RANK(), and DENSE_RANK() are most commonly used in actual work. Let's learn these three windowing functions through a simple data set.

# 首先创建虚拟的业务员销售数据 
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 month(idate),iname,sales, 
	ROW_NUMBER() 
	OVER(PARTITION BY month(idate) 
			 ORDER BY sales) as sales_order 
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;

# ROW_NUMBER()、RANK()、DENSE_RANK()的区别 
SELECT * FROM 
(SELECT month(idate) as imonth,iname,sales, 
ROW_NUMBER() 
OVER(PARTITION BY month(idate) ORDER BY sales) as row_order,
RANK() 
OVER(PARTITION BY month(idate) ORDER BY sales) as rank_order, 
DENSE_RANK() 
OVER(PARTITION BY month(idate) ORDER BY sales) as dense_order 
FROM Sales) as t;

ROW_NUMBER(): Sequential sorting——1, 2, 3
RANK(): Parallel sorting , skip repeated serial numbers - 1, 1, 3
DENSE_RANK(): sort in parallel, do not skip repeated serial numbers - 1, 1, 2

(2) Window function Practical application scenarios

In work or interviews, you may encounter situations where you need to ask users for the number of consecutive login days or check-in days. The following provides an idea for using windowing functions to solve such problems.

# 首先创建虚拟的用户登录表，并插入数据 
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;

There are usually three situations in calculating the number of consecutive login days:

• View the continuous login status of each user

• View the maximum number of consecutive login days for each user

• View users who have logged in for more than N days in a certain period

For The first situation: Check the continuous login situation of each user
Based on actual experience, we know that within a period of time, users may have multiple consecutive logins. We have to output this information, so the fields of the final result output can be User ID, first login date, end login date, and number of consecutive login days.

# 数据预处理：由于统计的窗口期是天数，所以可以对登录时间字段进行格式转换，将其变成日期格式然后再去重（去掉用户同一天内多次登录的情况） 
# 为方便后续代码查看，将处理结果放置新表中，一步一步操作 
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) irank 
from user_login_date); 
#查看结果 
select * from user_login_date_1;
 
# 增加辅助列，帮助判断用户是否连续登录 
create table user_login_date_2( 
select *,
date_sub(login_date, interval irank DAY) idate  #data_sub从指定的日期减去指定的时间间隔
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) start_date, 
       max(login_date) end_date, 
       count(login_date) days 
from (select *,date_sub(login_date, interval irank day) idate 
from (select *,rank() over(partition by user_id order by login_date) irank 
from (select distinct user_id, date(login_time) login_date from user_login) as a) as b) as c 
group by user_id,idate;

For the second case: Check the maximum number of consecutive login days for each user

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

For the third case: Check for users who have logged in for more than N days in a certain period of time

If we need to view users who logged in for 5 or more consecutive days between October 29th and November 25th, how to achieve this? . This requirement can also be filtered using the results of the query in the first case.

# 查看在这段时间内连续登录天数≥5天的用户 
select distinct user_id from 
(select user_id, 
		min(login_date) start_date, 
		max(login_date) end_date, 
		count(login_date) days 
from (select *,date_sub(login_date, interval irank day) idate 
from (select *,rank() over(partition by user_id order by login_date) irank 
from (select distinct user_id, date(login_time) login_date from user_login) as a) as b) as c 
group by user_id,idate 
having days>=5
) as d;

This way of writing can get results, but it is a bit troublesome for this problem. Here is a simple method: refer to a new static window function lead()

select *, 
lead(login_date,4) over(partition by user_id order by login_date) as idate5 
from user_login_date;

The lead function has three parameters. The first parameter is the specified column (the login date is used here), and the second parameter is the value of several rows after the current row. Here, 4 is used, which is the date of the fifth login. The third parameter is if the returned null value can be replaced with the specified value, the third parameter is not used here. In the over clause, windows are grouped by user_id, and the data in each window is arranged in ascending order by login date.

Use the fifth login date - login_date 1. If it is equal to 5, it means that you have logged in for five consecutive days. If you get a null value or greater than 5, it means that you have not logged in for five consecutive days. The code and results are as follows:

# 计算第5次登录日期与当天的差值 
select *,datediff(idate5,login_date)+1 days 
from (select *,lead(login_date,4) over(partition by user_id order by login_date) 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) idate5 
from user_logrin_date) as a)as b 
where days = 5;

[Exercise] Meituan takeout platform data analysis interview questions--SQL
The existing transaction data table user_goods_table is as follows:

Now the boss wants to Know the preference distribution of takeaway categories purchased by each user, and find out which takeaway category each user purchases the most.

# 分析题目：要求输出字段为用户名user_name，该用户购买最多的外卖品类goods_kind 
# 解题思路：这是一个分组排序的问题，可以考虑窗口函数 
# 第一步：使用窗口函数row_number()，对每个用户购买的外卖品类进行分组统计与排名
select user_name,goods_kind,count(goods_kind),
rank() over (partition by user_name order by count(goods_kind) desc) as irank
from user_goods_table
group by user_name,goods_kind;

# 第二步：筛选出每个用户排名第一的外卖品类
select user_id,goods_kind from 
(select user_name,goods_kind,count(goods_kind),
rank() over (partition by user_name order by count(goods_kind) desc) as irank
from user_goods_table
group by user_name,goods_kind) as a 
where irank=1

The above is the detailed content of How to use windowing functions in MySQL. For more information, please follow other related articles on the PHP Chinese website!