連載：SQLのウィンドウ関数を利用した分析―セッションを利用したアクセスログの分析
はじめに
みなさん、こんにちは! DATUMSTUDIOの宇佐見です。
普段の業務ではデータアナリストとして、他の企業さまから依頼を受けてデータ分析のお手伝いや データ分析基盤の構築をさせていただいております。
SQLを利用した様々な分析に携わることが多いため、今回はそのノウハウを紹介できればと思います。
SQLのケーススタディ紹介
本記事では3回にわけてウィンドウ関数を利用したSQLのケーススタディを紹介いたします。実際のアクセスログを利用して、実践ですぐに活用できることをコンセプトとしています。前提知識としてSQLを利用した基礎的な集計を行えるレベルを想定しています。
第1回目は、「セッションを利用したアクセスログの分析」ということで前回アクセスから5分以内の再訪問ならば同一のセッションでの接続とみなし、そのセッションごとの集計を可能とするクエリを作成します。このクエリを利用することで、ユーザの滞在時間や訪問回数などを把握することができます。
環境とデータソース
実行環境としてAWSのRedshiftを利用します。他の環境であってもウィンドウ関数が利用できれば、多少の書き換えで実行可能です。Redshiftを選定した理由としては以下の点が挙げられます。
- Postgresql準拠のSQLでの問い合わせが可能である
- 一時的に利用でき、他のDWHと比較して安価で容易に利用できる
- Redshiftのドキュメントにおいてウィンドウ関数の説明が充実している
データソースに関してはDATUMSTUDIOのホームページのアクセスログを利用します。アクセスログにおけるIPアドレスをハッシュ化し、それをユーザIDと見なしています。
以下のようなデータ形式となっております。
access_time | user_id ---------------------+---------------------------------- 2017-05-21 03:11:18 | 3f3fcac868404d0ae7e8ad90be6fc4d8 2017-05-21 03:11:21 | 3f3fcac868404d0ae7e8ad90be6fc4d8 2017-05-21 03:11:21 | 3f3fcac868404d0ae7e8ad90be6fc4d8 2017-05-21 03:23:50 | e07e844ba0c20d195f4701fb4f14451e 2017-05-21 03:24:26 | efcdf099ce3f9be9a0f9444f06e2679d 2017-05-21 03:24:27 | efcdf099ce3f9be9a0f9444f06e2679d 2017-05-21 03:24:28 | efcdf099ce3f9be9a0f9444f06e2679d 2017-05-21 03:28:11 | 5cba30f1752bae2a77f05007311b95c3 2017-05-21 03:28:11 | 5cba30f1752bae2a77f05007311b95c3 2017-05-21 03:28:13 | 5cba30f1752bae2a77f05007311b95c3 2017-05-21 03:28:19 | 50dda73283d5a5d3bdc3e01c787a03a0 2017-05-21 03:28:22 | 5da679a7d0ce86eed996bb4c568765f7
ウィンドウ関数の説明
実際のケーススタディの前に簡単にウィンドウ関数の説明をします。
ウィンドウ関数と集約関数が行う処理は非常によく似ています。集約関数はGROUP BY句に指定した項目について集約を行います。集約の結果を指定した項目数分だけに出力されます。それに対してウィンドウ関数はPARTITION BYで指定した項目についてウィンドウを作成し、その中で計算を行います。計算の結果は元の行数分出力されます。
以下の図は集約関数、ウィンドウ関数のそれぞれを適用した例となります。
ウィンドウ関数の多くは以下の構造になっています。
FUNCTION_NAME(expression) OVER ([PARTITION BY expr_list] [ORDER BY order_list [frame_clause] ])
FUNCTION_NAMEに利用するウィンドウ関数を指定します。expressionは集計対象の値を入力し、expr_listでウィンドウの作成範囲を指定します。order_listによってウィンドウの中でソートが行われます。 レコードの順番が影響する関数を利用するならば非常に重要になります。frame_clauseは作成されたウィンドウの範囲を変化させることができます。
セッションを利用したアクセスログの分析
本記事では、簡単なクエリからスタートしその過程を説明しながら、ゴールである「セッションを利用したアクセスログの分析」のクエリを完成させます。実務でクエリを作る際も段階を経ながら、クエリを作成することが多いです。
早速ウィンドウ関数の1つであるLAG関数を利用します。 LAG関数はウィンドウの中で任意のレコード数前の値を取得でき、以下が関数利用のイメージ図となります。
以下のクエリを利用して、user_idごとにaccess_time順に並べた際に1レコード前のaccess_timeの値を取得します。
SELECT user_id, access_time, LAG(access_time) OVER (PARTITION BY user_id ORDER BY access_time) AS prev_access_time FROM datum_access_log ;
user_id | access_time | prev_access_time ----------------------------------+---------------------+--------------------- 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:10 | 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:11 | 2017-05-22 06:17:10 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:11 | 2017-05-22 06:17:11 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:12 | 2017-05-22 06:17:11 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:13 | 2017-05-22 06:17:12 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-25 06:17:22 | 2017-05-22 06:17:13 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-25 06:17:23 | 2017-05-25 06:17:22
続いて前回のアクセス時間(prev_access_time)と今回のアクセス時間(access_time)の差を取得します。RedshiftでTIMESTAMP型の演算をするならば、DATEDIFF関数を用います。前回のクエリでは初回アクセスで、前回のアクセス時間が取れない場合はNULLとなっています。そのため、NVL関数を利用し、前回のアクセス時間がNULLの場合は現在のアクセス時間に置き換えます。
SELECT user_id, access_time, NVL(LAG(access_time) OVER (PARTITION BY user_id ORDER BY access_time), access_time) AS prev_access_time, DATEDIFF(sec, NVL(LAG(access_time) OVER (PARTITION BY user_id ORDER BY access_time), access_time), access_time) AS access_interval FROM datum_access_log ;
user_id | access_time | prev_access_time | access_interval ----------------------------------+---------------------+---------------------+----------------- 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:10 | 2017-05-22 06:17:10 | 0 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:11 | 2017-05-22 06:17:10 | 1 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:11 | 2017-05-22 06:17:11 | 0 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:12 | 2017-05-22 06:17:11 | 1 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:13 | 2017-05-22 06:17:12 | 1 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-25 06:17:22 | 2017-05-22 06:17:13 | 259209 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-25 06:17:23 | 2017-05-25 06:17:22 | 1
更に前回アクセスから5分以内であれば0、5分より長ければ1を返すようにします。 論理型ではなく数値型で扱いたいため、CAST関数を利用しています。
SELECT user_id, access_time, NVL(LAG(access_time) OVER (PARTITION BY user_id ORDER BY access_time), access_time) AS prev_access_time, CAST(DATEDIFF(sec, NVL(LAG(access_time) OVER (PARTITION BY user_id ORDER BY access_time), access_time), access_time) > 5 * 60 AS INT) AS session_flag FROM datum_access_log ;
user_id | access_time | prev_access_time | session_flag ----------------------------------+---------------------+---------------------+-------------- 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:10 | 2017-05-22 06:17:10 | 0 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:11 | 2017-05-22 06:17:10 | 0 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:11 | 2017-05-22 06:17:11 | 0 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:12 | 2017-05-22 06:17:11 | 0 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:13 | 2017-05-22 06:17:12 | 0 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-25 06:17:22 | 2017-05-22 06:17:13 | 1 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-25 06:17:23 | 2017-05-25 06:17:22 | 0
ウィンドウ関数内で、ウィンドウ関数を利用することができない(ネストできない)ため、 WITH句を利用して今までのクエリを副問い合せにまとめておきます。
今度はウィンドウ関数のSUMを利用して、session_flagの数を合計します。frame_clauseにはrows unbounded precedingを指定して、現在のレコード以前のsession_flagを合計の対象とします。すると対象となるユーザが今まで利用したセッションの数を取得することができます。SUM関数を適用する上でaccess_timeが同一の場合はsession_flagが1のものを優先するようにしました。
SUM関数の挙動イメージは以下の図になります。
session_countにuser_idを結合して、ユニークなセッションを作成します。 これで前のレコードから5分より短い場合のアクセスは前と同じセッションが割り振られ、5分以上の場合は新しいセッションが割り振られます。
WITH datum_access_flag AS (
SELECT
user_id,
access_time,
NVL(LAG(access_time) OVER (PARTITION BY user_id ORDER BY access_time), access_time) AS prev_access_time,
CAST(DATEDIFF(sec, NVL(lag(access_time) OVER (PARTITION BY user_id ORDER BY access_time), access_time), access_time) > 5 * 60 AS INT) AS session_flag
FROM
datum_access_log
)
SELECT
*,
user_id || '_' || SUM(session_flag) OVER (PARTITION BY user_id ORDER BY access_time, session_flag DESC rows unbounded preceding) AS user_session
FROM
datum_access_flag
;
user_id | access_time | prev_access_time | session_flag | user_session ----------------------------------+---------------------+---------------------+--------------+------------------------------------ 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:10 | 2017-05-22 06:17:10 | 0 | 046a9d18551a2f8ab4c060c7547d6540_0 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:11 | 2017-05-22 06:17:10 | 0 | 046a9d18551a2f8ab4c060c7547d6540_0 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:11 | 2017-05-22 06:17:11 | 0 | 046a9d18551a2f8ab4c060c7547d6540_0 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:12 | 2017-05-22 06:17:11 | 0 | 046a9d18551a2f8ab4c060c7547d6540_0 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-22 06:17:13 | 2017-05-22 06:17:12 | 0 | 046a9d18551a2f8ab4c060c7547d6540_0 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-25 06:17:22 | 2017-05-22 06:17:13 | 1 | 046a9d18551a2f8ab4c060c7547d6540_1 046a9d18551a2f8ab4c060c7547d6540 | 2017-05-25 06:17:23 | 2017-05-25 06:17:22 | 0 | 046a9d18551a2f8ab4c060c7547d6540_1
最後にuser_sessionごとに集約して1セッションあたりの開始時間、終了時間、その差分として利用時間を取得します。 これで「セッションを利用したアクセスログの分析」に必要なクエリが完成しました。
WITH datum_access_flag AS (
SELECT
user_id,
access_time,
CAST(DATEDIFF(sec, NVL(LAG(access_time) OVER (PARTITION BY user_id ORDER BY access_time), access_time), access_time) > 5 * 60 AS INT) AS session_flag
FROM
datum_access_log
),
datum_access_session AS (
SELECT
*,
user_id || '_' || SUM(session_flag) OVER (PARTITION BY user_id ORDER BY access_time, session_flag DESC rows unbounded preceding) AS user_session
FROM
datum_access_flag
)
SELECT
user_session,
MIN(access_time) AS start_time,
MAX(access_time) AS end_time,
DATEDIFF(sec, MIN(access_time), MAX(access_time)) AS use_time
FROM
datum_access_session
GROUP BY
user_session
;
user_session | start_time | end_time | use_time ------------------------------------+---------------------+---------------------+---------- 046a9d18551a2f8ab4c060c7547d6540_0 | 2017-05-22 06:17:10 | 2017-05-22 06:17:13 | 3 046a9d18551a2f8ab4c060c7547d6540_1 | 2017-05-25 06:17:22 | 2017-05-25 06:17:29 | 7 046a9d18551a2f8ab4c060c7547d6540_2 | 2017-05-26 04:22:10 | 2017-05-26 04:25:38 | 208 046a9d18551a2f8ab4c060c7547d6540_3 | 2017-05-29 08:23:48 | 2017-05-29 08:23:50 | 2 046a9d18551a2f8ab4c060c7547d6540_4 | 2017-06-05 09:54:11 | 2017-06-05 09:54:53 | 42 046a9d18551a2f8ab4c060c7547d6540_5 | 2017-06-08 10:47:42 | 2017-06-08 10:47:44 | 2 0e5463c0aa8544276ce88a585fc4e5bf_0 | 2017-06-01 01:23:42 | 2017-06-01 01:23:42 | 0
更にここからユーザごとの平均アクセス時間を求めたり、 アクセス回数、アクセス時間のヒストグラムを作成したりできます。今回は紙面の都合上割愛させていただきます。
最後に
このようにウィンドウ関数を利用すると、複雑な集計もシンプルなクエリで表現することができます。次回も実践的なクエリを紹介できればと思いますのでよろしくお願いいたします。
データはあるけど集計環境が作れない、複雑な集計を計画できても実行できないなどでお困りの場合は、ぜひDATUM STUDIOにお声がけください。
DATUM STUDIOでは様々なAI/機械学習のプロジェクトを行っております。
詳細につきましてはこちら
詳細/サービスについてのお問い合わせはこちら
このページをシェアする：
DATUM STUDIOは、クライアントの事業成長と経営課題解決を最適な形でサポートする、データ・ビジネスパートナーです。
データ分析の分野でお客様に最適なソリューションをご提供します。まずはご相談ください。
Contact
Explore Jobs
関連記事