イチから学ぶデータベース・SQL（６）では、サンプルデータベースに登録されているデータから合計や平均などの計算をしていきました。
今回も、これまでと同様にpaizaラーニングを参考にし、オンラインRPGを題材としてSQLについて学習していきます。
今回も、これまでと同様にpaizaラーニングを参考にし、オンラインRPGを題材としてSQLについて学習していきます。
イチから学ぶデータベース・SQL（７）
1. 前回の振り返りと今回学習する内容
前回は、eventlogテーブルからユーザーが獲得した経験値の合計や平均などを四則演算を用いて計算しました。
合計を求めるにはSUM関数を、平均を求めるにはAVG関数を使用します。
また、SQLでプログラムが実行される順番には決まりがあり、
1.FROM (データを取り出す)
2.WHERE (条件を指定)
3.GROUP BY (グループ化)
4.HAVING (絞り込む)
5.SELECT (データを表示)
となっています。
今回は、前回使った四則演算を使用し、プレイ期間を計算したり、生年月日から年齢を計算したりといった、日付に関する計算をしていきます。
なお、今回使用するサンプルデータベースは前回に引き続きこちらです。
合計を求めるにはSUM関数を、平均を求めるにはAVG関数を使用します。
また、SQLでプログラムが実行される順番には決まりがあり、
1.FROM (データを取り出す)
2.WHERE (条件を指定)
3.GROUP BY (グループ化)
4.HAVING (絞り込む)
5.SELECT (データを表示)
となっています。
今回は、前回使った四則演算を使用し、プレイ期間を計算したり、生年月日から年齢を計算したりといった、日付に関する計算をしていきます。
なお、今回使用するサンプルデータベースは前回に引き続きこちらです。
2. ユーザーのプレイ期間を計算してみる
前回の最後にユーザーがゲームをプレイした開始日と最終日をMIN関数とMAX関数を用いて計算しました。
それを利用し、ここではまずユーザーの累計のプレイ期間を計算してみたいと思います。
前回作成したユーザーのプレイ開始日と最終日を計算するためのSQL文がこちらです。
SELECT
userID AS ユーザーID,
MIN(startTime) AS プレイ開始日,
MAX(endTime) AS プレイ終了日
FROM
eventlog
GROUP BY userID;
SQLでは、通常のプログラミング言語と同じように足し算の+、引き算の-、掛け算の*、割り算の/の四則演算を使う事ができます。
括弧があると、その中を先に計算します。
ユーザーのプレイ期間を求めるには、プレイの終了日から開始日を引き算し、それに＋１をすると求める事ができます。
SELECT
userID AS ユーザーID,
MIN(startTime) AS プレイ開始日,
MAX(endTime) AS プレイ終了日,
DATE(MAX(endTime)) - DATE(MIN(startTime)) +1 AS プレイ期間
FROM
eventlog
GROUP BY userID;
例えばプレイ開始日が1月1日で、プレイ最終日が1月10日のプレイヤーは10-1+1で10日となります。
プレイ開始日とプレイ最終日が同じ日にちの場合、+1することによってプレイ期間が1日と表示されます。
このSQL文を実行すると、この画像のようになります。
ユーザーID毎のプレイ期間が表示されました。
ユーザーIDが1のプレイヤーは1日、4のプレイヤーは102日もプレイしているということがわかります。
それを利用し、ここではまずユーザーの累計のプレイ期間を計算してみたいと思います。
前回作成したユーザーのプレイ開始日と最終日を計算するためのSQL文がこちらです。
SELECT
userID AS ユーザーID,
MIN(startTime) AS プレイ開始日,
MAX(endTime) AS プレイ終了日
FROM
eventlog
GROUP BY userID;
SQLでは、通常のプログラミング言語と同じように足し算の+、引き算の-、掛け算の*、割り算の/の四則演算を使う事ができます。
括弧があると、その中を先に計算します。
ユーザーのプレイ期間を求めるには、プレイの終了日から開始日を引き算し、それに＋１をすると求める事ができます。
SELECT
userID AS ユーザーID,
MIN(startTime) AS プレイ開始日,
MAX(endTime) AS プレイ終了日,
DATE(MAX(endTime)) - DATE(MIN(startTime)) +1 AS プレイ期間
FROM
eventlog
GROUP BY userID;
例えばプレイ開始日が1月1日で、プレイ最終日が1月10日のプレイヤーは10-1+1で10日となります。
プレイ開始日とプレイ最終日が同じ日にちの場合、+1することによってプレイ期間が1日と表示されます。
このSQL文を実行すると、この画像のようになります。
ユーザーID毎のプレイ期間が表示されました。
ユーザーIDが1のプレイヤーは1日、4のプレイヤーは102日もプレイしているということがわかります。
3. ユーザーの年齢を計算してみる
続いては、現在の年と登録されている生年月日から、それぞれのユーザーの現在の年齢を調べてみます。
計算方法は、プレイ期間を計算した時と同じように現在の年から登録されている生年月日を引き算して求めます。
まずは、ユーザーIDと登録されている生年月日を表示してみます。
SELECT
userID AS ユーザーID,
birth AS 生年月日
FROM
users;
usersテーブルからuserIDとbirthの情報を選択して取り出しています。
このSQLを実行すると、この画像のようになります。
次に、さらに現在の日時を表示します。
SELECT
userID AS ユーザーID,
YEAR(CURRENT_DATE()) AS 現在年,
birth AS 生年月日
FROM
users;
現在の年はデータベース内には保存されていませんが、CURRENT_DATE関数で取り出すことができます。
CURRENT_DATE関数は現在の年、月、日、時間を表示するので、year関数で括って年だけを表示させています。
このSQL文を実行すると、画像のようになります。
このように、現在の年だけを表示することができました。
あとは、この現在年と生年月日を引き算すると数え年を計算することができます。
SELECT
userID AS ユーザーID,
YEAR(CURRENT_DATE()) AS 現在年,
birth AS 生年月日,
YEAR(CURRENT_DATE()) - YEAR(birth) AS 数え年
FROM
users;
YEAR(CURRENT_DATE()) （現在年）からYEAR(birth)（登録されている生年月日の年）を引いています。
このSQL文を実行すると、画像のようになります。
このように、関数を組み合わせて四則演算を利用することでユーザーの年齢を計算することができます。
なお、上のSQLだと誕生日を考慮せずに年齢を計算しているので、満年齢を求める事ができません。
誕生日を考慮して満年齢を計算するためには、TIMESTAMPDIFF関数を使います。
SELECT
userID AS ユーザーID,
YEAR(CURRENT_DATE()) AS 現在年,
birth AS 生年月日,
YEAR(CURRENT_DATE()) - YEAR(birth) AS 数え年,
TIMESTAMPDIFF(YEAR, birth, CURRENT_DATE()) AS 満年齢
FROM
users;
ここで使われているTIMESTAMPDIFF関数では、birthとCURRENT_DATE()の間の差を整数で取得しているので、満年齢を計算することができます。
このSQLを実行すると、画像のようになります。
ユーザー11は、11月10日現在でまだ誕生日を迎えていないため、数え年は50ですが満年齢は49となっています。
計算方法は、プレイ期間を計算した時と同じように現在の年から登録されている生年月日を引き算して求めます。
まずは、ユーザーIDと登録されている生年月日を表示してみます。
SELECT
userID AS ユーザーID,
birth AS 生年月日
FROM
users;
usersテーブルからuserIDとbirthの情報を選択して取り出しています。
このSQLを実行すると、この画像のようになります。
次に、さらに現在の日時を表示します。
SELECT
userID AS ユーザーID,
YEAR(CURRENT_DATE()) AS 現在年,
birth AS 生年月日
FROM
users;
現在の年はデータベース内には保存されていませんが、CURRENT_DATE関数で取り出すことができます。
CURRENT_DATE関数は現在の年、月、日、時間を表示するので、year関数で括って年だけを表示させています。
このSQL文を実行すると、画像のようになります。
このように、現在の年だけを表示することができました。
あとは、この現在年と生年月日を引き算すると数え年を計算することができます。
SELECT
userID AS ユーザーID,
YEAR(CURRENT_DATE()) AS 現在年,
birth AS 生年月日,
YEAR(CURRENT_DATE()) - YEAR(birth) AS 数え年
FROM
users;
YEAR(CURRENT_DATE()) （現在年）からYEAR(birth)（登録されている生年月日の年）を引いています。
このSQL文を実行すると、画像のようになります。
このように、関数を組み合わせて四則演算を利用することでユーザーの年齢を計算することができます。
なお、上のSQLだと誕生日を考慮せずに年齢を計算しているので、満年齢を求める事ができません。
誕生日を考慮して満年齢を計算するためには、TIMESTAMPDIFF関数を使います。
SELECT
userID AS ユーザーID,
YEAR(CURRENT_DATE()) AS 現在年,
birth AS 生年月日,
YEAR(CURRENT_DATE()) - YEAR(birth) AS 数え年,
TIMESTAMPDIFF(YEAR, birth, CURRENT_DATE()) AS 満年齢
FROM
users;
ここで使われているTIMESTAMPDIFF関数では、birthとCURRENT_DATE()の間の差を整数で取得しているので、満年齢を計算することができます。
このSQLを実行すると、画像のようになります。
ユーザー11は、11月10日現在でまだ誕生日を迎えていないため、数え年は50ですが満年齢は49となっています。
4. ユーザーの平均年齢を調べてみる
ユーザーが登録している生年月日と、現在の年からユーザーの年齢を計算して調べる事ができました。
では次に、現在のユーザーの平均年齢を計算してみたいと思います。
前回使ったとおり、平均を求めるためにはAVG関数を使用します。
SELECT
AVG(TIMESTAMPDIFF(YEAR, birth, CURRENT_DATE())) AS '平均年齢'
FROM
users;
このSQL文を実行すると、画像のようになります。
このように、ユーザーの平均年齢が約36歳だということがわかりました。
では次に、現在のユーザーの平均年齢を計算してみたいと思います。
前回使ったとおり、平均を求めるためにはAVG関数を使用します。
SELECT
AVG(TIMESTAMPDIFF(YEAR, birth, CURRENT_DATE())) AS '平均年齢'
FROM
users;
このSQL文を実行すると、画像のようになります。
このように、ユーザーの平均年齢が約36歳だということがわかりました。
5. ユーザーが実行したイベントを表示してみる
ここまでで四則演算を使用して基本的な計算をすることができました。
最後に、次回学習予定のテキスト検索について、SQLを作成して少しだけ触れていきます。
各ユーザーがいつ、何のイベントを実行したのかを調べるSQLを作成します。
SELECT
userID,
startTime,
events.event_summary
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID;
ここでは、eventlogテーブルととeventsテーブルをeventIDで関連付けて結合し、userIDとstartTime、eventsテーブルのevent_summaryを表示しています。
このSQLを実行すると、画像のようになります。
このように、誰がいつ何をしたかを表示することができました。
event_summaryには、各ユーザーが新規登録やログイン、町に入ったなどを実行した、イベント情報のデータが入っています。
次回はこのデータをもとに、テキスト検索について学習していきます。
最後に、次回学習予定のテキスト検索について、SQLを作成して少しだけ触れていきます。
各ユーザーがいつ、何のイベントを実行したのかを調べるSQLを作成します。
SELECT
userID,
startTime,
events.event_summary
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID;
ここでは、eventlogテーブルととeventsテーブルをeventIDで関連付けて結合し、userIDとstartTime、eventsテーブルのevent_summaryを表示しています。
このSQLを実行すると、画像のようになります。
このように、誰がいつ何をしたかを表示することができました。
event_summaryには、各ユーザーが新規登録やログイン、町に入ったなどを実行した、イベント情報のデータが入っています。
次回はこのデータをもとに、テキスト検索について学習していきます。