イチから学ぶデータベース・SQL（５）では、サンプルデータベースに登録されているデータのログ解析をしていきました。
今回も前回同様paizaラーニングを参考に、オンラインRPGを題材としてSQLについて学習を進めていきます。
今回も前回同様paizaラーニングを参考に、オンラインRPGを題材としてSQLについて学習を進めていきます。
イチから学ぶデータベース・SQL（６）
1. 前回の振り返りと今回学習する内容
前回は、サンプルデータベース内の行動ログを取り上げて、アクティブユーザー数を調べました。
まずはCOUNTを使用して登録されているユーザーの数を調べ、次に退会しているユーザーを除きました。DISTINCTを使って重複データを取り除いたり、日付け毎、月毎などより詳細なアクティブユーザー数を調べていきました。
今回は、ユーザーの獲得経験値の合計や、平均を計算する方法を学習していきます。
さらに、ユーザーのプレイ開始日やプレイ最終日なども調べていきます。
なお、今回使用するサンプルデータベースは前回に引き続きこちらです。
まずはCOUNTを使用して登録されているユーザーの数を調べ、次に退会しているユーザーを除きました。DISTINCTを使って重複データを取り除いたり、日付け毎、月毎などより詳細なアクティブユーザー数を調べていきました。
今回は、ユーザーの獲得経験値の合計や、平均を計算する方法を学習していきます。
さらに、ユーザーのプレイ開始日やプレイ最終日なども調べていきます。
なお、今回使用するサンプルデータベースは前回に引き続きこちらです。
2. ユーザーが獲得した経験値などの色々な計算をしてみる
今回題材としているオンライン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
そこで、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
3. ユーザーのプレイ開始日とプレイ最終日を調べてみる
まずは、ユーザーの各イベントの開始日時と終了日時を調べて表示します。
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分までゲームをプレイしていたということが読み取ることができます。
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分までゲームをプレイしていたということが読み取ることができます。