前回は、サンプルデータベース内の行動ログを取り上げて、アクティブユーザー数を調べました。

まずはCOUNTを使用して登録されているユーザーの数を調べ、次に退会しているユーザーを除きました。DISTINCTを使って重複データを取り除いたり、日付け毎、月毎などより詳細なアクティブユーザー数を調べていきました。


今回は、ユーザーの獲得経験値の合計や、平均を計算する方法を学習していきます。

さらに、ユーザーのプレイ開始日やプレイ最終日なども調べていきます。

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

今回題材としているオンラインRPGでは、イベントごとに獲得できる経験値やゴールドは決まっています。

そこで、eventlogテーブルからユーザーが獲得した経験値の合計や、平均を計算して表示していきます。

まずは、ユーザーIDと、各ユーザーが獲得した経験値を表示してみます。

以下のSQL文を使用します。

SELECT
eventlog.userID AS ユーザーID,
events.increase_exp AS 獲得経験値
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID;

eventlogテーブルとeventsテーブルを、それぞれのテーブルのeventIDで関連付け、eventlogにあるuserIDと、eventsテーブルにある獲得経験値を表示させます。

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


このように、ユーザーIDと獲得経験値が表示されました。

ちょっと見にくいので、それぞれのユーザーが獲得した経験値を合計して表示してみます。

合計を表示するためには、SUM関数を使用します。

SELECT
eventlog.userID AS ユーザーID,
SUM(events.increase_exp) AS 合計獲得経験値
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID
GROUP BY eventlog.userID;

events.increase_expをSUMで括って、GROUP BYでeventlog.userIDをひとまとめにします。

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


ユーザーIDをひとまとめにし、獲得経験値を合計して表示することができました。

SQLでは、このように関数やGROUP BYを使用して合計や平均を求める事ができます。


では続いて、獲得経験値の平均を求めてみます。

ユーザーがいろんなイベントを実行する中で、１イベントで獲得できる経験値の平均を計算します。

平均を求めるには、AVGというアベレージ関数を使用します。

SELECT
eventlog.userID AS ユーザーID,
SUM(events.increase_exp) AS 合計獲得経験値,
AVG(events.increase_exp) AS 平均獲得経験値
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID
GROUP BY eventlog.userID;

SUMの下にAVGを入れ、平均獲得経験値を計算しています。

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


このように、合計獲得経験値の隣に獲得した経験値の平均が表示されました。


今度は集計した結果から、表示するデータを絞り込んでみます。

同じデータの中で絞り込む場合、HAVINGという命令が使えます。

HAVINGは、WHEREと同じように使える命令ですが、WHEREと違ってGROUP BYでまとめた後に使う事ができます。

まずは、獲得した経験値の合計が3000以上のユーザーに絞り込んで表示してみます。

SELECT
eventlog.userID AS ユーザーID,
SUM(events.increase_exp) AS 合計獲得経験値,
A

まずは、ユーザーの各イベントの開始日時と終了日時を調べて表示します。

SELECT
eventlog.userID AS ユーザーID,
startTime,
endTime
FROM
eventlog;

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


このように、イベントの開始日時と終了日時が表示されました。

ここから、ユーザーのプレイ開始日とプレイ最終日を調べます。

プレイ開始日を見つけるためには、startTimeが1番小さいデータを、プレイ最終日を見つけるためにはendTimeが1番大きいデータを調べると表示することができます。

これには、最小値を求めるminimum関数と、最大値を求めるmax関数を使用します。

SELECT
eventlog.userID AS ユーザーID,
MIN(startTime) AS 開始日,
MAX(endTime) AS 終了日
FROM
eventlog
GROUP BY eventlog.userID;

MINでstartTimeの最小値を、MAXでendTimeの最大値を取っています。

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


このデータから、例えば4番のプレイヤーは2月1日の23時から、3月2日の23時17分までゲームをプレイしていたということが読み取ることができます。