カテゴリー: Web制作

近年のデータサイエンスブームをきっかけに「SQLを学びたい」と思った方へ。

そう思ったところで、壁になってくるのは実データの入手です。
私は職業としてデータサイエンス関連の業務に従事していますが、キレイに整形されたデータはKaggle等にあるものの、実際のログデータとしてはほど遠いという認識があります。

確かに、「実運用されているサービスのログを入手できないか」と言われれば不可能だと思います。
ただ、私は趣味でゲーム作成をしていることもあり、ログデータを作ってきたので入手が可能でした。
学ぶなら実データの方がいいなと思ったので、今回公開します。

そのため、今回はそのゲームデータを使用してSQLを学びましょう！
演習を3つ用意していますので、取り組んでみてください～(^o^)ﾉ

データ

名将と呼ばれた者達の実データを使用します。
このデータはユーザーのすべての行動を記録しているactsログ8-9月分（約14万行）です。

meisyo_acts_output_20200930.zip
※ZIPファイルのため解凍が必要です。
※ID等は個人特定が不可能な加工を施しています。

使用データベース

MySQL
※MySQLの最低限のクエリでどうにか集計する方法を学んでほしいため。分析はBigQueryやPostgreSQLなどの方が向いている。
※BigQuery等での読み込みも可能な形式（CSV）のため、そちらでも利用できます。

MySQL環境構築法

XAMPPをインストールし、phpMyAdminを起動してください。
phpMyAdminに実データ（CSV）をインポートしていただくと使用が可能になります。

下記の管理コンソールから、MySQLのAdminをクリックするとphpMyAdminにアクセス可能です。

はじめに

今回行う集計はよくありがちな流れです。

1枚の画像で表しました。

まずはこの図をメモしてください。

集計でミスが起こる場面は下記のどちらかです。
1・1ユーザーごとのログに集約
2・1ユーザーごとのログから図式・文書化

どちらでしょうか？

私の認識では、
2・1ユーザーごとのログから図式・文書化です。

これまでの業務（教育関連）で上手くできない人は、
目的意識がなく、集計ができたら満足してしまうという傾向がありました。
そこさえできていれば問題ないのですが…。そこが難しい。

何のために、何を見たいのかという目的意識をはっきり持ってください。

データベース作成

phpmyAdminを開いて画面左のNewをクリックし、データベースを作成します。

次に、そのデータベースのタブをクリックし、SQLでテーブル構造を設定します。

CREATE TABLE acts (
id INT AUTO_INCREMENT NOT NULL,
mn_id VARCHAR(32),
time VARCHAR(20),
sub VARCHAR(32),
action VARCHAR(1024),
PRIMARY KEY(id)
) ENGINE = INNODB character set utf8mb4;

最後に、テーブル構造を設定したactsにインポートでデータを入れます。
データ量が多いため複数回再送信が必要です。
※id=1を削除しておきましょう。

データ準備を行うことができました。

集計1

目的：日ごとのログインユーザー数を知りたい

まずは、データの構造を理解するところから始めましょう。
設定に使用したSQL構文を確認します。

CREATE TABLE acts (
id INT AUTO_INCREMENT NOT NULL,
mn_id VARCHAR(32),
time VARCHAR(20),
sub VARCHAR(32),
action VARCHAR(1024),
PRIMARY KEY(id)
) ENGINE = INNODB character set utf8mb4;

前提として、各カラムの意味、データの生成要件、用語の定義を知る必要があります。

各カラムの意味は以下の通りです。
id：テーブルログの生成id
mn_id：監督ID（プレイヤーIDと同義）
time：JST（UTC+9）時間での時間表記
sub：機能名
action：各機能での詳細ログ

データ生成要件は以下の通りです。
・何らかの機能を触ると、全ての行動が取得される。
（ログインしなければログは出されない）

用語の定義は以下の通りです。
・ログイン：何らかの機能に触れたことがある（＝何らかのログが生成されている）
・日ごと：日本時間での1日ごと

このように、集計の前提を定義することが非常に重要です。
なぜなら、それを定義していないと「思っていたことと違う」分析になってしまいます。
今回は、目的：日ごとのログインユーザー数を知りたいなので、そこから外れないようにしたいですね。

続いて、集計の考え方の手順です。

1・1ユーザーごとのログに集約

まず1からやっていきましょう～。
1ユーザーごと、かつ定義した日ごとのログに集約していきます。

SELECT
  DATE(time) AS date
  ,mn_id
FROM
  `acts`
GROUP BY
  date
  ,mn_id

※DISTINCTでもOKだが、後々のこと（日ごとのプレイ回数を付与等）を考えるとGROUP BYが最適。

2・1ユーザーごとのログから図式・文書化

続いて、日ごとの人数を出力します。

SELECT
  a.date
  ,COUNT(a.mn_id) AS count_uu
FROM
  (
    SELECT
      DATE(time) AS date
      ,mn_id
    FROM
      `acts`
    GROUP BY
      date
      ,mn_id
  ) AS a
GROUP BY
  a.date
ORDER BY
  a.date

※COUNT関数でDISTINCTを使う人は多いです。ただし、今回はすでにa内でGROUP BYしているので不要です。

このようにすると、目的：日ごとのログインユーザー数を知りたいを達成できます。

集計2

目的：日ごとのログインユーザー数とその継続率を知りたい

集計1を終えた方は、継続率って何だろう？決めないとと思っていただけるかと思います。

継続率は、「ログインした当日からN日後の日にログインしているか」という定義にします。
10月10日にログインしていた場合、7日後なら10月17日にログインしているかどうかを判定します。

この指標は、ゲームでユーザーが継続していることはKGI（Key Goal Indicator）（※売上等）の達成に良いことであると仮定しているため、集計を行います。

ここでは、LEFT JOINを使います。

下記のように接続を行います。、
10月10日にログインしたユーザー　←　M月J日にログインしたユーザー
プレイヤーIDは同一であるなら結合をOKとする。
SELECTで、N日後を設定する。

INNER JOINだと、
10月10日にログインかつM月J日にログイン、でどちらもログインしていないユーザーは消えます。

LEFT JOINだと、
10月10日にログインしていればよくてM月J日にログインしているかどうかは何も決めないという強くない制約を置きます。
※WITH句を使えないMySQLのバージョンなので、同じ構文を再度書いています。

1・1ユーザーごとのログに集約

まず1からやっていきましょう～。
1ユーザーごと、かつ定義した日ごとのログに集約していきます。

SELECT
  DATE(time) AS date
  ,mn_id
FROM
  `acts`
GROUP BY
  date
  ,mn_id

続いて、1ユーザーごとにN日後にログインしたかどうかのフラグを付けていきます。

SELECT
  a.mn_id
  ,a.date
  ,MAX(CASE WHEN DATEDIFF(IFNULL(b.date, '9999-12-31 23:59:59'), a.date) = 7 THEN 1 ELSE 0 END) AS flag_day7 # 差分が7日あるユーザー（Nullの場合の対応をIFNULLで追加）
  ,MAX(CASE WHEN DATEDIFF(IFNULL(b.date, '9999-12-31 23:59:59'), a.date) = 14 THEN 1 ELSE 0 END) AS flag_day14 # 差分が14日あるユーザー
  ,MAX(CASE WHEN DATEDIFF(IFNULL(b.date, '9999-12-31 23:59:59'), a.date) = 30 THEN 1 ELSE 0 END) AS flag_day30 # 差分が30日あるユーザー
FROM
  (
    SELECT
      DATE(time) AS date
      ,mn_id
    FROM
      `acts`
    GROUP BY
      date
      ,mn_id
  ) AS a
LEFT JOIN
  (
    SELECT
      DATE(time) AS date
      ,mn_id
    FROM
      `acts`
    GROUP BY
      date
      ,mn_id
  ) AS b
ON
  a.mn_id = b.mn_id # プレイヤーIDが同じなら結合
GROUP BY
  a.mn_id
  ,a.date

2・1ユーザーごとのログから図式・文書化

最後に、継続率を出していきましょう。

N日後継続率＝ 当日ログインしていた人のうち、N日後にもログインしていた人 / 当日ログインしていた人なので、AVGで出せます。
AVGは計算方法が=SUM(case)/COUNT(all)なので、フラグ化した変数を使用すると実施割合を出力できます。

SELECT
  a.date
  ,COUNT(a.mn_id) AS count_uu
  ,AVG(flag_day7) AS per_day7
  ,AVG(flag_day14) AS per_day14
  ,AVG(flag_day30) AS per_day30
FROM
  (
    SELECT
      a.mn_id
      ,a.date
      ,MAX(CASE WHEN DATEDIFF(IFNULL(b.date, '9999-12-31 23:59:59'), a.date) = 7 THEN 1 ELSE 0 END) AS flag_day7 # 差分が7日あるユーザー（Nullの場合の対応をIFNULLで追加）
      ,MAX(CASE WHEN DATEDIFF(IFNULL(b.date, '9999-12-31 23:59:59'), a.date) = 14 THEN 1 ELSE 0 END) AS flag_day14 # 差分が14日あるユーザー
      ,MAX(CASE WHEN DATEDIFF(IFNULL(b.date, '9999-12-31 23:59:59'), a.date) = 30 THEN 1 ELSE 0 END) AS flag_day30 # 差分が30日あるユーザー
    FROM
      (
        SELECT
          DATE(time) AS date
          ,mn_id
        FROM
          `acts`
        GROUP BY
          date
          ,mn_id
      ) AS a
    LEFT JOIN
      (
        SELECT
          DATE(time) AS date
          ,mn_id
        FROM
          `acts`
        GROUP BY
          date
          ,mn_id
      ) AS b
    ON
      a.mn_id = b.mn_id # プレイヤーIDが同じなら結合
    GROUP BY
      a.mn_id
      ,a.date
  ) AS a
GROUP BY
  a.date
ORDER BY
  a.date
  

このように継続率を出します。
基本的にソーシャルゲームの業界では1・3・7・14・30日後継続率を出すことが多いですが、あくまで一般論であってN日を定義するのはアナリスト次第です。

集計3

目的：2020年9月にログインしたユーザーは何のコンテンツをプレイしているのか知りたい

集計2を終え、何となくですがSQLが分かってきたかと思います。
今回は・・・何のコンテンツをプレイしているかという問題です。

まず用語の定義が必要ですが、ドメイン知識（領域知識、業界知識）がないのでコンテンツとは何かわかりません。

そのため、コンテンツの種類を知るための・・・集計のための集計を行います。
コンテンツの種類が分かったところで、プレイしている人数が分からないと重要度がわかりませんので、併せて集計しておきます。

SELECT
  sub
  ,COUNT(DISTINCT mn_id) AS count_uu # DISTINCT必須
FROM
  `acts`
WHERE
  DATE_FORMAT(DATE(time),'%Y-%m-01') = '2020-09-01' # 9月に限定
GROUP BY
  sub
ORDER BY
  count_uu DESC # 降順

下記表が出力されます。

ここで立ち止まって考えてみましょう。
なぜ何のコンテンツをプレイしているか知りたいのか？を考えてみてください。

私が出せる仮説は、大きくは以下の通りです。
・とりあえず大まかなプレイ傾向を知りたいから、試合や特訓のメインコンテンツが遊ばれているか知りたい
・新規登録ユーザーが想定したプレイ内容（練習試合、特訓）きちんと遊んでくれているのか知りたい
・既存ユーザーが全然プレイしていないコンテンツがないか知りたい

この3点をSQLで集計しようとすると全然違ったモノが必要ですよね？

解決策としては、依頼者に話を聞きましょう。
「何が知りたいのか」「なぜ知りたいのか」をです。
ここをしない人は必ず失敗します。ご注意ください。

今回は講座なので、下記の仮説に従って集計していきます。
とりあえず大まかなプレイ傾向を知りたいから、試合や特訓のメインコンテンツが遊ばれているか知りたい

ドメイン知識を整理します。
試合は3種類あります。
20時間に1回いずれかをプレイ可能です。監督LvUPするともう1度遊べます。
・練習試合：月ごとのリーグ戦（PvP）を行うコンテンツ。
　プレイはsub=game_practice_goで取得可能
・シナリオ：難易度が選べるvsNPCコンテンツ。一度負けると最初からのタワー形式。
　プレイはsub=senario_goで取得可能
・模擬戦：対戦相手が選べるvsNPCコンテンツ。
　プレイはsub=game_test_goで取得可能

特訓は3種類あります。
20時間に1回いずれかをプレイ可能です。監督LvUPするともう1度遊べます。
・挑戦：クリアすることでオリジナル選手を獲得できるコンテンツ
　プレイはsub=practice_chall_fで取得可能
・特訓：特定の選手を強化かつ投打データ取得が行えるコンテンツ
　プレイはsub=practice_training_goで取得可能
・探索：選手強化アイテムを取得できるコンテンツ
　プレイはsub=map_json_userおよびaction=1で取得可能。
　action=0はサーバー接続確認のための確認ログ。

用語の定義としては、以下を決めておきます。
・遊ばれているか
　ログインしている人のうち、遊ばれている割合

ではそれをSQLの考え方を使ってまとめていきましょう。

1・1ユーザーごとのログに集約

GROUP BYを使い、MAXでフラグ化するととても扱いやすいログ形式になります。

SELECT
  DATE(time) AS date
  ,mn_id
  ,MAX(CASE WHEN sub = 'game_practice_go' THEN 1 ELSE 0 END) AS flag_play_gpractice
  ,MAX(CASE WHEN sub = 'senario_go' THEN 1 ELSE 0 END) AS flag_play_gsenario
  ,MAX(CASE WHEN sub = 'game_test_go' THEN 1 ELSE 0 END) AS flag_play_gtest
  ,MAX(CASE WHEN sub = 'practice_chall_f' THEN 1 ELSE 0 END) AS flag_play_pchall
  ,MAX(CASE WHEN sub = 'practice_training_go' THEN 1 ELSE 0 END) AS flag_play_ptraining
  ,MAX(CASE WHEN sub = 'map_json_user' AND action = '1' THEN 1 ELSE 0 END) AS flag_play_pmap
FROM
  `acts`
WHERE
  DATE_FORMAT(DATE(time),'%Y-%m-01') = '2020-09-01' # 9月に限定
GROUP BY
  date
  ,mn_id

2・1ユーザーごとのログから図式・文書化

ここで注意したいのは、「試合・特訓をどちらもプレイしていてほしい」という依頼者が見たいであろう意図を汲むことです。
試合・特訓の種類ごとに役割が異なるので、プレイしているユーザー層は違うことが予想されますが、今回はそもそもプレイしているんだっけ？ということが知りたいのです。

SELECT
  a.date
  ,COUNT(a.mn_id) AS count_uu
  ,AVG(CASE WHEN flag_play_gpractice = 1 OR flag_play_gsenario = 1 OR flag_play_gtest = 1 THEN 1 ELSE 0 END) AS per_play_game # 試合のうちいずれかをプレイ
  ,AVG(CASE WHEN flag_play_pchall = 1 OR flag_play_ptraining = 1 OR flag_play_pmap = 1 THEN 1 ELSE 0 END) AS per_play_practice # 特訓のうちいずれかをプレイ
  ,AVG(flag_play_gpractice) AS per_play_gpractice
  ,AVG(flag_play_gsenario) AS per_play_gsenario
  ,AVG(flag_play_gtest) AS per_play_gtest
  ,AVG(flag_play_pchall) AS per_play_pchall
  ,AVG(flag_play_ptraining) AS per_play_ptraining
  ,AVG(flag_play_pmap) AS per_play_pmap
FROM
  (
    SELECT
      DATE(time) AS date
      ,mn_id
      ,MAX(CASE WHEN sub = 'game_practice_go' THEN 1 ELSE 0 END) AS flag_play_gpractice
      ,MAX(CASE WHEN sub = 'senario_go' THEN 1 ELSE 0 END) AS flag_play_gsenario
      ,MAX(CASE WHEN sub = 'game_test_go' THEN 1 ELSE 0 END) AS flag_play_gtest
      ,MAX(CASE WHEN sub = 'practice_chall_f' THEN 1 ELSE 0 END) AS flag_play_pchall
      ,MAX(CASE WHEN sub = 'practice_training_go' THEN 1 ELSE 0 END) AS flag_play_ptraining
      ,MAX(CASE WHEN sub = 'map_json_user' AND action = '1' THEN 1 ELSE 0 END) AS flag_play_pmap
    FROM
      `acts`
    WHERE
      DATE_FORMAT(DATE(time),'%Y-%m-01') = '2020-09-01' # 9月に限定
    GROUP BY
      date
      ,mn_id
  ) AS a
GROUP BY
  a.date
ORDER BY
  a.date

下記のように出力できました。

データを見ていて気付い方かも居るかもしれませんが、
19日から20日に練習試合からシナリオへユーザーのプレイ傾向が偏りました。
これは、ゲーム内の仕様で練習試合の月次リーグ戦が20日の0時に終了するからです。
それ以降はプレイしてもランキングは変わらないので、シナリオに移行するユーザーも出てくるといった状況が、数値からも見て取れます。

おわりに

今回は行動ログから簡単な集計を行いました。
前提を定義し、なぜそうなるかを考えながら集計を行うという一連の流れを経験できたかと思います。

実際にユーザーが行動した生データですので、いろいろと利用できる方法はあると思います。
例えば、「アイテム課金するユーザーはどのような傾向にあるか。何をどれくらいプレイしていないと課金しないのか。」など、運営目線で言うと実際の売り上げにつながる分析が欲しいですよね。

あとは、「このデータがないと分析できないよ」という気づきもあります。
例えば、月1回の公式戦と呼ばれるトーナメント戦があるのですが、ログは公式戦閲覧しか取っておらず、閲覧ユーザーのチームが勝ったかどうか（または単純に他のチームが気になるから見ているか）はわかりません。
このゲームの中心として公式戦を位置付けているので、「公式戦を勝つために工夫するユーザー像」がプレイを継続するモチベーションではないかと。
それを分析できないのは、公式戦システムの都合もあります。

そのように、SQLを学び、考えるきっかけになれば幸いです。

反響があれば中級編に続くかもしれません。
今回は簡単な行動データのみでしたが、次回はスナップショットや選手データ、アイテム保有など組み合わせた複雑なデータを取り扱えればと思います。

SQLをさらに勉強したい方へのおすすめ図書

SQLをとりあえずガリガリ書けるようになりたいならこちら。技術的、ドメイン知識的な面は弱い。
テスト用のdokoQLの試行回数がもっと増えれば良いなと思います。

まさにオライリー。教科書。基礎はこれでOK。

分析技術寄りの本。入門書の次に買った方が良さそう。
10年戦えるかは置いといて良書。

実務を教えるときに使うならコレ。ドメイン知識寄りの本。
ただ、実務で使わない内容も多いため、こういったものがあるんだなと知るのにも便利。