ウィンドウ関数は、OLAP 機能(Online Analytical Processing、オンライン分析処理)とも呼ばれ、主にデータをリアルタイムに分析・処理するために使用されます。 MySQL バージョン 8.0 より前では、ウィンドウ関数はサポートされていませんでしたが、このバージョン以降、ウィンドウ関数のサポートが提供されています。
# 开窗函数语法 func_name(<parameter>) OVER([PARTITION BY <part_by_condition>] [ORDER BY <order_by_list> ASC|DESC])
ウィンドウ関数ステートメントの分析:
関数は 2 つの部分に分かれており、1 つの部分は関数名です。ウィンドウ関数の数は比較的少なく、合計で 11 個のウィンドウ関数集約関数しかありません (すべての集計関数はウィンドウ関数として使用できます)。関数の性質に応じて、パラメーターを記述する必要があるものと、そうでないものがあります。
もう 1 つの部分は over ステートメントです。over() を記述する必要があります。内部のパラメータはすべてオプションであり、必要に応じて選択して使用できます:
第 1 章パラメータはフィールドによるパーティションであり、このフィールドに基づいてデータセットを複数の部分に分割することを意味します
2 番目のパラメータはフィールドによる順序であり、各ウィンドウのデータはこれに基づいていますフィールド 昇順または降順に並べる
ウィンドウ関数はグループ化集計関数と似ており、どちらもフィールドを指定してデータを複数の部分に分割します。
# 首先创建虚拟的业务员销售数据 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;
RANK(): 並列並べ替え、繰り返しのシリアル番号をスキップします - 1、1、3
DENSE_RANK(): 並列で並べ替え、繰り返しのシリアル番号をスキップしません - 1、1、2
# 首先创建虚拟的用户登录表,并插入数据 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) 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;
# 计算每个用户最大连续登录天数 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;
# 查看在这段时间内连续登录天数≥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;
select *, lead(login_date,4) over(partition by user_id order by login_date) as idate5 from user_login_date;
# 计算第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;
既存のトランザクション データ テーブル user_goods_table は次のとおりです:
rree
以上がMySQL でウィンドウ関数を使用する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。