Cara menggunakan fungsi windowing dalam MySQL

(1) Definisi fungsi tetingkap

Fungsi tetingkap juga dipanggil fungsi OLAP (Pemprosesan Analitik Dalam Talian, pemprosesan analitik dalam talian), yang digunakan terutamanya untuk menganalisis dan memproses data dalam masa nyata. Sebelum MySQL versi 8.0, fungsi windowing tidak disokong, tetapi sokongan untuk fungsi windowing telah disediakan sejak versi ini.

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

Analisis penyataan fungsi tetingkap:
Fungsi terbahagi kepada dua bahagian Satu bahagian ialah nama fungsi Bilangan fungsi tetingkap adalah sedikit sahaja (semua fungsi agregat boleh digunakan sebagai fungsi tetingkap). Bergantung pada sifat fungsi, ada yang perlu menulis parameter dan ada yang tidak.

Bahagian lain ialah pernyataan over() mesti ditulis Parameter di dalamnya adalah pilihan dan boleh digunakan secara terpilih mengikut keperluan:

• Bab Satu. parameter ialah partition by + field, yang bermaksud membahagikan set data kepada beberapa bahagian berdasarkan medan ini

• Parameter kedua ialah medan tertib mengikut +, data setiap tetingkap adalah berdasarkan pada ini Medan disusun dalam tertib menaik atau menurun

Fungsi tetingkap adalah serupa dengan mengelompokkan fungsi pengagregatan. Kedua-duanya membahagikan data kepada beberapa bahagian dengan menentukan medan. Perbezaannya ialah :

• Piawaian SQL membenarkan semua fungsi agregat digunakan sebagai fungsi tetingkap, dan kata kunci OVER digunakan untuk membezakan fungsi tetingkap daripada fungsi agregat.

• Fungsi pengagregatan hanya mengembalikan satu nilai bagi setiap kumpulan, manakala fungsi tetingkap boleh mengembalikan berbilang nilai setiap kumpulan.

Antara 11 fungsi tetingkap ini, tiga fungsi pengisihan ROW_NUMBER(), RANK(), dan DENSE_RANK() paling banyak digunakan dalam kerja sebenar. Mari kita pelajari ketiga-tiga fungsi windowing ini melalui set data ringkas.

# 首先创建虚拟的业务员销售数据 
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(): isihan berurutan——1, 2, 3
RANK(): isihan selari , langkau nombor siri berulang - 1, 1, 3
DENSE_RANK(): isih selari, jangan langkau nombor siri berulang - 1, 1, 2

(2) Fungsi tetingkap Senario aplikasi praktikal

Di tempat kerja atau dalam temu duga, anda mungkin menghadapi situasi di mana anda perlu meminta pengguna untuk bilangan hari log masuk atau hari daftar masuk berturut-turut. Berikut memberikan idea untuk menggunakan fungsi windowing untuk menyelesaikan masalah tersebut.

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

Biasanya terdapat tiga situasi apabila mengira bilangan hari log masuk berturut-turut:

• Lihat status log masuk berterusan setiap pengguna

• Lihat bilangan maksimum hari log masuk berturut-turut untuk setiap pengguna

• Lihat pengguna yang telah log masuk lebih daripada N hari dalam tempoh masa tertentu

Untuk Situasi pertama: Semak situasi log masuk berterusan setiap pengguna
Berdasarkan pengalaman sebenar, kami tahu bahawa dalam tempoh masa tertentu, pengguna mungkin mempunyai berbilang log masuk berterusan Kami perlu mengeluarkan maklumat ini. jadi medan keluaran hasil akhir boleh menjadi ID Pengguna, tarikh log masuk pertama, tarikh log masuk tamat dan bilangan hari log masuk berturut-turut.

# 数据预处理：由于统计的窗口期是天数，所以可以对登录时间字段进行格式转换，将其变成日期格式然后再去重（去掉用户同一天内多次登录的情况） 
# 为方便后续代码查看，将处理结果放置新表中，一步一步操作 
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;

Untuk kes kedua: semak bilangan maksimum hari log masuk berturut-turut untuk setiap pengguna

# 计算每个用户最大连续登录天数 
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;

Untuk kes ketiga: semak pengguna yang telah log masuk lebih daripada N hari dalam tempoh tertentu tempoh masa

Jika kita perlu melihat pengguna yang log masuk selama 5 atau lebih hari berturut-turut antara 29 Oktober dan 25 November, bagaimana kita boleh mencapai ini? . Keperluan ini juga boleh ditapis menggunakan hasil pertanyaan dalam kes pertama.

# 查看在这段时间内连续登录天数≥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;

Cara penulisan ini boleh mendapatkan hasil, tetapi ia agak menyusahkan untuk masalah ini Berikut adalah kaedah mudah: rujuk kepada lead() fungsi tetingkap statik baharu

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

Petunjuk. fungsi mempunyai tiga parameter Parameter pertama ialah lajur yang ditentukan (tarikh log masuk digunakan di sini), dan parameter kedua ialah nilai beberapa baris selepas baris semasa Di sini, 4 digunakan, iaitu tarikh log masuk kelima . Parameter ketiga ialah jika nilai null yang dikembalikan boleh digantikan dengan nilai yang ditentukan, parameter ketiga tidak digunakan di sini. Dalam klausa atas, tetingkap dikumpulkan mengikut user_id, dan data dalam setiap tetingkap disusun dalam tertib menaik mengikut tarikh log masuk.

Gunakan tarikh log masuk kelima - login_date+1 Jika sama dengan 5, ini bermakna anda telah log masuk selama lima hari berturut-turut Jika anda mendapat nilai nol atau lebih daripada 5, ini bermakna anda belum log masuk selama lima hari berturut-turut. Kod dan keputusannya adalah seperti berikut:

# 计算第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;

[Amalan] Soalan Temuduga Analisis Data Platform Penghantaran Makanan Meituan——SQL
Jadual data transaksi sedia ada user_goods_table adalah seperti berikut:

Sekarang bos saya ingin tahu taburan keutamaan kategori bawa pulang yang dibeli oleh setiap pengguna, dan ketahui kategori bawa pulang yang paling banyak dibeli oleh setiap pengguna.

rreeee

Atas ialah kandungan terperinci Cara menggunakan fungsi windowing dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!