【SQL】CASE式まとめ 01

前提

• 「達人に学ぶSQL徹底指南書 第2版 ミック著 翔泳社」を自分用にまとめたものです。
• 使用するデータは、MySQL公式のemployeesのデータを使用しています。
  【SQL】MySQL(MariaDB)を使ったSQL学習用のサンプルデータベースの準備

CASE式概要

• CASE式を使いこなせるかが、初級者と中級者の分かれ目
• CASE式はある列の値を別の値に読み替えているだけ
• 他のSQLツール（集約関数等）と組み合わせることで威力を発揮する
• やっかいなバグを防ぐために、ELSE句を必ず書く（ELSE句を省略するとELSE NULL扱いとなる）

CASE式の演習

演習1：単純CASE式と検索CASE式

課題：employeesテーブルを使って下記のデータを表示してください。

• 表示データ
  • emp_no
  • first_name
  • last_name
  • gender Mは「Mail」、Fは「Femail」とCASE式を使って表示

解答：CASE式の書式には、単純CASE式と検索CASE式があります。

• 単純CASE式

SELECT 
   e.emp_no,
   e.first_name,
   e.last_name,
   CASE e.gender
      WHEN 'M' THEN 'Male'
      WHEN 'F' THEN 'Femail'
      ELSE 'Other'
   END AS gender
FROM employees as e

• 検索CASE式

SELECT 
   e.emp_no,
   e.first_name,
   e.last_name,
   CASE 
      WHEN e.gender = 'M' THEN 'Male'
      WHEN e.gender = 'F' THEN 'Femail'
      ELSE 'Other'
   END AS gender
FROM employees as e

単純CASE式はできることも限られるので、以降は検索CASE式を使用していきます。

演習2：新しい階級に変換して集計

課題：salariesテーブルを使って、2000年01月の給料（2000年01月31日支払）を下記の階級(s_class)に分け、階級ごとの人数の合計(cnt)を表示してください。
salariesテーブルの各レコードの有効期間は、from_date以上to_date未満で、毎月salaryの額が給料として支給されるものとします。

■ 階級

■ クエリー結果

解答

SELECT CASE
	WHEN s.salary >= 0 AND s.salary < 50000 THEN 'A'
	WHEN s.salary >= 50000 AND s.salary < 100000 THEN 'B'
	WHEN s.salary >= 100000 AND s.salary < 150000 THEN 'C'
	WHEN s.salary >= 150000 AND s.salary < 200000 THEN 'D'
	ELSE NULL END AS `s_class`,
   COUNT(*) AS `cnt`
FROM salaries AS s
WHERE s.from_date <= '2000-01-31' AND s.to_date > '2000-01-31'
GROUP BY s_class

上記の解答は、MariaDB(MySQL)では動作しますが、GROUP BY句で別名「s_class」が使えないDBもあります。この場合は、下記のようなSQLになります。

SELECT CASE
	WHEN s.salary >= 0 AND s.salary < 50000 THEN 'A'
	WHEN s.salary >= 50000 AND s.salary < 100000 THEN 'B'
	WHEN s.salary >= 100000 AND s.salary < 150000 THEN 'C'
	WHEN s.salary >= 150000 AND s.salary < 200000 THEN 'D'
	ELSE NULL END AS `s_class`,
   COUNT(*) AS `cnt`
FROM salaries AS s
WHERE s.from_date <= '2000-01-31' AND s.to_date > '2000-01-31'
GROUP BY CASE
	WHEN s.salary >= 0 AND s.salary < 50000 THEN 'A'
	WHEN s.salary >= 50000 AND s.salary < 100000 THEN 'B'
	WHEN s.salary >= 100000 AND s.salary < 150000 THEN 'C'
	WHEN s.salary >= 150000 AND s.salary < 200000 THEN 'D'
	ELSE NULL END

演習3：クロス集計クエリ（異なる条件の集計）

課題：employeesテーブルを使って、男女別(sex)・誕生月別(b_month)の人数の合計(cnt_m, cnt_f)を表示してください。

■ クエリ結果

解答

SELECT 
   MONTH(e.birth_date) AS `b_month`,
   -- the number of men
   SUM(CASE WHEN e.gender = 'M' THEN 1 ELSE 0 END) AS cnt_m,
   -- the number of women
   SUM(CASE WHEN e.gender = 'F' THEN 1 ELSE 0 END) AS cnt_f
FROM employees AS e
GROUP BY b_month

初心者はWHERE句で条件分岐させます。中級者以上はSELECT句で分岐させます。

おわりに

SQLはエラーなく実行結果が表示されても、それが自分が思い描いている通りの結果でないことがよくあります。
会社では、1からSQLステートメントを書いたときは、実行結果が正しいデータかを検証しないと不安で仕方ありません。
みなさんはいかがでしょうか？

次は、CASE式後半を掲載する予定です。