CASE式を使った条件分岐でSQLを極める！

SQLの条件分岐を記述するための式です。

どんな時に、CASE式が使えるのか？

使用例も載せてます！

現場で使える箇所があるかも？

プログラミング言語のif-else分と同じと考えればかなり重要です。

しかし、実際の現場ではあまり使われてません。（私が今ままで体験した案件では…かもしれませんが）

CASE式を使いこなせれば、かなり幅が広がります。

一緒にCASE式をマスターしましょう！

CASE式の基礎

まず、書き方から学習です。

CASE式の書き方には2通りあります。

どちらも同じように動作しますが、後者の「検索CASE式」を使うことが多いです。

単純CASE式

CASE color
  WHEN 'red' THEN '赤'
  WHEN 'blue' THEN '青'
ELSE 'その他' END

検索CASE式

CASE
  WHEN color = 'red' THEN '赤'
  WHEN color = 'blue' THEN '青'
ELSE 'その他' END

CASE式の注意点

1. 条件に一致した時点で後続の条件は無視（評価されない）される

この注意点は、絶対に覚えましょう！

思わぬところでバグの元になります。

例えば以下のような条件の場合、「2のみ」は絶対に出力されません！

最初の条件に一致したことで、後続の処理が無視されるためです。

CASE
  WHEN code in (1,2) THEN '1か2'
  WHEN code in (2) THEN '2のみ'
ELSE 'その他' END

2. データ方を統一する

全ての条件分岐のデータ型は一致している必要があります。

3. ENDの書き忘れ

書き忘れると構文エラーになるのですぐ気づくはずです。

構文エラーになったら、まず確認しましょう。

4. ELSE文を必ず書く

書かなくても構文エラーにはなりません。

ELSEがない場合は、暗黙的に「ELSE NULL」扱いになります。

これも覚えましょう！

「思った結果と違う…」場合は、ELSE文を書いてないことが原因の可能性があります！

バグに気づきにくいのも厄介な点です。

レビューするときは要注意！

CASE式の使用例1：新しい体系に変換する

前提条件：年齢と名前があるテーブル

やりたいこと：年齢のタイプごとに集計したい

create temp table temptbl (
  id integer unique,
  name varchar(10),
  age INTEGER
);

insert into temptbl values (1, '太郎', 0);
insert into temptbl values (2, '次郎', 18);
insert into temptbl values (3, '三郎', 30);
insert into temptbl values (4, '四郎', 60);
insert into temptbl values (5, '吾郎', 61);

以下のようなテーブルがあったとします。

これを、

「0~18」を子供。

「19<30」を青年。

「31<60」を中年。

「61以上」を老年。

と分類分けして、集計したい場合にCASE式が使えます！

select 
 CASE 
 	WHEN age < 18 THEN '子供'
 	WHEN age < 30 THEN '青年'
 	WHEN age < 60 THEN '中年'
 	ELSE '老年'
 	END,
  count (id)
from
 temptbl
GROUP BY
	 CASE 
 	WHEN age < 18 THEN '子供'
 	WHEN age < 30 THEN '青年'
 	WHEN age < 60 THEN '中年'
 	ELSE '老年'
 	END

Group byにcase式の内容をそのままペタ！

postgresqlであれば、エイリアスを使えばもっとスッキリかけます！

＊使えないDBMSもあります。その場合は↑ので諦めてください…

select 
 CASE 
 	WHEN age < 18 THEN '子供'
 	WHEN age < 30 THEN '青年'
 	WHEN age < 60 THEN '中年'
 	ELSE '老年'
 	END AS age_type,
  count (id)
from
 temptbl
GROUP BY age_type

結果は、こう！

プログラミング上で、for文とか回さずにSQLのみで完結できます！

CASE式の使用例2：異なる条件の集計を1つのSQLで行う

前提条件：地域名、人数、性別が入ったテーブルがある

やりたいこと：地域毎、さらに性別で分けて集計したい

create temp table area (
  id integer unique,
  area_name varchar(10),
  count INTEGER,
  sex INTEGER
);

insert into area values (1, 'A地域', 10, 1);
insert into area values (2, 'A地域', 1, 0);
insert into area values (3, 'B地域', 20, 1);
insert into area values (4, 'B地域', 2, 0); 
insert into area values (5, 'C地域', 30, 1);
insert into area values (6, 'C地域', 3, 0); 

以下のようなテーブルがあったとします。

これを、地域毎、性別で集計したい場合もCASE式で使えます。

SELECT 
	area_name,
	SUM(CASE WHEN sex=1 then count else 0 end) as man_count,
	SUM(CASE WHEN sex=0 then count else 0 end) as woman_count
from area
GROUP BY area_name;

これを実行すると以下のようになります。

肝は、SUM関数です。

これがない状態で実行すると、以下のようになります。

SUM関数が必須の理由がわかるはずです。

SELECT 
	area_name,
	CASE WHEN sex=1 then count else 0 end as man_count,
	CASE WHEN sex=0 then count else 0 end as woman_count
from area

CASE式の使用例3：AND条件よりも緩い制約で抽出する

前提条件：「CASE式の使用例2」と同じテーブル

やりたいこと：女性または、男性だったら人数が10人以上のユーザーを抽出したい

抽出した結果は以下です。

ANDで出力すると上手くいきません。

なぜなら、「男性だったら人数が10人以上のユーザーを抽出したい」この部分しか満たせないから。

SELECT 
	*
from area
WHERE 
	sex =1 AND count <= 10

こうすると、一回のSQLで取得できます。

キーは、CASE式を入れ後にしているところです！

こうすることで、SQLを2回発行してプログラム側で整形する…などをしなくて済みます！

SELECT 
	*
from area
WHERE case When sex = 1
		THEN Case WHEN count <= 10
			THEN TRUE else FALSE END
	else TRUE end 

CASE式の使用例4：キーの入れ替えをUPDATE文1回で行う

前提条件：「CASE式の使用例2」と同じテーブル。

やりたいこと：sexのキーを「0：女性」「1：男性」としてたところを逆にしたい。

考えられるのが、一度退避させる方法です。

1. 「0：女性」→「3:女性」にupdate

2. 「1：男性」→「0：男性」にupdate

3. 「3：女性」→「1:女性」にupdate

ただ、このやり方だと3回もupdateを打たないといけないです。

CASE式を使うと一回で行けます！

キーポイントは、「ELSE sex」の部分です。

このELSEがないと条件に一致しないデータは、NULLになるので注意です。

update area 
SET sex = CASE WHEN sex = 1 THEN 0
				WHEN sex = 0 THEN 1
				ELSE sex
END

UPDATEを1つにまとめることで、更新が一気に行われるので主キーの重複によるエラーも回避できる場合もあります！

＊DBMSによる…ポスグレちゃんはダメぽい…。今回はキー制約つけてないから出来てるけど

CASE式の使用例5：テーブル同士をマッチングしてクロス表を作成する

前提条件：科目毎のマスターテーブルと、タイムスケジュールテーブルがある状態

やりたいこと：縦に科目、横に「月〜日」までに時間割を作成する

テーブルの状態

create temp table subject (
	id INTEGER unique,
	subject_id INTEGER,
	subject_name text
)


insert INTO subject values (1, 1, '国語');
insert INTO subject values (2, 2, '数学');
insert INTO subject values (3, 3, '英語');
insert INTO subject values (4, 4, '歴史');
insert INTO subject values (5, 5, '物理');



create temp table timetable (
	id INTEGER unique,
	day_of_week text,
	subject_id INTEGER
)

insert into timetable values (1, '月', 1);
insert into timetable values (2, '火', 1);
insert into timetable values (3, '水', 1);
insert into timetable values (4, '木', 1);
insert into timetable values (5, '金', 1);
insert into timetable values (6, '月', 2);
insert into timetable values (7, '火', 3);
insert into timetable values (8, '水', 4);
insert into timetable values (9, '木', 5);
insert into timetable values (10, '金', 3);
insert into timetable values (11, '月', 3);
insert into timetable values (12, '火', 2);
insert into timetable values (13, '水', 4);
insert into timetable values (14, '木', 4);
insert into timetable values (15, '金', 5);

表示したい結果

…国語多いーーー。。。

SQLで表示する方法

CASE式のなかで、IN、EXISTSが使えます！

＝サブクエリも使えます。

なので、こういう書き方も出来ます！

SELECT 
	subject_name, 
	CASE WHEN subject_id IN (SELECT subject_id from timetable where day_of_week='月') THEN '⚪︎' ELSE '-' END AS "月曜日",
	CASE WHEN subject_id IN (SELECT subject_id from timetable where day_of_week='火') THEN '⚪︎' ELSE '-' END AS "火曜日",
	CASE WHEN subject_id IN (SELECT subject_id from timetable where day_of_week='水') THEN '⚪︎' ELSE '-' END AS "水曜日",
	CASE WHEN subject_id IN (SELECT subject_id from timetable where day_of_week='木') THEN '⚪︎' ELSE '-' END AS "木曜日",
	CASE WHEN subject_id IN (SELECT subject_id from timetable where day_of_week='金') THEN '⚪︎' ELSE '-' END AS "金曜日",
	CASE WHEN subject_id IN (SELECT subject_id from timetable where day_of_week='土') THEN '⚪︎' ELSE '-' END AS "土曜日",
	CASE WHEN subject_id IN (SELECT subject_id from timetable where day_of_week='日') THEN '⚪︎' ELSE '-' END AS "日曜日"
from subject;

EXISTSでも同様にかけます！

さらにEXISTSの方がパフォーマンスもいいです。

ここでは記載しませんが、気になる方は書いてみてください！

↑に一時テーブルとinsert文を書いてますので、そちらで試せるはずです。

まとめ

こちらの本で学習してます！

今回は、CASE式について書かれている内容を

自分なりにアレンジしつつ、

SQLを書きながら実行しました！

かなり勉強になります！

他にもパフォーマンス向上させる方法やウインドウ関数などの未学習の部分や

SQLのベストプラクティスが書かれているので、持ってない方は是非お手元にご用意くださいw