イチから学ぶデータベース・SQL（１１）
公開日: 2022/12/28
更新日: 2022/12/28
イチから学ぶデータベース・SQL（１０）では、CASE式について学習していきました。
今回も、これまでと同様paizaラーニングを参考に、オンラインRPGを題材としてSQLについて学習していきます。
今回も、これまでと同様paizaラーニングを参考に、オンラインRPGを題材としてSQLについて学習していきます。
1. 前回の振り返りと今回学習する内容
前回は、CASE式についての説明と、CASE式を用いてユーザーのレベル状況や所持ゴールド別にグループ分けし、それぞれユーザー数を求めていきました。
CASE式には単純CASE式と検索CASE式があり、｢=｣や｢<｣などの演算子を使う検索CASE式の方が応用が利きます。
前回、レベル5以上のユーザーを上級者、レベル3以上のユーザーを中級者、それ以外のユーザーを初級者としてグループ分けをするSQLを、
SELECT
userID,
level,
CASE
WHEN level >= 5 THEN '上級者'
WHEN level >= 3 THEN '中級者'
ELSE '初級者'
END AS クラス
FROM
users;
このように記述しました。
CASEからENDまでの中にWHENで条件を記述します。
また、条件を記述する順番も重要で、条件の対象が少ないユーザーから順番に記述していかなければ、対象を条件で全て絞り込んだ場合に残りの条件をスキップしてしまいます。
これまでに、サブクエリやCASE式といったより応用的なSQLを学習してきましたが、今回はクロス集計を行います。
クロス集計は、サブクエリやCASE式などを組み合わせる必要があります。
例えば、イベントログから日付を取り出し、それぞれのユーザーをレベルごとに初級者・中級者・上級者とグループ分けをする場合に、クロス集計を使わない通常の出力結果では、
このように、共通のカラム構成でレコードが縦に並ぶように出力されます。
これをクロス集計を使って出力した場合、
このように、出力結果のデータが縦と横に並ぶように出力することができます。
これは、月ごとでどのランク帯のユーザーが多いのかがわかる結果となっています。
今回は、このようなデータを出力するクロス集計を作っていきます。
なお、今回使用するサンプルデータベースは前回までと同じようにこちらです。
CASE式には単純CASE式と検索CASE式があり、｢=｣や｢<｣などの演算子を使う検索CASE式の方が応用が利きます。
前回、レベル5以上のユーザーを上級者、レベル3以上のユーザーを中級者、それ以外のユーザーを初級者としてグループ分けをするSQLを、
SELECT
userID,
level,
CASE
WHEN level >= 5 THEN '上級者'
WHEN level >= 3 THEN '中級者'
ELSE '初級者'
END AS クラス
FROM
users;
このように記述しました。
CASEからENDまでの中にWHENで条件を記述します。
また、条件を記述する順番も重要で、条件の対象が少ないユーザーから順番に記述していかなければ、対象を条件で全て絞り込んだ場合に残りの条件をスキップしてしまいます。
これまでに、サブクエリやCASE式といったより応用的なSQLを学習してきましたが、今回はクロス集計を行います。
クロス集計は、サブクエリやCASE式などを組み合わせる必要があります。
例えば、イベントログから日付を取り出し、それぞれのユーザーをレベルごとに初級者・中級者・上級者とグループ分けをする場合に、クロス集計を使わない通常の出力結果では、
このように、共通のカラム構成でレコードが縦に並ぶように出力されます。
これをクロス集計を使って出力した場合、
このように、出力結果のデータが縦と横に並ぶように出力することができます。
これは、月ごとでどのランク帯のユーザーが多いのかがわかる結果となっています。
今回は、このようなデータを出力するクロス集計を作っていきます。
なお、今回使用するサンプルデータベースは前回までと同じようにこちらです。
2. ユーザーがランクごとに何人いるのかがわかるクロス集計をしてみる
まずは、eventlogテーブルからイベントの開始時間とユーザーID、ユーザーのレベルを表示します。
SELECT
startTime,
eventlog.userID,
users.level
FROM eventlog
INNER JOIN users ON users.userID = eventlog.userID;
eventlogから、startTime(イベントの開始時間)とeventlogテーブルのuserID、usersテーブルのlevelを取り出し、usersテーブルはusersテーブルのuserIDとeventlogテーブルのuserIDで結合しています。
このSQLを実行すると、
このようになります。
次に、このSQL文を改良してクロス集計を作成していきます。
まずは、日と時間が出力されている日付を年月のみ出力するようにして、ユーザーのレベルごとに初級者、中級者、上級者でグループ分けしていきます。
SELECT
DATE_FORMAT(startTime, '%Y-%m') AS 日付,
eventlog.userID AS ユーザー,
CASE
WHEN users.level >= 4 THEN '上級者'
WHEN users.level >= 2 THEN '中級者'
ELSE '初級者'
END AS 'ランク'
FROM eventlog
INNER JOIN users ON users.userID = eventlog.userID
ORDER BY eventlog.userID;
DATE_FORMAT関数を使い日付を年月のみにし、CASE式を使ってユーザーのレベルを参照してランク付けを行っています。
このSQLを実行すると、
このように、日付、ユーザー、ランクのリストが表示されました。
次に、ユーザーが重複して表示されているので、これを重複しないように表示します。
SELECT DISTINCT
DATE_FORMAT(startTime, '%Y-%m') AS 日付,
eventlog.userID AS ユーザー,
CASE
WHEN users.level >= 4 THEN '上級者'
WHEN users.level >= 2 THEN '中級者'
ELSE '初級者'
END AS 'ランク'
FROM eventlog
INNER JOIN users ON users.userID = eventlog.userID;
SELECTで抽出するデータを、DISTINCTで重複しないようにします。
このSQLを実行すると、
このように、ユーザーが重複しないようにデータを出力できました。
続いて、クロス集計表を作るためにこのSQLをサブクエリにします。
SELECT 日付,
ユーザー,
ランク
FROM (SELECT DISTINCT
DATE_FORMAT(startTime, '%Y-%m') AS 日付,
eventlog.userID AS ユーザー,
CASE
WHEN users.level >= 4 THEN '上級者'
WHEN users.level >= 2 THEN '中級者'
ELSE '初級者'
SELECT
startTime,
eventlog.userID,
users.level
FROM eventlog
INNER JOIN users ON users.userID = eventlog.userID;
eventlogから、startTime(イベントの開始時間)とeventlogテーブルのuserID、usersテーブルのlevelを取り出し、usersテーブルはusersテーブルのuserIDとeventlogテーブルのuserIDで結合しています。
このSQLを実行すると、
このようになります。
次に、このSQL文を改良してクロス集計を作成していきます。
まずは、日と時間が出力されている日付を年月のみ出力するようにして、ユーザーのレベルごとに初級者、中級者、上級者でグループ分けしていきます。
SELECT
DATE_FORMAT(startTime, '%Y-%m') AS 日付,
eventlog.userID AS ユーザー,
CASE
WHEN users.level >= 4 THEN '上級者'
WHEN users.level >= 2 THEN '中級者'
ELSE '初級者'
END AS 'ランク'
FROM eventlog
INNER JOIN users ON users.userID = eventlog.userID
ORDER BY eventlog.userID;
DATE_FORMAT関数を使い日付を年月のみにし、CASE式を使ってユーザーのレベルを参照してランク付けを行っています。
このSQLを実行すると、
このように、日付、ユーザー、ランクのリストが表示されました。
次に、ユーザーが重複して表示されているので、これを重複しないように表示します。
SELECT DISTINCT
DATE_FORMAT(startTime, '%Y-%m') AS 日付,
eventlog.userID AS ユーザー,
CASE
WHEN users.level >= 4 THEN '上級者'
WHEN users.level >= 2 THEN '中級者'
ELSE '初級者'
END AS 'ランク'
FROM eventlog
INNER JOIN users ON users.userID = eventlog.userID;
SELECTで抽出するデータを、DISTINCTで重複しないようにします。
このSQLを実行すると、
このように、ユーザーが重複しないようにデータを出力できました。
続いて、クロス集計表を作るためにこのSQLをサブクエリにします。
SELECT 日付,
ユーザー,
ランク
FROM (SELECT DISTINCT
DATE_FORMAT(startTime, '%Y-%m') AS 日付,
eventlog.userID AS ユーザー,
CASE
WHEN users.level >= 4 THEN '上級者'
WHEN users.level >= 2 THEN '中級者'
ELSE '初級者'