SQLTech Blog　

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

はじめに

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

普段の業務ではデータアナリストとして他の企業さまから依頼を受けてデータ分析のお手伝いやデータ分析基盤の構築をさせていただいております。SQLを利用した様々な分析に携わることが多いため、そのノウハウを前回、前々回に引き続き紹介できればと思います。

やること紹介

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

第3回目は、「FQ5を利用したアクセスログの分析」です。ゲームやアプリの業界においてしばしば使われる指標の1つにFQ5があります。(5の代わりに7や10などを利用するバリエーションもあります。)FQ5は当日を含む過去5日間に何回利用したかをユーザごとにカウントし、日付ごとに集計します。単純なユーザ数では広告や曜日の効果を受けてしまうため、この指標を用いると影響を減らすことができます。詳細については以下の資料が参考になるかと思います。

FQ5もウィンドウ関数を利用すると比較的簡単に求めることができます。

環境とデータソース

実行環境は前回に引き続きAWSの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

ウィンドウ関数の説明については前々回の投稿を参照してください。

FQ5を利用したアクセスログの分析

FQ5ということでユーザ1人あたりの1日のアクセスのデータがベースとなります。前回と同じように1人あたりの1日のアクセス回数を以下のクエリで集計します。

SELECT
  CAST(DATE_TRUNC('day', access_time) AS DATE) AS access_day,
  user_id,
  COUNT(*)
FROM
  datum_access_log
GROUP BY
  CAST(DATE_TRUNC('day', access_time) AS DATE),
  user_id
;

 access_day |             user_id              | count
------------+----------------------------------+-------
 2017-05-21 | 3f3fcac868404d0ae7e8ad90be6fc4d8 |   135
 2017-05-21 | 94206ce9bf9de4874af016067d414d7e |     1
 2017-05-21 | d1522ce8a41506346c24c6b1579566dd |    25
 2017-05-21 | e07e844ba0c20d195f4701fb4f14451e |    14
 2017-05-21 | efcdf099ce3f9be9a0f9444f06e2679d |     3
 2017-05-21 | 5cba30f1752bae2a77f05007311b95c3 |     3
 2017-05-21 | 50dda73283d5a5d3bdc3e01c787a03a0 |     4
 2017-05-21 | 5da679a7d0ce86eed996bb4c568765f7 |    14

上記のクエリを副問い合わせにまとめます。そのユーザの対象日から1回前の利用日を求めます。前回、前々回に引き続きLAG関数を利用しています。LAG関数はウィンドウ関数の中で利用頻度の高いものの1つです。

同様に2～4回前の利用日も求めます。

WITH daily_access_data AS (
  SELECT
    CAST(DATE_TRUNC('day', access_time) AS DATE) AS access_day,
    user_id,
    COUNT(*)
  FROM
    datum_access_log
  GROUP BY
    CAST(DATE_TRUNC('day', access_time) AS DATE),
    user_id
)
SELECT
  access_day,
  user_id,
  NVL(LAG(access_day, 1) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS acc_day_1_before,
  NVL(LAG(access_day, 2) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS acc_day_2_before,
  NVL(LAG(access_day, 3) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS acc_day_3_before,
  NVL(LAG(access_day, 4) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS acc_day_4_before
FROM
  daily_access_data
;

 access_day |             user_id              | acc_day_1_before | acc_day_2_before | acc_day_3_before | acc_day_4_before
------------+----------------------------------+------------------+------------------+------------------+------------------
 2017-06-05 | 00001cc54dfaf28cd922158caaa3ad1f | 1970-01-01       | 1970-01-01       | 1970-01-01       | 1970-01-01
 2017-06-15 | 00023345b78c5b5ad98aaffc99a9528e | 1970-01-01       | 1970-01-01       | 1970-01-01       | 1970-01-01
 2017-06-06 | 0004e74949a9e134f8e14638c181dd95 | 1970-01-01       | 1970-01-01       | 1970-01-01       | 1970-01-01
 2017-06-19 | 001533de27d9a51c5359d885a59c6d58 | 2017-06-16       | 2017-06-14       | 2017-06-13       | 2017-06-08
 2017-06-16 | 001533de27d9a51c5359d885a59c6d58 | 2017-06-14       | 2017-06-13       | 2017-06-08       | 2017-06-01
 2017-06-14 | 001533de27d9a51c5359d885a59c6d58 | 2017-06-13       | 2017-06-08       | 2017-06-01       | 2017-05-29
 2017-06-13 | 001533de27d9a51c5359d885a59c6d58 | 2017-06-08       | 2017-06-01       | 2017-05-29       | 1970-01-01
 2017-06-08 | 001533de27d9a51c5359d885a59c6d58 | 2017-06-01       | 2017-05-29       | 1970-01-01       | 1970-01-01
 2017-06-01 | 001533de27d9a51c5359d885a59c6d58 | 2017-05-29       | 1970-01-01       | 1970-01-01       | 1970-01-01
 2017-05-29 | 001533de27d9a51c5359d885a59c6d58 | 1970-01-01       | 1970-01-01       | 1970-01-01       | 1970-01-01
 2017-05-29 | 001e411e16969083a7abf1434dc77500 | 1970-01-01       | 1970-01-01       | 1970-01-01       | 1970-01-01

1～4回前の利用日が4日前の日付以内かどうか判定します。このあとに集計しやすくなるようbool型からint型へ変換しておきます。

WITH daily_access_data AS (
  SELECT
    CAST(DATE_TRUNC('day', access_time) AS DATE) AS access_day,
    user_id,
    COUNT(*)
  FROM
    datum_access_log
  GROUP BY
    CAST(DATE_TRUNC('day', access_time) AS DATE),
    user_id
)
SELECT
  access_day,
  user_id,
  CAST(DATEADD(day, -4, access_day) <= NVL(LAG(access_day, 1) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS int) AS acc_flag_1_before,
  CAST(DATEADD(day, -4, access_day) <= NVL(LAG(access_day, 2) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS int) AS acc_flag_2_before,
  CAST(DATEADD(day, -4, access_day) <= NVL(LAG(access_day, 3) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS int) AS acc_flag_3_before,
  CAST(DATEADD(day, -4, access_day) <= NVL(LAG(access_day, 4) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS int) AS acc_flag_4_before
FROM
  daily_access_data
;

 access_day |             user_id              | acc_flag_1_before | acc_flag_2_before | acc_flag_3_before | acc_flag_4_before
------------+----------------------------------+-------------------+-------------------+-------------------+-------------------
 2017-06-05 | 00001cc54dfaf28cd922158caaa3ad1f |                 0 |                 0 |                 0 |                 0
 2017-06-15 | 00023345b78c5b5ad98aaffc99a9528e |                 0 |                 0 |                 0 |                 0
 2017-06-06 | 0004e74949a9e134f8e14638c181dd95 |                 0 |                 0 |                 0 |                 0
 2017-05-30 | 0012c93b674f56d085fd2b17161829ab |                 0 |                 0 |                 0 |                 0
 2017-06-19 | 001533de27d9a51c5359d885a59c6d58 |                 1 |                 0 |                 0 |                 0
 2017-06-16 | 001533de27d9a51c5359d885a59c6d58 |                 1 |                 1 |                 0 |                 0
 2017-06-14 | 001533de27d9a51c5359d885a59c6d58 |                 1 |                 0 |                 0 |                 0
 2017-06-13 | 001533de27d9a51c5359d885a59c6d58 |                 0 |                 0 |                 0 |                 0
 2017-06-08 | 001533de27d9a51c5359d885a59c6d58 |                 0 |                 0 |                 0 |                 0
 2017-06-01 | 001533de27d9a51c5359d885a59c6d58 |                 1 |                 0 |                 0 |                 0
 2017-05-29 | 001533de27d9a51c5359d885a59c6d58 |                 0 |                 0 |                 0 |                 0

ウィンドウ関数を使った処理は複雑なことができる分、1行あたりが長くなりがちです。そのため本当は副問い合わせにする必要がない処理についても、副問い合わせにすることで可読性を上げることができます。

そして1人ごとに集計日から4日前以内の利用日数を集計します。対象日には必ず利用しているため、1を加えて5日間の利用回数を計算します。

WITH daily_access_data AS (
  SELECT
    CAST(DATE_TRUNC('day', access_time) AS DATE) AS access_day,
    user_id,
    COUNT(*)
  FROM
    datum_access_log
  GROUP BY
    CAST(DATE_TRUNC('day', access_time) AS DATE),
    user_id
), daily_access_flag AS (
  SELECT
    access_day,
    user_id,
    CAST(DATEADD(day, -4, access_day) <= NVL(LAG(access_day, 1) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS int) AS acc_flag_1_before,
    CAST(DATEADD(day, -4, access_day) <= NVL(LAG(access_day, 2) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS int) AS acc_flag_2_before,
    CAST(DATEADD(day, -4, access_day) <= NVL(LAG(access_day, 3) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS int) AS acc_flag_3_before,
    CAST(DATEADD(day, -4, access_day) <= NVL(LAG(access_day, 4) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS int) AS acc_flag_4_before
  FROM
    daily_access_data
)
SELECT
  access_day,
  user_id,
  sum(1 + acc_flag_1_before + acc_flag_2_before + acc_flag_3_before + acc_flag_4_before) AS access_count
FROM
  daily_access_flag
GROUP BY
  access_day,
  user_id
;

 access_day |             user_id              | access_count
------------+----------------------------------+--------------
 2017-06-05 | 00001cc54dfaf28cd922158caaa3ad1f |            1
 2017-06-15 | 00023345b78c5b5ad98aaffc99a9528e |            1
 2017-06-06 | 0004e74949a9e134f8e14638c181dd95 |            1
 2017-05-30 | 0012c93b674f56d085fd2b17161829ab |            1
 2017-06-19 | 001533de27d9a51c5359d885a59c6d58 |            2
 2017-06-16 | 001533de27d9a51c5359d885a59c6d58 |            3
 2017-06-14 | 001533de27d9a51c5359d885a59c6d58 |            2
 2017-06-13 | 001533de27d9a51c5359d885a59c6d58 |            1
 2017-06-08 | 001533de27d9a51c5359d885a59c6d58 |            1
 2017-06-01 | 001533de27d9a51c5359d885a59c6d58 |            2
 2017-05-29 | 001533de27d9a51c5359d885a59c6d58 |            1

最後に上記クエリを副問い合わせにまとめ、対象日と5日間の利用回数ごとにユーザ数を集約すれば完成です。

WITH daily_access_data AS (
  SELECT
    CAST(DATE_TRUNC('day', access_time) AS DATE) AS access_day,
    user_id,
    COUNT(*)
  FROM
    datum_access_log
  GROUP BY
    CAST(DATE_TRUNC('day', access_time) AS DATE),
    user_id
), daily_access_flag AS (
  SELECT
    access_day,
    user_id,
    CAST(DATEADD(day, -4, access_day) <= NVL(LAG(access_day, 1) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS int) AS acc_flag_1_before,
    CAST(DATEADD(day, -4, access_day) <= NVL(LAG(access_day, 2) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS int) AS acc_flag_2_before,
    CAST(DATEADD(day, -4, access_day) <= NVL(LAG(access_day, 3) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS int) AS acc_flag_3_before,
    CAST(DATEADD(day, -4, access_day) <= NVL(LAG(access_day, 4) OVER (PARTITION BY user_id ORDER BY access_day), '1970-01-01') AS int) AS acc_flag_4_before
  FROM
    daily_access_data
), daily_access_count AS (
  SELECT
    access_day,
    user_id,
    sum(1 + acc_flag_1_before + acc_flag_2_before + acc_flag_3_before + acc_flag_4_before) AS access_count
  FROM
    daily_access_flag
  GROUP BY
    access_day,
    user_id
)
SELECT
  access_day,
  access_count,
  COUNT(*) AS user_count
FROM
  daily_access_count
GROUP BY
  access_day,
  access_count
;

 access_day | access_count | user_count
------------+--------------+------------
 2017-05-21 |            1 |        XXX
 2017-05-22 |            1 |        XXX
 2017-05-22 |            2 |        XXX
 2017-05-23 |            1 |        XXX
 2017-05-23 |            2 |        XXX
 2017-05-23 |            3 |        XXX
 2017-05-24 |            1 |        XXX
 2017-05-24 |            2 |        XXX
 2017-05-24 |            3 |        XXX
 2017-05-24 |            4 |        XXX
 2017-05-25 |            1 |        XXX
 2017-05-25 |            2 |        XXX
 2017-05-25 |            3 |        XXX
 2017-05-25 |            4 |        XXX
 2017-05-25 |            5 |        XXX
 2017-05-26 |            1 |        XXX
 2017-05-26 |            2 |        XXX
 2017-05-26 |            3 |        XXX
 2017-05-26 |            4 |        XXX
 2017-05-26 |            5 |        XXX

※具体的なユーザ数が分かってしまうため、数字は伏せさせていただきます。

最後に

3回に渡った連載も今回が最後です。この連載を通してウィンドウ関数を利用すれば複雑な集計も簡単できることを分かっていただけたならば幸いです。

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

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


DATUM STUDIOは、クライアントの事業成長と経営課題解決を最適な形でサポートする、データ・ビジネスパートナーです。
データ分析の分野でお客様に最適なソリューションをご提供します。まずはご相談ください。

Contact

サービス・研修・アドバイザリーに関するお問い合わせはこちら

Explore Jobs

採用に関する情報はこちら

関連記事

前へ 次へ