SQLのCASE式はラベルの読み替え！

数ヶ月前に『達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ』を読みました。
CASE式は条件分岐で使いますが、それだけではなく、CASE式の真価は、他のSQLのツールと組み合わせたときに発揮されます。紹介されているのは、コード体系の最分類、行列変換、制約との組み合わせ、集約結果に対する条件分岐ですね。

CASE式はSQLの宣言的プログラミングを支える生命線の1つです。
式であるがゆえに、CASE式は実行時には評価されて1つの値に定まります。
CASE式は列名や定数を書ける場所には常に書くことができます。

達人に学ぶSQL徹底指南書 第2版 CASE式のススメ

OracleではDECODEが似たような使い方をしますが、Oracle依存だけでなく、引数に述語が使えない、サブクエリを引数に取れないなど記述力も劣っているのでCASE式を使いましょう。
※ Oracle で確認しています。

CASE式の種類と構文

CASE式には、単純CASE式と検索CASE式があります。

単純CASE式

単純CASE式は、1つの式を評価し、その結果に基づいて異なる値を返すものです。
単純CASE”式”の構文は次のようになります。イメージとしては、単一の列を評価して、別の単一の列を出力するような感じでしょうか。

-- 1つの属性を評価して別の値を出力する
CASE column_name
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

以下の例は、department_id の値に応じてdepartment_nameを出力する例ですが、別の言い方をすればdepartment_id の値をその値に応じて、別の値(ラベル)に張り替えるということもできるかと思います。

-- department_id の値に応じてdepartment_nameを出力する
SELECT 
    employee_id,
    first_name,
    last_name,
    department_id,
    CASE department_id
        WHEN 10 THEN 'Administration'
        WHEN 20 THEN 'Marketing'
        WHEN 30 THEN 'Purchasing'
        WHEN 40 THEN 'Human Resources'
        ELSE 'Other'
    END AS department_name
FROM 
    employees;

検索CASE式

検索CASE式は、複数の条件を評価し、その条件に基づいて異なる値を返すものです。
検索CASE”式”の構文は次のようになります。

-- 条件に応じて結果を出力する
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

これも本質的には単純CASE式と同じで、これも条件に応じて別の値へとラベルの張り替えをしているだけですね。

SELECT 
    employee_id,
    first_name,
    last_name,
    salary,
    CASE
        WHEN salary < 3000 THEN 'Low'
        WHEN salary >= 3000 AND salary < 7000 THEN 'Medium'
        WHEN salary >= 7000 THEN 'High'
        ELSE 'Unknown'
    END AS salary_category
FROM 
    employees;

CASE式は「式」なので1つの値を返します。厳密にいえば、列名が書ける場所にCASE式が書けるのでレコード数文における単一の値を評価します。
ですので、注意点としては条件分岐が返すデータ型を統一する必要があります。
とある、条件の時は数値、別の条件の時は文字列という感じには出力できません。

CASE式の使い所

いくつか本書の中で基礎的な部分をまとめました。

既存のコード体系をベースに新規のコード体系に置き換えて集計する

マスターテーブルに見られる既存のカテゴリーをベースにさらに分類して集計するサンプルですね。例によって、県のサンプルは以下のように、GROUP BYで別の値として集約、それに応じてSELECTも対応するように修正します。

WITH PrefectureM (pref, population) AS (
SELECT '新潟県', 222 FROM dual UNION ALL
SELECT '富山県', 104 FROM dual UNION ALL
SELECT '石川県', 114 FROM dual UNION ALL
SELECT '福井県',  76 FROM dual UNION ALL
SELECT '岐阜県', 197 FROM dual UNION ALL
SELECT '静岡県', 363 FROM dual UNION ALL
SELECT '愛知県', 755 FROM dual UNION ALL
SELECT '三重県', 180 FROM dual
)
SELECT
    CASE
        WHEN pref IN ('新潟県', '富山県', '石川県', '福井県') THEN '北陸'
        WHEN pref IN ('静岡県', '岐阜県', '三重県', '愛知県') THEN '東海'
        ELSE 'その他'
    END AS 地方名,
    SUM(population) AS 総人口
FROM
    PrefectureM
GROUP BY
    CASE
        WHEN pref IN ('新潟県', '富山県', '石川県', '福井県') THEN '北陸'
        WHEN pref IN ('静岡県', '岐阜県', '三重県', '愛知県') THEN '東海'
        ELSE 'その他'
    END;

列(属性)の値をベースにクロス表を作成する

gender 属性の男女という値をベースに、それぞれ列としてクロス表を作成する SQLは以下のように書けます。

WITH ToukaiM (pref, gender, population) AS (
    SELECT '静岡県', '男', 178 FROM dual UNION ALL
    SELECT '静岡県', '女', 185 FROM dual UNION ALL
    SELECT '岐阜県', '男', 96 FROM dual UNION ALL
    SELECT '岐阜県', '女', 101 FROM dual UNION ALL
    SELECT '三重県', '男', 87 FROM dual UNION ALL
    SELECT '三重県', '女', 93 FROM dual UNION ALL
    SELECT '愛知県', '男', 375 FROM dual UNION ALL
    SELECT '愛知県', '女', 380 FROM dual
)
SELECT
    pref AS 県名,
    MAX(CASE WHEN gender = '男' THEN population ELSE 0 END) AS 男,
    MAX(CASE WHEN gender = '女' THEN population ELSE 0 END) AS 女
FROM ToukaiM
GROUP BY pref;

その他応用する上で気になったこと

CHECK制約で複数の列の条件関係を定義する

ポイントは、条件法(PならばQ)と論理積(PかつQ)の違いですね。CASEを用いれば、論理積しか記述できなWHERE句と違って、CASEを入れ子にすることによって、条件法(PならばQ)が記述できるよってことですね。

テーブル同士のマッチング

やりたいのはマスターテーブルの組み合わせでクロス表を作成することです。ポイントとしては、CASE式は列名の指定するところには書けて、各月毎に属性を持つので、それぞれのSELECT句の列指定にCASE式を書けばうまくいきそうだっという風に考えることができます。

CASE式の中で集約関数を使う

簡単にいうと、それぞれのクエリの結果をUNION ALL で結合して、ORDER BYで並び替えても欲しい結果を取得できるけど、GROUP BYで集約したあとに、CASE式内で集約関数の結果に対して、分岐すれば同様の結果が取得できるよっ！ということですね。学生番号ごとに集約しているので、その集合に対して、SELECT内では集約関数の値を比較できるということです。

これは、条件を分岐させたUPDATEの話もあった、複数回のSQLを組み合わせなければいけない場合でもCASE式を用いることで1回のSQLで処理できるというのはCASE式を理解する上で重要なポイントだと思います。

参照

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ