MySQL でウィンドウ関数を使用する方法-mysql チュートリアル-php.cn

(1) ウィンドウ関数の定義

ホームページ

データベース

mysql チュートリアル

MySQL でウィンドウ関数を使用する方法

May 30, 2023 pm 03:10 PM

mysql

(1) ウィンドウ関数の定義

ウィンドウ関数は、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 番目のパラメータはフィールドによる順序であり、各ウィンドウのデータはこれに基づいていますフィールド昇順または降順に並べる

MySQL でウィンドウ関数を使用する方法

ウィンドウ関数はグループ化集計関数と似ており、どちらもフィールドを指定してデータを複数の部分に分割します。

##SQL 標準では、OVER キーワードを使用してウィンドウ関数と集計関数を区別することにより、すべての集計関数をウィンドウ関数として使用することができます。
集計関数はグループごとに 1 つの値のみを返しますが、ウィンドウ関数はグループごとに複数の値を返すことができます。

これら 11 個のウィンドウ関数の中で、実際の作業で最もよく使用されるのは、ROW_NUMBER()、RANK()、DENSE_RANK() の 3 つの並べ替え関数です。簡単なデータセットを通じてこれら 3 つのウィンドウ関数を学習しましょう。

# 首先创建虚拟的业务员销售数据 
CREATE TABLE Sales
( 
idate date, 
iname char(2), 
sales int
); 
# 向表中插入数据 
INSERT INTO Sales VALUES 
(&#39;2021/1/1&#39;, &#39;丁一&#39;, 200), 
(&#39;2021/2/1&#39;, &#39;丁一&#39;, 180), 
(&#39;2021/2/1&#39;, &#39;李四&#39;, 100), 
(&#39;2021/3/1&#39;, &#39;李四&#39;, 150), 
(&#39;2021/2/1&#39;, &#39;刘猛&#39;, 180), 
(&#39;2021/3/1&#39;, &#39;刘猛&#39;, 150), 
(&#39;2021/1/1&#39;, &#39;王二&#39;, 200), 
(&#39;2021/2/1&#39;, &#39;王二&#39;, 180), 
(&#39;2021/3/1&#39;, &#39;王二&#39;, 300), 
(&#39;2021/1/1&#39;, &#39;张三&#39;, 300), 
(&#39;2021/2/1&#39;, &#39;张三&#39;, 280), 
(&#39;2021/3/1&#39;, &#39;张三&#39;, 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;

ログイン後にコピー

MySQL でウィンドウ関数を使用する方法

# 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;

ログイン後にコピー

MySQL でウィンドウ関数を使用する方法

ROW_NUMBER(): 順次並べ替え——1、2、3

RANK(): 並列並べ替え、繰り返しのシリアル番号をスキップします - 1、1、3
DENSE_RANK(): 並列で並べ替え、繰り返しのシリアル番号をスキップしません - 1、1、2

(2) ウィンドウ関数実際のアプリケーションシナリオ

仕事や面接などで、ユーザーに連続ログイン日数やチェックイン日数を尋ねる必要がある場面に遭遇することがあります。以下に、ウィンドウ関数を使用してこのような問題を解決するアイデアを示します。

# 首先创建虚拟的用户登录表，并插入数据 
create table user_login
( 
user_id varchar(100), 
login_time datetime
); 
 
insert into user_login values 
(1,&#39;2020-11-25 13:21:12&#39;), 
(1,&#39;2020-11-24 13:15:22&#39;), 
(1,&#39;2020-11-24 10:30:15&#39;), 
(1,&#39;2020-11-24 09:18:27&#39;), 
(1,&#39;2020-11-23 07:43:54&#39;), 
(1,&#39;2020-11-10 09:48:36&#39;), 
(1,&#39;2020-11-09 03:30:22&#39;), 
(1,&#39;2020-11-01 15:28:29&#39;), 
(1,&#39;2020-10-31 09:37:45&#39;), 
(2,&#39;2020-11-25 13:54:40&#39;), 
(2,&#39;2020-11-24 13:22:32&#39;), 
(2,&#39;2020-11-23 10:55:52&#39;), 
(2,&#39;2020-11-22 06:30:09&#39;), 
(2,&#39;2020-11-21 08:33:15&#39;), 
(2,&#39;2020-11-20 05:38:18&#39;), 
(2,&#39;2020-11-19 09:21:42&#39;), 
(2,&#39;2020-11-02 00:19:38&#39;), 
(2,&#39;2020-11-01 09:03:11&#39;), 
(2,&#39;2020-10-31 07:44:55&#39;), 
(2,&#39;2020-10-30 08:56:33&#39;), 
(2,&#39;2020-10-29 09:30:28&#39;); 
# 查看数据 
SELECT * FROM user_login;

ログイン後にコピー

連続ログイン日数の計算には通常、次の 3 つの状況があります。

各ユーザーの継続ログインステータスの表示
ユーザーごとの連続ログイン最大日数を表示
一定期間内にN日以上ログインしたユーザーを表示

最初の状況: 各ユーザーの連続ログイン状況を確認する

実際の経験に基づいて、一定期間内にユーザーが複数の連続ログインを行う可能性があることがわかっています。この情報を出力する必要があるため、フィールド最終結果出力には、ユーザー 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;

ログイン後にコピー

2 番目のケース: 各ユーザーの連続ログイン最大日数を確認する

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

ログイン後にコピー

3 番目のケース: 特定の期間に N 日を超えてログインしているユーザーを確認する期間

10 月 29 日から 11 月 25 日までに 5 日以上連続してログインしたユーザーを表示する必要がある場合、どうすればこれを実現できますか? 。この要件は、最初のケースのクエリの結果を使用してフィルタリングすることもできます。

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

ログイン後にコピー

この書き方でも結果は得られますが、この問題では少し面倒ですので、簡単な方法を紹介します: 新しい静的ウィンドウ関数 lead()を参照してください

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

ログイン後にコピー

リードこの関数には 3 つのパラメータがあります。最初のパラメータは指定された列 (ここではログイン日が使用されます)、2 番目のパラメータは現在の行から数行後の値です。ここでは、5 回目のログイン日である 4 が使用されています。 3 番目のパラメーターは、返された null 値を指定された値に置き換えることができる場合、ここでは 3 番目のパラメーターは使用されません。 over 句では、ウィンドウが user_id ごとにグループ化され、各ウィンドウ内のデータがログイン日の昇順に並べられます。

5 番目のログイン日 (login_date 1) を使用します。5 に等しい場合は、5 日間連続してログインしたことを意味します。null 値または 5 より大きい値を取得した場合は、ログインしたことを意味します。

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