So verwenden Sie Fensterfunktionen in MySQL

(1) Definition der Fensterfunktion

Die Fensterfunktion wird auch als OLAP-Funktion (Online Analytical Processing, Online Analytical Processing) bezeichnet und wird hauptsächlich zur Analyse und Verarbeitung von Daten in Echtzeit verwendet. Vor MySQL-Version 8.0 wurden Fensterfunktionen nicht unterstützt, seit dieser Version wird jedoch Unterstützung für Fensterfunktionen bereitgestellt.

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

Analyse der Fensterfunktionsanweisung:
Die Funktion ist in zwei Teile unterteilt, ein Teil ist der Funktionsname, die Anzahl der Fensterfunktionen ist relativ gering, es gibt insgesamt nur 11 Fensterfunktionen + Aggregatfunktionen (alle Aggregatfunktionen können sein). wird als Fensterfunktion verwendet). Abhängig von der Art der Funktion müssen einige Parameter schreiben, andere nicht.

Der andere Teil ist die Over-Anweisung, die über () geschrieben werden muss. Die darin enthaltenen Parameter sind alle optional und können je nach Bedarf selektiv verwendet werden:

• Der erste Parameter ist das Feld „Partition nach +“, was bedeutet Dieses Feld unterteilt den Datensatz in mehrere Teile. Der zweite Parameter ist das Feld „Reihenfolge nach +“. Die Daten in jedem Fenster werden entsprechend diesem Feld in aufsteigender oder absteigender Reihenfolge angeordnet. Fensterfunktion und Gruppierung Aggregationsfunktion Sie sind relativ ähnlich und die Daten werden durch Angabe von Feldern in mehrere Teile unterteilt. Der Unterschied besteht darin:

• Der SQL-Standard ermöglicht die Verwendung aller Aggregatfunktionen als Fensterfunktionen, und das Schlüsselwort OVER wird zur Unterscheidung von Fensterfunktionen verwendet und Aggregatfunktionen.

Die Aggregationsfunktion gibt nur einen Wert pro Gruppe zurück, während die Fensterfunktion mehrere Werte pro Gruppe zurückgeben kann.

Unter diesen 11 Fensterfunktionen werden die drei Sortierfunktionen ROW_NUMBER(), RANK() und DENSE_RANK() in der tatsächlichen Arbeit am häufigsten verwendet. Lassen Sie uns diese drei Fensterfunktionen anhand eines einfachen Datensatzes lernen.

# 首先创建虚拟的业务员销售数据 
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(): sequentielle Sortierung – 1, 2, 3
RANK(): parallele Sortierung, wiederholte Seriennummern überspringen – 1, 1, 3

DENSE_RANK(): Parallel Sortieren, ohne wiederholte Seriennummern zu überspringen – 1, 1, 2

(2) Praktische Anwendungsszenarien von Fensterfunktionen

Bei der Arbeit oder bei Vorstellungsgesprächen kann es vorkommen, dass sich Benutzer an aufeinanderfolgenden Tagen oder an mehreren Tagen anmelden müssen . Im Folgenden finden Sie eine Idee zur Verwendung von Fensterfunktionen zur Lösung solcher Probleme.

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

Bei der Berechnung der Anzahl aufeinanderfolgender Anmeldetage gibt es normalerweise drei Situationen:

Sehen Sie sich die kontinuierliche Anmeldesituation jedes Benutzers an.

Zeigen Sie die maximale Anzahl aufeinanderfolgender Anmeldetage für jeden Benutzer an einen bestimmten Zeitraum Benutzer, die sich länger als N Tage ununterbrochen angemeldet haben

Für die erste Situation: Überprüfen Sie die kontinuierliche Anmeldesituation jedes Benutzers

Basierend auf tatsächlichen Erfahrungen wissen wir, dass sich Benutzer innerhalb eines bestimmten Zeitraums möglicherweise anmelden Wir verwenden diese Informationen mehrmals kontinuierlich. Alle Felder müssen ausgegeben werden, sodass die im Endergebnis ausgegebenen Felder Benutzer-ID, Datum der ersten Anmeldung, Datum der Endanmeldung und Anzahl aufeinanderfolgender Anmeldetage sein können.

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

  Für den zweiten Fall: Überprüfen Sie die maximale Anzahl aufeinanderfolgender Anmeldetage für jeden Benutzer

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

Für den dritten Fall: Überprüfen Sie die Benutzer, die sich in einem bestimmten Zeitraum mehr als N Tage angemeldet haben

• Wenn wir das überprüfen müssen die Anzahl der aufeinanderfolgenden Anmeldetage in 10. Wie kann dies für Benutzer implementiert werden, die sich zwischen dem 29. und 25. November an 5 aufeinanderfolgenden Tagen oder mehr angemeldet haben? . Diese Anforderung kann auch im ersten Fall anhand der Ergebnisse der Abfrage gefiltert werden.

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

Diese Schreibweise kann zu Ergebnissen führen, ist jedoch für dieses Problem etwas mühsam. Hier ist eine einfache Methode: Verweisen Sie auf eine neue statische Fensterfunktion „lead()“

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

  Die Lead-Funktion hat drei Parameter Der erste Parameter ist die angegebene Spalte (hier wird das Anmeldedatum verwendet), der zweite Parameter ist der Wert mehrerer Zeilen nach der aktuellen Zeile, hier ist 4, also das Datum der fünften Anmeldung, und der dritte Parameter ist, wenn er zurückgegeben wird, Null Werte können durch angegebene Werte ersetzt werden. Der dritte Parameter wird hier nicht verwendet. In der Over-Klausel werden Fenster nach Benutzer-ID gruppiert und die Daten in jedem Fenster werden in aufsteigender Reihenfolge nach Anmeldedatum angeordnet.

Verwenden Sie das fünfte Anmeldedatum – login_date+1. Wenn es gleich 5 ist, bedeutet dies, dass Sie sich an fünf aufeinanderfolgenden Tagen angemeldet haben. Wenn Sie einen Nullwert oder mehr als 5 erhalten, bedeutet dies, dass Sie sich nicht angemeldet haben für fünf aufeinanderfolgende Tage. Der Code und die Ergebnisse lauten wie folgt:

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

[Übung] Interviewfragen zur Datenanalyse der Meituan-Plattform – SQL
Die vorhandene Transaktionsdatentabelle user_goods_table lautet wie folgt:

Jetzt möchte der Chef es wissen Die Verteilung der von jedem Benutzer gekauften Takeaway-Kategorien und die Ermittlung der von jedem Benutzer am häufigsten gekauften Takeaway-Kategorie.

# 分析题目：要求输出字段为用户名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

Das obige ist der detaillierte Inhalt vonSo verwenden Sie Fensterfunktionen in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!