SQLデータ分析

連載:SQLのウィンドウ関数を利用した分析―セッションを利用したアクセスログの分析

はじめに

みなさん、こんにちは! DATUMSTUDIOの宇佐見です。

普段の業務ではデータアナリストとして、他の企業さまから依頼を受けてデータ分析のお手伝いや データ分析基盤の構築をさせていただいております。

SQLを利用した様々な分析に携わることが多いため、今回はそのノウハウを紹介できればと思います。

やること紹介

本記事では3回にわけてウィンドウ関数を利用したSQLのケーススタディを紹介いたします。実際のアクセスログを利用して、実践ですぐに活用できることをコンセプトとしています。前提知識としてSQLを利用した基礎的な集計を行えるレベルを想定しています。

第1回目は、「セッションを利用したアクセスログの分析」ということで前回アクセスから5分以内の再訪問ならば同一のセッションでの接続とみなし、そのセッションごとの集計を可能とするクエリを作成します。このクエリを利用することで、ユーザの滞在時間や訪問回数などを把握することができます。

環境とデータソース

実行環境としてAWSのRedshiftを利用します。他の環境であってもウィンドウ関数が利用できれば、多少の書き換えで実行可能です。Redshiftを選定した理由としては以下の点が挙げられます。

  • Postgresql準拠のSQLでの問い合わせが可能である
  • 一時的に利用でき、他のDWHと比較して安価で容易に利用できる
  • Redshiftのドキュメントにおいてウィンドウ関数の説明が充実している

データソースに関してはDATUMSTUDIOのホームページのアクセスログを利用します。アクセスログにおけるIPアドレスをハッシュ化し、それをユーザIDと見なしています。

以下のようなデータ形式となっております。

ウィンドウ関数の説明

実際のケーススタディの前に簡単にウィンドウ関数の説明をします。

ウィンドウ関数と集約関数が行う処理は非常によく似ています。集約関数はGROUP BY句に指定した項目について集約を行います。集約の結果を指定した項目数分だけに出力されます。それに対してウィンドウ関数はPARTITION BYで指定した項目についてウィンドウを作成し、その中で計算を行います。計算の結果は元の行数分出力されます。

以下の図は集約関数、ウィンドウ関数のそれぞれを適用した例となります。

ウィンドウ関数のイメージ図

ウィンドウ関数のイメージ図

ウィンドウ関数の多くは以下の構造になっています。

FUNCTION_NAMEに利用するウィンドウ関数を指定します。expressionは集計対象の値を入力し、expr_listでウィンドウの作成範囲を指定します。order_listによってウィンドウの中でソートが行われます。 レコードの順番が影響する関数を利用するならば非常に重要になります。frame_clauseは作成されたウィンドウの範囲を変化させることができます。

セッションを利用したアクセスログの分析

本記事では、簡単なクエリからスタートしその過程を説明しながら、ゴールである「セッションを利用したアクセスログの分析」のクエリを完成させます。実務でクエリを作る際も段階を経ながら、クエリを作成することが多いです。

早速ウィンドウ関数の1つであるLAG関数を利用します。 LAG関数はウィンドウの中で任意のレコード数前の値を取得でき、以下が関数利用のイメージ図となります。

LAG関数のイメージ図

LAG関数のイメージ図

以下のクエリを利用して、user_idごとにaccess_time順に並べた際に1レコード前のaccess_timeの値を取得します。

続いて前回のアクセス時間(prev_access_time)と今回のアクセス時間(access_time)の差を取得します。RedshiftでTIMESTAMP型の演算をするならば、DATEDIFF関数を用います。前回のクエリでは初回アクセスで、前回のアクセス時間が取れない場合はNULLとなっています。そのため、NVL関数を利用し、前回のアクセス時間がNULLの場合は現在のアクセス時間に置き換えます。

更に前回アクセスから5分以内であれば0、5分より長ければ1を返すようにします。 論理型ではなく数値型で扱いたいため、CAST関数を利用しています。

ウィンドウ関数内で、ウィンドウ関数を利用することができない(ネストできない)ため、 WITH句を利用して今までのクエリを副問い合せにまとめておきます。

今度はウィンドウ関数のSUMを利用して、session_flagの数を合計します。frame_clauseにはrows unbounded precedingを指定して、現在のレコード以前のsession_flagを合計の対象とします。すると対象となるユーザが今まで利用したセッションの数を取得することができます。SUM関数を適用する上でaccess_timeが同一の場合はsession_flagが1のものを優先するようにしました。

SUM関数の挙動イメージは以下の図になります。

SUM関数のイメージ図

SUM関数のイメージ図

session_countにuser_idを結合して、ユニークなセッションを作成します。 これで前のレコードから5分より短い場合のアクセスは前と同じセッションが割り振られ、5分以上の場合は新しいセッションが割り振られます。

最後にuser_sessionごとに集約して1セッションあたりの開始時間、終了時間、その差分として利用時間を取得します。 これで「セッションを利用したアクセスログの分析」に必要なクエリが完成しました。

更にここからユーザごとの平均アクセス時間を求めたり、 アクセス回数、アクセス時間のヒストグラムを作成したりできます。今回は紙面の都合上割愛させていただきます。

最後に

このようにウィンドウ関数を利用すると、複雑な集計もシンプルなクエリで表現することができます。次回も実践的なクエリを紹介できればと思いますのでよろしくお願いいたします。

データはあるけど集計環境が作れない、複雑な集計を計画できても実行できないなどでお困りの場合は、ぜひDATUM STUDIOにお声がけください。

このページをシェアする: