前回は、まずSQLを使ってログ解析にチャレンジしました。

題材として、データベースに格納されたオンラインRPGの新規作成などの行動ログを取り上げ、日時と月次のアクセス数を調べました。

その次に、COUNTを使用しusersテーブルのデータの数を数えて表示させました。


今回は、前回同様ログ解析を進めて行きます。usersテーブルのデータの数を数えて終わったので、その続きとしてより詳細にデータベースの中身を見ていきたいと思います。

なお、今回使用するサンプルデータベースの中は前回に引き続きこちらです。

前回はこのSQL文を使い、COUNTを使用しusersテーブルにいくつデータが入っているかを見てみました。

SELECT COUNT(*) FROM users;

このSQL文を実行すると、このような実行結果になります。


これが、現在登録されているユーザーの数です。

ですが、これには既に退会したユーザーも含まれているため、退会したユーザーを除いたユーザー数を調べてみます。


usersテーブルには、このようなデータが入っています。

このテーブルに、deleted_atというカラムがあります。

このカラムは、ユーザーが退会した時に、その日時を入れておくカラムです。

退会済みのユーザーは日時が入っていますが、退会していないユーザーはNULLとなっています。


ちなみに、データが無いなら0を使えばいいのでは？と考える事もあるかもしれませんが、それだと0というデータが入っているという事になってしまうので、データが何も入っていなく、空っぽだということを示すためにNULLを使います。


さて、では退会したユーザーを除いたユーザー数を調べます。

そのためには、deleted_atにデータが入っていない、NULLのユーザーを取り出します。

それには、次のようなSQL文を記述します。

SELECT COUNT(*) AS アクティブユーザー数
FROM users
WHERE deleted_at IS NULL;

このSQL文を実行すると、


このように表示されます。

これで、退会したユーザー数が20人という事、現在も登録されているユーザー数が80人だという事がわかります。

ですが、usersテーブルからアクティブユーザー数を求めると、会員登録はしているがゲームはプレイしていないというユーザーもカウントしてしまいます。

ですので、アクティブユーザー数をイベントログでイベントを行ったユーザーから求めてみます。


まずは、eventlogテーブルからユーザーIDを、重複しないように表示させます。

そのためには、このようなSQL文を記述します。

SELECT DISTINCT userID AS アクティブユーザー数
FROM eventlog;

DISTINCT を使うと、userIDを抽出した時に重複しているデータを省いて表示することができます。

このSQL文の実行結果がこちらです。


それでは次に、先ほどと同じように退会したユーザーを省いて表示させます。

そのためにはeventlogテーブルとusersテーブルを結合して、deleted_atがNULLのデータのみを表示させる必要があります。

SELECT DISTINCT eventlog.userID AS アクティブユーザー数
FROM eventlog
INNER JOIN users ON users.userID = eventlog.userID
WHERE deleted_at IS NULL;

このようにINNER JOINでusersテーブルとeventlogテーブルのuserIDを関連付け、結合してからdeleted_atがNULLのデータを表示させます。


注意点として、SELECTの行で指定するuserIDは、usersテーブルとeventlogテーブル両方に存在するので、eventlog.userIDと記述しなければなりません。

このSQL文を実行すると、