今回は、サンプルデータベースを使用し、ログの解析をしてユーザーのアクセス数を調べるなど、より実務的なことを学習していきます。

今回使うサンプルデータベースは、こちらです。


このデータベースは、オンラインRPGのデータベースを題材としています。


usersテーブルにはuserIDやjobID、名前、レベルなどの各ユーザーの情報が格納されています。そしてusersテーブルとeventlogは、userIDで関連付けられています。

jobsテーブルにはjobIDとそれに対応したjob_name（職業）の名前が格納されており、usersテーブルのjobIDとjobsテーブルのjobIDが関連付けられています。

areaテーブルにはareaIDとそれに対応したarea_name（都道府県名）が格納されており、usersテーブルのareaIDと関連付けられています。

eventlogテーブルにはlogIDやuserID、各イベントIDや、そのイベントの開始時間や終了時間のログが格納されています。

eventsテーブルには各イベント毎のタイトルや経験値、ゴールドなどが格納されています。

サンプルデータベース内の行動ログを取り上げて、日時と月次のアクセス数を調べてみます。

まずは、eventlogを表示してみます。

SELECT * FROM eventlog;


このSQL文を実行します。


このようにeventlogテーブルが表示されました。

どのプレイヤー(userID)がどのevent(eventID)をいつ開始(startTime)し、いつ終了(endTime)したのかがわかります。


これからログの解析をしてアクセス数を調べていく上で、例えば、画像のuserIDが1のユーザーが新規登録をし、ログインして、と2回イベントが発生した場合アクセス数は2回、とカウントするといった具合で進めていきます。

ではまず最初に、日付情報を持つカラムを表示してみます。

SELECT startTime, logID
FROM eventlog;


イベントログと日付が表示されました。

しかし、余計な時間まで表示されてしまっているため、これを日付だけ表示します。

SELECT DATE(startTime), logID
FROM eventlog;


DATEという関数を使用すると、startTimeの日付のみを表示させることができます。


このように、startTimeに時間が表示されなくなり、日付のみが表示されました。

続いて、アクセス数を求めてみます。

日付け毎のアクセス数を求めるには、次のSQL文を実行します。

SELECT DATE(startTime), COUNT(logID)
FROM eventlog
GROUP BY DATE(startTime);


GROUP BYで日付別にまとめ、COUNT関数で行数を数えています。


このように、日付毎にカウントすることができます。

続いて、特定の日付のアクセス数を調べてみます。ここでは、3月以降のアクセス数を調べてみます。

これは条件なので、WHEREを追加します。

SELECT DATE(startTime), COUNT(logID)
FROM eventlog
WHERE DATE(startTime) >= '2015-03-01'
GROUP BY DATE(startTime);


このように、3月以降でアクセス数を表示することができました。

前に学んだように、WHEREでは=や<など、他の演算子を使用して期間を絞り込むこともできます。

さらに、特定の期間でアクセス数を表示することも可能で、それにはBETWEENとANDを使用します。

例として、5月のみでアクセス数を調べてみます。

SELECT DATE(startTime), COUNT(logID)

まずは、現在登録しているユーザーの人数を調べます。

SELECT COUNT(*) FROM users;


COUNTで、usersテーブルのデータの数を数えています。

このSQL文を実行すると、


このように、usersテーブルのデータ数をカウントして表示することができます。