SQLのCASE式を使いこなす（サンプルクエリあり） #MySQL

この記事の内容

CASE式とは
CASE式の例を3つ紹介

補足

CASE式は簡易CASE式と検索CASE式の２通り書き方がありますが、より汎用的な検索CASE式で記述します
mysql8.0で動作確認をしています

CASE式ってなに？

CASE式はSQLで条件分岐させ、値を取得するための構文です。

CASE
    WHEN 条件1 THEN 結果1
    WHEN 条件2 THEN 結果2
    ...
    ELSE デフォルト結果
END

汎用性に富んでおり、列名や定数を書ける箇所は常に書くことができます。

SELECT
WHERE
GROUP BY
HAVING
CHECK制約の中
関数、述語の中
...etc

CASE式を駆使することで、可読性を上げられ複数のSQLを一つにまとめることができるため、ぜひ使いこなしていきたいところですね！

とっても便利なCASE式ですが、利用する時は以下のポイントに気をつける必要があります。

ポイント1.式の結果のデータ型を一致させる

CASE式で返すデータは全ての戻り値で型を一致させる必要があります。

-- データ型が異なるNG例
SELECT gender,
	CASE gender
	  WHEN 0 THEN now()-- 日時を返すためNG
	  WHEN 1 THEN '女性'
       ELSE '不明'
	END AS '性別'
FROM users;

ちなみにMySQLではエラーにならず、暗黙の型変換により文字列になります。

しかし、予期せぬ挙動を防ぐため、一貫したデータ型を返すようにしましょう。

ポイント2.ELSE句を必ず書く

ELSE句はCASE式のオプションです。そのため、省略してもエラーにはなりません。その場合、値がNULLで返されます。
しかし、「エラーにはならないけど、結果が違う」のはバグの温床となるため、明示的にELSE句を書くようにしましょう。

SELECT gender,
	CASE
	  WHEN gender = 0 THEN '男性'
          WHEN gender = 1 THEN '女性'
	END AS '性別'-- ELSEを省略
FROM users;

いろんな例

ケース1. 既存のデータを新しい単位に変換する

既存のデータを分析用のデータ体系に変換したい場合があります。たとえば、都道府県単位で保存されている人工を地域単位で集計したい時は以下のように記述します。

-- 県名を地域に集計する
SELECT
    CASE 
        WHEN name IN ('東京', '神奈川', '埼玉') THEN '関東'
        WHEN name IN ('大阪', '奈良') THEN '関西'
        ELSE null
    END AS 地域,
    SUM(population) as 人口
FROM population
GROUP BY 地域;

GROUP BY 地域で、地域ごとに人口を加算しています。SELECT文ですでにCASE式を定義しているので、そちらを再利用しています。
ちなみに以下は同じ結果になります。

--　GROUP BYでCASE式を使う場合
SELECT
    CASE 
        WHEN name IN ('東京', '神奈川', '埼玉') THEN '関東'
        WHEN name IN ('大阪', '奈良') THEN '関西'
        ELSE null
    END AS 地域,
    SUM(population) as 人口
FROM population
GROUP BY
　　　　　　　　CASE 
        WHEN name IN ('東京', '神奈川', '埼玉') THEN '関東'
        WHEN name IN ('大阪', '奈良') THEN '関西'
        ELSE null
    END;

CREATE TABLE population (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
population int
);

INSERT INTO population (name, population) VALUES ('東京', 100);
INSERT INTO population (name, population) VALUES ('神奈川', 90);
INSERT INTO population (name, population) VALUES ('埼玉', 80);
INSERT INTO population (name, population) VALUES ('大阪', 88);
INSERT INTO population (name, population) VALUES ('奈良', 50);
SELECT name as 都道府県, population as 人口 from population;

ケース2. 2つカラムを条件によって選択肢1つのカラムとして問い合わせる

1つのテーブルにあるカラムAとカラムBを条件によってどちらかだけを表示したい場合です。
例として、性別によって取得したいカラムが異なる場合分けを記述します。

性別が男（gender=0）の場合:
- ニックネーム＋「君」
性別が女（gender=1）の場合:
- ニックネーム＋「ちゃん」
性別不明(gender=null)の場合:
- 名前+「さん」

-- CASE式の結果に異なるカラム（nicknameとname）を使う
SELECT gender,
	CASE 
	  WHEN gender = 0 THEN CONCAT(nickname, "君")
          WHEN gender = 1 THEN CONCAT(nickname, "ちゃん")
        ELSE CONCAT(name, "さん")
	END AS 呼称
FROM users;

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `nickname` varchar(50) DEFAULT NULL,
  `gender` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO qiita.users (name,nickname,gender,email,created_at) VALUES
	 ('山田太郎','タロー',0),
	 ('上野のぶこ','のぶ',1),
	 ('クリス・シフトレフト','クリス',NULL);

ケース3. クロス表を作成する

クロス表とは

質問項目を1つの表の表頭と表側に分け、該当する回答数やその合計を記載した表をクロス集計表といいます。

上記のクロス表は年代別にずんだ餅が好きかそうでないかを表しています。（値はダミーです。）
表頭に目的の欲しい情報を、表側に目的を説明するための情報を設定します。

CASE式でクロス表を表現する

例として、くだものの売上を日毎に登録している売上テーブルから、その月にどの果物がいくら売れたかを集計するクロス表を作成します。
表題は「くだもの売上」、表側を「年月」とします。

--「年月毎における商品ごとの売上」のクロス集計表を出力
SELECT
    date_format(saleDate, '%Y-%m') as '年月',
    CONCAT(SUM(CASE WHEN productName = 'りんご' THEN price ELSE 0 END), "円") AS 'りんごの売上',
    CONCAT(SUM(CASE WHEN productName = 'みかん' THEN price ELSE 0 END),"円") AS 'みかんの売上',
    CONCAT(SUM(price), "円") AS '売上合計'
FROM sales
GROUP BY 
     date_format(saleDate, '%Y-%m');

まとめ

CASE式のポイントと使用例をまとめました。
他に便利な使い方があれば、教えていただきたいです！

参考文献

達人に学ぶSQL指南書

おまけ

デモ用DDLとDML

-- テーブルの作成
CREATE TABLE sales
(
	id CHAR(3)  PRIMARY KEY,
	productName VARCHAR(16),
	price       INTEGER NOT NULL,
	saleDate    DATE NOT NULL
);

-- データの登録
INSERT INTO sales VALUES('001','りんご','10','2019-01-01');
INSERT INTO sales VALUES('002','りんご','20','2019-01-02');
INSERT INTO sales VALUES('003','りんご','30','2019-01-03');
INSERT INTO sales VALUES('004','みかん','40','2019-01-04');
INSERT INTO sales VALUES('005','みかん','50','2019-01-05');
INSERT INTO sales VALUES('006','みかん','60','2019-01-06');
INSERT INTO sales VALUES('007','りんご','70','2019-02-01');
INSERT INTO sales VALUES('008','りんご','80','2019-02-02');
INSERT INTO sales VALUES('009','りんご','90','2019-02-03');
INSERT INTO sales VALUES('010','みかん','100','2019-02-04');
INSERT INTO sales VALUES('011','みかん','110','2019-02-05');
INSERT INTO sales VALUES('012','りんご','120','2020-01-01');
INSERT INTO sales VALUES('013','りんご','130','2020-01-02');
INSERT INTO sales VALUES('014','みかん','140','2020-01-03');
INSERT INTO sales VALUES('015','みかん','150','2020-01-04');