【MySQL】ウィンドウ関数を使ってログ解析を捗らせる

  • 2023年7月3日
  • SQL

　この記事はMySQL 8.0.30環境で試しながら作りました。

　MySQLにはウィンドウ関数という関数があります。これは結果の前後を参照したり結果の範囲を区切った上で様々な操作を行える関数です。ログを解析する際などにウィンドウ関数を用いると便利です。

MySQL ：： MySQL 8.0 リファレンスマニュアル ：： 12.21.2 Window 関数の概念と構文
MySQL ：： MySQL 8.0 リファレンスマニュアル ：： 12.21.1 Window 関数の説明

　ウィンドウ関数は OVER (PARTITION BY [区切り条件] ORDER BY [並び替え条件]) という構文で範囲（パーティション）を定義し、範囲内のデータを考慮して計算を行えます。例えば次の使用例ではユーザーごとに範囲を作り、その中での順番を得ています。

テーブル定義 
CREATE TABLE user_logs
(
    log_id     BIGINT UNSIGNED AUTO_INCREMENT
        PRIMARY KEY,
    user_id    BIGINT UNSIGNED                                                 NOT NULL,
    created_at DATETIME                                                        NOT NULL,
    action     ENUM ('LOGIN', 'LOGOUT','FORM_INIT','FORM_CONFIRM','FORM_SENT') NOT NULL
) COMMENT 'ユーザーログ';
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (1, 1, '2023-07-03 09:15:30', 'FORM_INIT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (2, 2, '2023-07-03 09:01:30', 'LOGIN');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (3, 3, '2023-07-03 09:15:30', 'FORM_CONFIRM');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (4, 3, '2023-07-03 09:27:31', 'FORM_SENT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (5, 2, '2023-07-03 09:43:31', 'LOGOUT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (6, 3, '2023-07-03 09:36:31', 'FORM_INIT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (7, 1, '2023-07-03 09:27:31', 'LOGOUT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (8, 1, '2023-07-03 09:43:31', 'FORM_INIT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (9, 2, '2023-07-03 09:12:31', 'LOGOUT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (10, 1, '2023-07-03 09:28:31', 'FORM_INIT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (11, 2, '2023-07-03 09:11:31', 'LOGIN');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (12, 1, '2023-07-03 09:15:31', 'FORM_CONFIRM');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (13, 3, '2023-07-03 09:20:31', 'FORM_INIT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (14, 3, '2023-07-03 09:12:31', 'FORM_INIT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (15, 4, '2023-07-03 09:36:31', 'LOGIN');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (16, 3, '2023-07-03 09:10:31', 'LOGIN');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (17, 1, '2023-07-03 09:35:31', 'FORM_INIT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (18, 3, '2023-07-03 09:39:31', 'FORM_INIT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (19, 1, '2023-07-03 09:03:31', 'LOGOUT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (20, 1, '2023-07-03 09:02:31', 'FORM_INIT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (21, 2, '2023-07-03 09:14:31', 'FORM_INIT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (22, 2, '2023-07-03 09:07:31', 'LOGOUT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (23, 4, '2023-07-03 09:45:31', 'FORM_CONFIRM');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (24, 3, '2023-07-03 09:24:31', 'FORM_CONFIRM');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (25, 4, '2023-07-03 09:47:31', 'FORM_SENT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (26, 3, '2023-07-03 09:47:31', 'FORM_CONFIRM');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (27, 4, '2023-07-03 09:43:31', 'FORM_INIT');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (28, 2, '2023-07-03 09:13:31', 'LOGIN');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (29, 1, '2023-07-03 08:54:31', 'LOGIN');
INSERT INTO user_logs (log_id, user_id, created_at, action) VALUES (30, 1, '2023-07-03 09:12:31', 'LOGIN');

SELECT
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS `seq`,
    user_id,
    created_at,
    action
FROM user_logs
ORDER BY user_id, created_at;

seq	user_id	created_at	action
1	1	2023-07-03 08:54:31	LOGIN
2	1	2023-07-03 09:02:31	FORM_INIT
3	1	2023-07-03 09:03:31	LOGOUT
# 以下省略

　例にもある様に自分がログを扱う際は次の様な範囲を作ることが多いです。

OVER (PARTITION BY user_id ORDER BY created_at )

　これはユーザー単位でログを時系列順でまとめて扱う、という範囲の定義です。システムややりたいことによってユーザー以外の単位でまとめたり、ログ作成日時以外の日時で時系列を構築したりすることも多いです。

　自分がよく使うウィンドウ関数を使った列の例をいくつか紹介します。

　シーケンス番号を割り振る例です。ウィンドウ関数で範囲を作ったとき、どこからどこまでがその範囲なのか、あるレコードは範囲の中の何番目のデータなのかをわかりやすくしたくなります。これは範囲内のデータの単位で採番することによってそれをわかりやすくするウィンドウ関数の使い方です。

-- ユーザーID (user_id) ごとにログの作成日時 (created_at) 順でシーケンス番号 (seq) を割り振ります。
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS `seq`,
-- 使用例
SELECT
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS `seq`,
    user_logs.*
FROM user_logs;

　このシーケンス番号の割り振りを利用してウィンドウの範囲を人の目でわかりやすくすることもできます。手元でSQLを直に実行して確認する時に便利です。

-- シーケンス番号を割り振り、それが最初の行（つまり1）であれば1を、そうでなければNULLを返す。
IF((ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at)) = 1, 1, null) AS `is_start`,

-- 使用例
SELECT
    IF((ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at)) = 1, 1, null) AS `is_start`,
    user_logs.*
FROM user_logs;

　前後のログが条件に当てはまるか否かを調べる例です。一連の手順を行っているのかを調べたりする時に使ったりします。

-- 前の行のアクションが 'LOGIN' だった場合は 1、そうでない場合は 0 を返します。
IF(LAG(action) OVER (PARTITION BY user_id ORDER BY created_at) = 'LOGIN', 1, 0) AS pre_is_login,

-- 次の行のアクションが 'LOGOUT' だった場合は 1、そうでない場合は 0 を返します。
IF(LEAD(action) OVER (PARTITION BY user_id ORDER BY created_at) = 'LOGOUT', 1, 0) AS next_is_logout,

-- 現在のアクションが 'FORM_INIT' で、次のアクションが 'FORM_CONFIRM' で、さらに次のアクションが 'FORM_SENT' だった場合は 1、それ以外の場合は 0 を返します。
-- ユーザーがフォームの一連の流れを一直線にたどって完了したかどうかを示します。
IF(
    action = 'FORM_INIT'
    AND LEAD(action) OVER (PARTITION BY user_id ORDER BY created_at) = 'FORM_CONFIRM'
    AND LEAD(action, 2) OVER (PARTITION BY user_id ORDER BY created_at) = 'FORM_SENT'
    ,  1, 0
) AS 'form_straight_complete_flow',

-- 使用例
SELECT
    IF(LAG(action) OVER (PARTITION BY user_id ORDER BY created_at) = 'LOGIN', 1, 0) AS pre_is_login,
    IF(LEAD(action) OVER (PARTITION BY user_id ORDER BY created_at) = 'LOGOUT', 1, 0) AS next_is_logout,
    IF(
        action = 'FORM_INIT'
        AND LEAD(action) OVER (PARTITION BY user_id ORDER BY created_at) = 'FORM_CONFIRM'
        AND LEAD(action, 2) OVER (PARTITION BY user_id ORDER BY created_at) = 'FORM_SENT'
        ,  1, 0
    ) AS 'form_straight_complete_flow',
    user_logs.*
FROM user_logs;

　LAGは現在の行よりも前の行を参照するウィンドウ関数です。LEADは現在の行よりも後の行を参照します。これらの関数を使って他の行を判定の対象にすることが可能です。
MySQL ：： MySQL 8.0 リファレンスマニュアル ：： 12.21.1 Window 関数の説明#function_lag
MySQL ：： MySQL 8.0 リファレンスマニュアル ：： 12.21.1 Window 関数の説明#function_lead

　前のログからの時間経過、ウィンドウの開始からの時間経過、ウィンドウ内の特定の時点からの時間経過です。ウィンドウ関数にはいくつもの前の行を参照する方法があり、これを駆使することで現在のログエントリがそれより前のログエントリからどれくらい時間が経過したかを求めることが可能です。

-- 前のログの作成日時から現在のログの作成日時までの秒数を計算します。
TIMESTAMPDIFF(SECOND, LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at), created_at) AS diff_sec_from_pre,

-- 最初のログの作成日時から現在のログの作成日時までの秒数を計算します。
TIMESTAMPDIFF(SECOND, FIRST_VALUE(created_at) OVER (PARTITION BY user_id ORDER BY created_at), created_at) AS diff_sec_from_start,

-- 最初に 'FORM_INIT' というアクションが発生したログの作成日時から現在のログの作成日時までの秒数を計算します。
-- 'FORM_INIT' というアクションがまだ発生していない場合は、NULLが返ります。
TIMESTAMPDIFF(SECOND, MIN(IF(action = 'FORM_INIT', created_at, null)) OVER (PARTITION BY user_id ORDER BY created_at), created_at) AS diff_sec_from_form_start,

-- 使用例
SELECT
    TIMESTAMPDIFF(SECOND, LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at), created_at) AS diff_sec_from_pre,
    TIMESTAMPDIFF(SECOND, FIRST_VALUE(created_at) OVER (PARTITION BY user_id ORDER BY created_at), created_at) AS diff_sec_from_start,
    TIMESTAMPDIFF(SECOND, MIN(IF(action = 'FORM_INIT', created_at, null)) OVER (PARTITION BY user_id ORDER BY created_at), created_at) AS diff_sec_from_form_start,
    user_logs.*
FROM user_logs;

　これらを組み合わせて使った例とデモが次です。

SELECT
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS `seq`,
    user_id,
    IF((ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at)) = 1, 1, NULL) AS `is_start`,
    created_at,
    action,
    IF(LAG(action) OVER (PARTITION BY user_id ORDER BY created_at) = 'LOGIN', 1, 0) AS pre_is_login,
    IF(LEAD(action) OVER (PARTITION BY user_id ORDER BY created_at) = 'LOGOUT', 1, 0) AS next_is_logout,
    IF(
        action = 'FORM_INIT'
        AND LEAD(action) OVER (PARTITION BY user_id ORDER BY created_at) = 'FORM_CONFIRM'
        AND LEAD(action, 2) OVER (PARTITION BY user_id ORDER BY created_at) = 'FORM_SENT'
    ,  1, 0
    ) AS 'form_straight_complete_flow',
    TIMESTAMPDIFF(SECOND, LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at), created_at) AS diff_sec_from_pre,
    TIMESTAMPDIFF(SECOND, FIRST_VALUE(created_at) OVER (PARTITION BY user_id ORDER BY created_at), created_at) AS diff_sec_from_start,
    TIMESTAMPDIFF(SECOND, MIN(IF(action = 'FORM_INIT', created_at, NULL)) OVER (PARTITION BY user_id ORDER BY created_at), created_at) AS diff_sec_from_form_start
FROM user_logs
ORDER BY user_id, created_at
;
実行結果 
seq,user_id,is_start,created_at,action,pre_is_login,next_is_logout,form_straight_complete_flow,diff_sec_from_pre,diff_sec_from_start,diff_sec_from_form_start
1,1,1,2023-07-03 08:54:31,LOGIN,0,0,0,,0,
2,1,,2023-07-03 09:02:31,FORM_INIT,1,1,0,480,480,0
3,1,,2023-07-03 09:03:31,LOGOUT,0,0,0,60,540,60
4,1,,2023-07-03 09:12:31,LOGIN,0,0,0,540,1080,600
5,1,,2023-07-03 09:15:30,FORM_INIT,1,0,0,179,1259,779
6,1,,2023-07-03 09:15:31,FORM_CONFIRM,0,1,0,1,1260,780
7,1,,2023-07-03 09:27:31,LOGOUT,0,0,0,720,1980,1500
8,1,,2023-07-03 09:28:31,FORM_INIT,0,0,0,60,2040,1560
9,1,,2023-07-03 09:35:31,FORM_INIT,0,0,0,420,2460,1980
10,1,,2023-07-03 09:43:31,FORM_INIT,0,0,0,480,2940,2460
1,2,1,2023-07-03 09:01:30,LOGIN,0,1,0,,0,
2,2,,2023-07-03 09:07:31,LOGOUT,1,0,0,361,361,
3,2,,2023-07-03 09:11:31,LOGIN,0,1,0,240,601,
4,2,,2023-07-03 09:12:31,LOGOUT,1,0,0,60,661,
5,2,,2023-07-03 09:13:31,LOGIN,0,0,0,60,721,
6,2,,2023-07-03 09:14:31,FORM_INIT,1,1,0,60,781,0
7,2,,2023-07-03 09:43:31,LOGOUT,0,0,0,1740,2521,1740
1,3,1,2023-07-03 09:10:31,LOGIN,0,0,0,,0,
2,3,,2023-07-03 09:12:31,FORM_INIT,1,0,0,120,120,0
3,3,,2023-07-03 09:15:30,FORM_CONFIRM,0,0,0,179,299,179
4,3,,2023-07-03 09:20:31,FORM_INIT,0,0,1,301,600,480
5,3,,2023-07-03 09:24:31,FORM_CONFIRM,0,0,0,240,840,720
6,3,,2023-07-03 09:27:31,FORM_SENT,0,0,0,180,1020,900
7,3,,2023-07-03 09:36:31,FORM_INIT,0,0,0,540,1560,1440
8,3,,2023-07-03 09:39:31,FORM_INIT,0,0,0,180,1740,1620
9,3,,2023-07-03 09:47:31,FORM_CONFIRM,0,0,0,480,2220,2100
1,4,1,2023-07-03 09:36:31,LOGIN,0,0,0,,0,
2,4,,2023-07-03 09:43:31,FORM_INIT,1,0,1,420,420,0
3,4,,2023-07-03 09:45:31,FORM_CONFIRM,0,0,0,120,540,120
4,4,,2023-07-03 09:47:31,FORM_SENT,0,0,0,120,660,240

>株式会社シーポイントラボ

株式会社シーポイントラボ

TEL：053-543-9889
営業時間：9：00～18：00（月〜金）
住所：〒432-8003
　　　静岡県浜松市中央区和地山3-1-7
　　　浜松イノベーションキューブ 315
※ご来社の際はインターホンで「316」をお呼びください

access
CTR IMG