皆さんMySQL使っていますか？この記事では入門記事の中でも少し応用的な内容を扱います。
今回のテーマはCASE式。使いこなせるようになると、様々な場面で複雑な処理を任せることのできる便利な式です。さっそくいってみましょう！
そもそもCASE式とは
まずは、そもそもCASE式とはなんなのかをみていきます。CASE式の書式は次のようになります。
WHEN 条件2 THEN 表示2
……
ELSE 表示その他
END
CASEは条件に合致した時、指定した表示を返します。条件1に合致したら表示1を、条件2に合致したら表示2を、という具合ですね。すべての条件に当てはまらない場合は、ELSEで指定した表示その他が使われます。
ELSEの部分は省略可能ですが、エラーの原因になるため、設定しておくべきです。では、実際にテーブルを使って操作を体験してみましょう。そのためのテーブルをまずは準備します。下のクエリを実行してテーブルを準備しておいてください。
CREATE DATABASE case_db;
USE case_db;
CREATE TABLE name_dept_gender_score(
id INT(11) AUTO_INCREMENT NOT NULL,
name VARCHAR(30) NOT NULL,
dept INT(2) NOT NULL,
gender INT(1) NOT NULL,
score INT(3) NOT NULL,
PRIMARY KEY (id));
INSERT INTO name_dept_gender_score (name, dept, gender, score)VALUES
('タロー', 1, 1, 95),
('ジロー', 2, 1, 50),
('サブロー', 1, 1, 75),
('ハナコ', 3, 2, 100),
('ヨシコ', 1, 2, 25),
('ナンシー', 2, 2, 60),
('カルボナーラ', 3, 3, 85)
;
DESC name_dept_gender_score;
SELECT * FROM name_dept_gender_score;
実行結果
+--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | NULL | | | dept | int(2) | NO | | NULL | | | gender | int(1) | NO | | NULL | | | score | int(3) | NO | | NULL | | +--------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) +----+--------------------+------+--------+-------+ | id | name | dept | gender | score | +----+--------------------+------+--------+-------+ | 1 | タロー | 1 | 1 | 95 | | 2 | ジロー | 2 | 1 | 50 | | 3 | サブロー | 1 | 1 | 75 | | 4 | ハナコ | 3 | 2 | 100 | | 5 | ヨシコ | 1 | 2 | 25 | | 6 | ナンシー | 2 | 2 | 60 | | 7 | カルボナーラ | 3 | 3 | 85 | +----+--------------------+------+--------+-------+ 7 rows in set (0.00 sec)
このようなテーブルができていればまずはオッケーです。
CASEの簡単な例：成績を優良可不可にわける
この章では前章で作ったテーブルを使い、成績ごとに優良可不可をつけたテーブルを作っていきます。
ゴールのイメージはこのような感じです。
+----+--------------------+------+--------+-------+--------+ | id | name | dept | gender | score | 評価 | +----+--------------------+------+--------+-------+--------+ | 1 | タロー | 1 | 1 | 95 | 優 | | 2 | ジロー | 2 | 1 | 50 | 不可 | | 3 | サブロー | 1 | 1 | 75 | 良 | | 4 | ハナコ | 3 | 2 | 100 | 優 | | 5 | ヨシコ | 1 | 2 | 25 | 不可 | | 6 | ナンシー | 2 | 2 | 60 | 可 | | 7 | カルボナーラ | 3 | 3 | 85 | 優 | +----+--------------------+------+--------+-------+--------+ 7 rows in set (0.01 sec)
まずは既存の知識で作れる部分を作ってしまいましょう。このような表を出したい場合、SELECT文を使えばscoreから左の部分は簡単に作ることが可能ですね。
SELECT id, name, dept, gender, score FROM name_dept_gender_score;
実行結果
+----+--------------------+------+--------+-------+ | id | name | dept | gender | score | +----+--------------------+------+--------+-------+ | 1 | タロー | 1 | 1 | 95 | | 2 | ジロー | 2 | 1 | 50 | | 3 | サブロー | 1 | 1 | 75 | | 4 | ハナコ | 3 | 2 | 100 | | 5 | ヨシコ | 1 | 2 | 25 | | 6 | ナンシー | 2 | 2 | 60 | | 7 | カルボナーラ | 3 | 3 | 85 | +----+--------------------+------+--------+-------+ 7 rows in set (0.00 sec)
あとは、先程紹介したCASE式を組み込んでいけばゴールを実現できそうです。評価について、まずは基準を決めておきましょう。今回は次の基準で優良可不可を判定します。
- 優：80点以上
- 良：70点以上80点未満
- 可：60点以上70点未満
- 不可：60点未満
- 未評価：それ以外の値
では、この条件を式に起こしてみましょう。優は80点以上なので、式にすると次のようになります。
score >= 80
不可も同じように
60 > score
このことから良は80 > score >= 70 とやりたくなってしまいますが、MySQLでは比較演算子は1つずつ処理しなければいけないので、次のようになります。
80 > score AND score >= 70
可も同じように考えて
70 > score AND score >= 60
これらを先程のCASE式の書式に当てはめると次のようになります。
CASE WHEN score >= 80 THEN 表示1
WHEN 80 > score AND score >= 70 THEN 表示2
WHEN 70 > score AND score >= 60 THEN 表示3
WHEN 60 > score THEN 表示4
ELSE 表示その他
END
あとは表示部分を優良可不可に書き換えて、エイリアスをつけてあげればCASE式は完成です。
CASE WHEN score >= 80 THEN "優"
WHEN 80 > score AND score >= 70 THEN "良"
WHEN 70 > score AND score >= 60 THEN "可"
WHEN 60 > score THEN "不可"
ELSE "未測定"
END AS "評価"
では、CASE式を組み込んだSELECT文全体をみてみましょう。
SELECT id, name, dept, gender, score ,
CASE WHEN score >= 80 THEN "優"
WHEN 80 > score AND score >= 70 THEN "良"
WHEN 70 > score AND score >= 60 THEN "可"
WHEN 60 > score THEN "不可"
ELSE "未測定"
END AS "評価"
FROM name_dept_gender_score;
これを実行した結果がこちらになります。
+----+--------------------+------+--------+-------+--------+ | id | name | dept | gender | score | 評価 | +----+--------------------+------+--------+-------+--------+ | 1 | タロー | 1 | 1 | 95 | 優 | | 2 | ジロー | 2 | 1 | 50 | 不可 | | 3 | サブロー | 1 | 1 | 75 | 良 | | 4 | ハナコ | 3 | 2 | 100 | 優 | | 5 | ヨシコ | 1 | 2 | 25 | 不可 | | 6 | ナンシー | 2 | 2 | 60 | 可 | | 7 | カルボナーラ | 3 | 3 | 85 | 優 | +----+--------------------+------+--------+-------+--------+ 7 rows in set (0.01 sec)
当初のゴールを達成することができましたね。
CASEの応用例：男女の人数を部署ごとにカウント
今度は少し応用として、部署ごとに男女が何人いるかをカウントするクエリを考えてみましょう。完成イメージは次のとおりです。
+------+-----------------------+-----------------------+-----------------------------+ | dept | 男性社員の人数 | 女性社員の人数 | その他の社員の人数 | +------+-----------------------+-----------------------+-----------------------------+ | 1 | 2 | 1 | 0 | | 2 | 1 | 1 | 0 | | 3 | 0 | 1 | 1 | +------+-----------------------+-----------------------+-----------------------------+
これを実現するにはSUM関数とGROUP BYの知識が必要になります。
SUM関数とは
SUM関数はExcelなどでもおなじみの和を取る関数です。書式は次のようになります。
- SUM（カラム名や式）
簡単な例として、参加者のテストの合計点を計算してみましょう。クエリは次のようになります。
SELECT SUM(score) FROM name_dept_gender_score;
実行結果
+------------+ | SUM(score) | +------------+ | 490 | +------------+ 1 row in set (0.00 sec)
上の書式で説明したように、SUM関数は引数にカラム名だけでなく、式も設定することができます。そこで、SUM関数とCASE式を組み合わせてみましょう。genderが1の社員に1、それ以外を0とするようなCASE式を作ってみます。
SELECT CASE WHEN gender = "1" THEN 1 ELSE 0 END FROM name_dept_gender_score;
実行結果
+------------------------------------------+ | CASE WHEN gender = "1" THEN 1 ELSE 0 END | +------------------------------------------+ | 1 | | 1 | | 1 | | 0 | | 0 | | 0 | | 0 | +------------------------------------------+ 7 rows in set (0.01 sec)
これをSUM関数で合計することで、テーブル内の男性社員の数を調べることができます。
SELECT SUM(CASE WHEN gender = "1" THEN 1 ELSE 0 END) FROM name_dept_gender_score;
実行結果
+-----------------------------------------------+ | SUM(CASE WHEN gender = "1" THEN 1 ELSE 0 END) | +-----------------------------------------------+ | 3 | +-----------------------------------------------+ 1 row in set (0.00 sec)
同様に考えて、女性社員、その他の社員の人数も調べることができます。
クエリ
SELECT SUM(CASE WHEN gender = "1" THEN 1 ELSE 0 END),
SUM(CASE WHEN gender = "2" THEN 1 ELSE 0 END),
SUM(CASE WHEN gender = "3" THEN 1 ELSE 0 END)
FROM name_dept_gender_score;
+-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+ | SUM(CASE WHEN gender = "1" THEN 1 ELSE 0 END) | SUM(CASE WHEN gender = "2" THEN 1 ELSE 0 END) | SUM(CASE WHEN gender = "3" THEN 1 ELSE 0 END) | +-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+ | 3 | 3 | 1 | +-----------------------------------------------+-----------------------------------------------+-----------------------------------------------+ 1 row in set (0.01 sec)
部署をまたがないで、全体で男性女性その他の集計を取ることができました。
GROUP BYとは
GROUP BYはカラム名を基準にテーブルをグループ化する事ができます。GROUP BYは次のような書式を取ります。
- SELECT*FROM テーブル名 GROUP BY カラム名;
たとえば部門ごとにテストの合計点を計算するには次の式を使います。
SELECT dept, SUM(score) FROM name_dept_gender_score GROUP BY dept;
実行結果
+------+------------+ | dept | SUM(score) | +------+------------+ | 1 | 195 | | 2 | 110 | | 3 | 185 | +------+------------+ 3 rows in set (0.00 sec)
男女の人数を部署ごとにカウントするクエリ
さて、これまでの知識を組み合わせてみましょう。クエリは次のようになります。
SELECT dept,
SUM( CASE WHEN gender = "1" THEN 1 ELSE 0 END) AS "男性社員の人数",
SUM( CASE WHEN gender = "2" THEN 1 ELSE 0 END) AS "女性社員の人数",
SUM( CASE WHEN gender = "3" THEN 1 ELSE 0 END) AS "その他の社員の人数"
FROM name_dept_gender_score
GROUP BY dept;
+------+-----------------------+-----------------------+-----------------------------+ | dept | 男性社員の人数 | 女性社員の人数 | その他の社員の人数 | +------+-----------------------+-----------------------+-----------------------------+ | 1 | 2 | 1 | 0 | | 2 | 1 | 1 | 0 | | 3 | 0 | 1 | 1 | +------+-----------------------+-----------------------+-----------------------------+ 3 rows in set (0.01 sec)
想定通り、部門ごとの男女その他の社員数が得られましたね！
まとめ
いかがでしたか？今回はCASE式について、基礎的な部分から簡単な例、応用的な例を紹介してきました。データベース業界の巨人、J.セルコの言葉によれば、CASE式は「SQL-92（1992年に策定されたSQLの規格） で追加された中で最も有用かもしれない」というくらい有用な道具です。
使いこなせればSQLでできることがグッと広がるので、ぜひいろいろな例に挑戦して、CASE式の運用能力を高めていってください。この記事が皆様のMySQL学習の一助となれば幸いです。
