イチから学ぶデータベース・SQL（１０）
公開日: 2022/12/26
更新日: 2022/12/26
イチから学ぶデータベース・SQL（９）では、サブクエリについて学習していきました。
今回も、これまでと同様paizaラーニングを参考に、オンラインRPGを題材としてSQLについて学習していきます。
今回も、これまでと同様paizaラーニングを参考に、オンラインRPGを題材としてSQLについて学習していきます。
1. 前回の振り返りと今回学習する内容
前回は、サブクエリを用いて日ごとのアクティブユーザー数と、月ごとのアクティブユーザー数を求めました。
サブクエリはSQLの中にSQLを書く事を指し、このように記述します。
(WHERE句で使う場合)
SELECT カラム
FROM テーブル
WHERE カラム名 = (SELECT カラム
FROM テーブル
WHERE カラム名 = 値)
実行順序は、まずカッコ内のSQLを実行し、次にカッコ内の値をもとに、カッコ外のSQLを実行します。
今回は、前回の最後に少しだけ触れましたが、CASE式について学習していきます。
なお、今回使用するサンプルデータベースは前回までと同じようにこちらです。
サブクエリはSQLの中にSQLを書く事を指し、このように記述します。
(WHERE句で使う場合)
SELECT カラム
FROM テーブル
WHERE カラム名 = (SELECT カラム
FROM テーブル
WHERE カラム名 = 値)
実行順序は、まずカッコ内のSQLを実行し、次にカッコ内の値をもとに、カッコ外のSQLを実行します。
今回は、前回の最後に少しだけ触れましたが、CASE式について学習していきます。
なお、今回使用するサンプルデータベースは前回までと同じようにこちらです。
2. CASE式とは？
CASE式とは、条件分岐をするための命令の事をいい、単純CASE式と検索CASE式があります。
単純CASE式は、このように記述します。
SELECT (カラム名)
CASE
WHEN (条件) THEN (条件を満たしている場合に表示する内容)
ELSE (条件を満たしていなかった場合に表示する内容)
END
FROM (テーブル名);
検索CASE式は、このように記述します。
SELECT (カラム名)
CASE
WHEN (カラム名) = (条件) THEN (条件を満たしている場合に表示する内容)
ELSE (条件を満たしていなかった場合に表示する内容)
END
FROM (テーブル名);
単純CASE式と検索CASE式、どちらにもメリットとデメリットがあります。
単純CASE式は、SQL文自体をシンプルにできますが、検索CASE式ほど応用が効きません。
検索CASE式は、単純CASE式で求められる結果は検索CASE式でも求められ、「=」以外の演算子も使用できるため単純CASE式よりも応用が効きますが、可読性が低くなる可能性があります。
単純CASE式は、このように記述します。
SELECT (カラム名)
CASE
WHEN (条件) THEN (条件を満たしている場合に表示する内容)
ELSE (条件を満たしていなかった場合に表示する内容)
END
FROM (テーブル名);
検索CASE式は、このように記述します。
SELECT (カラム名)
CASE
WHEN (カラム名) = (条件) THEN (条件を満たしている場合に表示する内容)
ELSE (条件を満たしていなかった場合に表示する内容)
END
FROM (テーブル名);
単純CASE式と検索CASE式、どちらにもメリットとデメリットがあります。
単純CASE式は、SQL文自体をシンプルにできますが、検索CASE式ほど応用が効きません。
検索CASE式は、単純CASE式で求められる結果は検索CASE式でも求められ、「=」以外の演算子も使用できるため単純CASE式よりも応用が効きますが、可読性が低くなる可能性があります。
3. レベルごとにユーザーをグループ分けしてみる
まずは、CASE式を使ってレベルごとに初級者・上級者としてグループ分けを行っていきます。
ここでは、レベルが3以上のユーザーを上級者、それ未満のユーザーを初級者としてグループ分けを行います。
SQLはこのように記述します。
SELECT
userID,
level,
CASE
WHEN level >= 3 THEN '上級者'
ELSE '初級者'
END AS クラス
FROM
users;
usersテーブルからuserIDとlevelを取り出し、CASE式を使って条件分岐を行い、levelが3以上なら上級者、それ以外を初級者として表示しています。
このSQLを実行すると、
このように、初級者と上級者でグループ分けをすることができました。
また、CASE式の中で条件を提示しているWHENの行には、いくつでも条件を追加することができます。
例えば、レベルが5以上のユーザーを上級者、レベルが3以上のユーザーは中級者、それ以外のユーザーを初級者としてグループ分けしてみます。
SQLはこのように記述します。
SELECT
userID,
level,
CASE
WHEN level >= 5 THEN '上級者'
WHEN level >= 3 THEN '中級者'
ELSE '初級者'
END AS クラス
FROM
users;
このSQLを実行すると、
このように、初級者・中級者・上級者の3パターンでグループ分けすることができました。
なお、このCASE式を使った条件分岐の記述では順番に注意すべき点があります。
例えば、上記のSQL文で条件を指定する際に
SELECT
userID,
level,
CASE
WHEN level >= 3 THEN '中級者'
WHEN level >= 5 THEN '上級者'
ELSE '初級者'
END AS クラス
FROM
users;
このように、WHEN level >= 3 THEN '中級者'を上にして書くと、
このように、上級者が表示されなくなってしまいます。
これはCASE式の特徴として、上のWHENの条件で検索し、全てヒットした場合に、下のWHENの条件はスキップされてしまうからです。
levelが3以上のユーザーは全て中級者であると検索結果で全てヒットしたので、残りの条件はスキップされて初級者と中級者のみが表示されてしまいます。
CASE式を使う際は、このような事にも注意しなければなりません。
ここでは、レベルが3以上のユーザーを上級者、それ未満のユーザーを初級者としてグループ分けを行います。
SQLはこのように記述します。
SELECT
userID,
level,
CASE
WHEN level >= 3 THEN '上級者'
ELSE '初級者'
END AS クラス
FROM
users;
usersテーブルからuserIDとlevelを取り出し、CASE式を使って条件分岐を行い、levelが3以上なら上級者、それ以外を初級者として表示しています。
このSQLを実行すると、
このように、初級者と上級者でグループ分けをすることができました。
また、CASE式の中で条件を提示しているWHENの行には、いくつでも条件を追加することができます。
例えば、レベルが5以上のユーザーを上級者、レベルが3以上のユーザーは中級者、それ以外のユーザーを初級者としてグループ分けしてみます。
SQLはこのように記述します。
SELECT
userID,
level,
CASE
WHEN level >= 5 THEN '上級者'
WHEN level >= 3 THEN '中級者'
ELSE '初級者'
END AS クラス
FROM
users;
このSQLを実行すると、
このように、初級者・中級者・上級者の3パターンでグループ分けすることができました。
なお、このCASE式を使った条件分岐の記述では順番に注意すべき点があります。
例えば、上記のSQL文で条件を指定する際に
SELECT
userID,
level,
CASE
WHEN level >= 3 THEN '中級者'
WHEN level >= 5 THEN '上級者'
ELSE '初級者'
END AS クラス
FROM
users;
このように、WHEN level >= 3 THEN '中級者'を上にして書くと、
このように、上級者が表示されなくなってしまいます。
これはCASE式の特徴として、上のWHENの条件で検索し、全てヒットした場合に、下のWHENの条件はスキップされてしまうからです。
levelが3以上のユーザーは全て中級者であると検索結果で全てヒットしたので、残りの条件はスキップされて初級者と中級者のみが表示されてしまいます。
CASE式を使う際は、このような事にも注意しなければなりません。
4. グループ分けしたクラスごとにユーザー数をカウントしてみる
続いて、初級者・中級者・上級者でグループ分けしたクラスごとにユーザー数をそれぞれカウントしていきます。
CASE式を記述した後にCOUNT関数を記述し、最後にGROUP BYで纏めることでユーザー数を求めます。
SQLはこのように記述します。
SELECT
CASE
WHEN level >= 5 THEN '上級者'
WHEN level >= 3 THEN '中級者'
ELSE '初級者'
END AS クラス,
COUNT(*) AS ユーザー数
FROM
users
GROUP BY クラス;
このSQL文を実行すると、
このように、クラスごとのユーザー数が表示されました。
CASE式を記述した後にCOUNT関数を記述し、最後にGROUP BYで纏めることでユーザー数を求めます。
SQLはこのように記述します。
SELECT
CASE
WHEN level >= 5 THEN '上級者'
WHEN level >= 3 THEN '中級者'
ELSE '初級者'
END AS クラス,
COUNT(*) AS ユーザー数
FROM
users
GROUP BY クラス;
このSQL文を実行すると、
このように、クラスごとのユーザー数が表示されました。
5. ユーザーの所持金を調べてみる
最後に、ユーザーが現在所持しているゴールドを調べてみます。
まずは、ユーザーIDとゴールドをusersテーブルから表示してみます。
SELECT userID, gold
FROM users;
このSQLを実行すると、
このように表示されました。
では次に、ゴールドを3000以上持っているユーザーを大金持ち、ゴールドを1000以上持っているユーザーを中金持ち、ゴールドを1以上持っているユーザーを小金持ち、それ以外を無一文として表示してみます。
SQLはこのように記述します。
SELECT
userID,
gold,
CASE
WHEN gold >= 3000 THEN '大金持ち'
WHEN gold >= 1000 THEN '中金持ち'
WHEN gold >= 1 THEN '小金持ち'
ELSE '無一文'
END AS ゴールド所持状況
FROM users;
このSQLを実行すると、
このようにゴールドの所持状況でグループ分けすることができました。
続いては、どのゴールドの所持状況が1番多いのかをカウントしていきたいと思います。
SQLはこのように記述します。
SELECT
CASE
WHEN gold >= 3000 THEN '大金持ち'
WHEN gold >= 1000 THEN '中金持ち'
WHEN gold >= 1 THEN '小金持ち'
ELSE '無一文'
END AS ゴールド所持状況,
COUNT(*) AS ユーザー数
FROM
users
GROUP BY ゴールド所持状況;
クラスごとのカウントと同じように、COUNT関数を使用してGROUP BYでひとまとめにしています。
このSQLを実行すると、
このように、ゴールド所持状況別のユーザー数が求められました。
まずは、ユーザーIDとゴールドをusersテーブルから表示してみます。
SELECT userID, gold
FROM users;
このSQLを実行すると、
このように表示されました。
では次に、ゴールドを3000以上持っているユーザーを大金持ち、ゴールドを1000以上持っているユーザーを中金持ち、ゴールドを1以上持っているユーザーを小金持ち、それ以外を無一文として表示してみます。
SQLはこのように記述します。
SELECT
userID,
gold,
CASE
WHEN gold >= 3000 THEN '大金持ち'
WHEN gold >= 1000 THEN '中金持ち'
WHEN gold >= 1 THEN '小金持ち'
ELSE '無一文'
END AS ゴールド所持状況
FROM users;
このSQLを実行すると、
このようにゴールドの所持状況でグループ分けすることができました。
続いては、どのゴールドの所持状況が1番多いのかをカウントしていきたいと思います。
SQLはこのように記述します。
SELECT
CASE
WHEN gold >= 3000 THEN '大金持ち'
WHEN gold >= 1000 THEN '中金持ち'
WHEN gold >= 1 THEN '小金持ち'
ELSE '無一文'
END AS ゴールド所持状況,
COUNT(*) AS ユーザー数
FROM
users
GROUP BY ゴールド所持状況;
クラスごとのカウントと同じように、COUNT関数を使用してGROUP BYでひとまとめにしています。
このSQLを実行すると、
このように、ゴールド所持状況別のユーザー数が求められました。