CASE式のススメ

0．導入：CASE式とは

1．既存のコード体系を新しい体系に変換して集計する

2．異なる条件の集計を一つのSQLで行なう

3．CHECK制約で二つの列の相関関係を定義する

4．複数の列から最大値を選択する

5．条件を分岐させたUPDATE

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

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

8．やってはいけない間違い

0．導入：CASE式とは

     --単純CASE式 
     CASE sex 
       WHEN '1' THEN '男' 
       WHEN '2' THEN '女' 
       ELSE 'その他' 
     END 

     --検索CASE式
     CASE WHEN sex = '1' THEN '男'
          WHEN sex = '2' THEN '女'
     ELSE 'その他' END 

     --例えば、こんなふうに書くと、結果には「２番」が現れない
     CASE WHEN col_1 IN ('a', 'b')  THEN '１番'
          WHEN col_1 IN ('a')       THEN '２番'
     ELSE 'その他' END

1．既存のコード体系を新しい体系に変換してGROUP BY

　　　　　SELECT  SUM(popularity), 
　　　　　　　　　　 CASE pre_name
　　　　　　　　　　　　　　 WHEN '徳島県' THEN '四国'
　　　　　　　　　　　　　　 WHEN '香川県' THEN '四国'
　　　　　　　　　　　　　　 WHEN '愛媛県' THEN '四国'
　　　　　　　　　　　　　　 WHEN '高知県' THEN '四国'
　　　　　　　　　　　　　　 WHEN '福岡県' THEN '九州'
　　　　　　　　　　　　　　 WHEN '佐賀県' THEN '九州'
　　　　　　　　　　　　　　 WHEN '長崎県' THEN '九州'
　　　　　　　　　　　　　　 WHEN '熊本県' THEN '九州'
　　　　　　　　　　　　　　 WHEN '大分県' THEN '九州'
　　　　　　　　　　　　　　 ELSE 'その他' END
　　　　　  FROM Table_A
　　　　　GROUP BY CASE pre_name
　　　　　　　　　　　　　　 WHEN '徳島県' THEN '四国'
　　　　　　　　　　　　　　 WHEN '香川県' THEN '四国'
　　　　　　　　　　　　　　 WHEN '愛媛県' THEN '四国'
　　　　　　　　　　　　　　 WHEN '高知県' THEN '四国'
　　　　　　　　　　　　　　 WHEN '福岡県' THEN '九州'
　　　　　　　　　　　　　　 WHEN '佐賀県' THEN '九州'
　　　　　　　　　　　　　　 WHEN '長崎県' THEN '九州'
　　　　　　　　　　　　　　 WHEN '熊本県' THEN '九州'
　　　　　　　　　　　　　　 WHEN '大分県' THEN '九州'
　　　　　　　　　　　　　　 ELSE 'その他' END;

         SELECT
　　　　　　　　CASE WHEN salary <= 500 THEN '1'
                  WHEN salary > 500 AND salary <= 600 THEN '2'
                  WHEN salary > 600 AND salary <= 800 THEN '3'
                  WHEN salary > 800 AND salary <= 1000 THEN '4'
　　　　　　　　ELSE NULL END salary_class,
　　　　　　　　COUNT(*)
　　　　　  FROM Table_A
　　　　　GROUP BY
　　　　　　　　CASE WHEN salary <= 500 THEN '1'　--SELECT句のCASE式と同じ！
                     WHEN salary > 500 AND salary <= 600 THEN '2'
                  WHEN salary > 600 AND salary <= 800 THEN '3'
                  WHEN salary > 800 AND salary <= 1000 THEN '4'
　　　　　　　　ELSE NULL END;

2．異なる条件の集計を一つのSQLで行なう

     --男性の人数
     SELECT  dept_nbr, 
            COUNT(*)
       FROM Table_A
      WHERE sex = '1'
     GROUP BY dept_nbr;

     --女性の人数
     SELECT  dept_nbr, 
             COUNT(*)
       FROM Table_A
      WHERE sex = '2'
     GROUP BY dept_nbr;

     SELECT  dept_nbr, 
            SUM( CASE WHEN sex = '1' THEN 1 ELSE 0 END),    --男性の人数
              SUM( CASE WHEN sex = '2' THEN 1 ELSE 0 END)     --女性の人数
       FROM Table_A
     GROUP BY dept_nbr;

3．CHECK制約で二つの列の条件関係を定義する

     CONSTRAINT check_salary CHECK
           ( CASE WHEN sex = '2' 
                  THEN CASE WHEN salary <= 200000
                            THEN 1 ELSE 0 END
             ELSE 1 END = 1 )

　　　　　CONSTRAINT check_salary CHECK
　　　　　　　　　　 ( sex = '2' AND salary <= 200000 )

4．複数の列から最大値を選択する

SELECT CASE WHEN x < y THEN y
            ELSE x END AS greatest
  FROM Sample;

結果：
greatest
--------
      2
      2
      3
      5

SELECT CASE WHEN CASE WHEN x < y THEN y
                      ELSE x END < z THEN z
            ELSE CASE WHEN x < y THEN y
                      ELSE x END END AS greatest
  FROM Sample;

結果：
greatest
--------
      3
      2
      3
      5

5．条件を分岐させたUPDATE

     --条件１．
     UPDATE Personnel
        SET salary = salary * 0.9
      WHERE salary >= 300000;

     --条件２．
     UPDATE Personnel
        SET salary = salary * 1.20
      WHERE salary >= 250000 AND salary < 280000;

     UPDATE Personnel
        SET salary = CASE WHEN salary >= 300000
                            THEN  salary * 0.9 
                          WHEN salary >= 250000 AND salary < 280000
                            THEN  salary * 1.20 
                     ELSE salary END;

     UPDATE SomeTable
        SET p_key = CASE WHEN p_key = 'a'
                           THEN  'b'
                         WHEN p_key = 'b'
                           THEN  'a'
                    ELSE p_key END
      WHERE p_key IN ('a', 'b');

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

     --IN述語の場合
     SELECT keyCol,
            CASE WHEN keyCol IN ( SELECT keyCol From tbl_B ) 
                 THEN 'Match'
            ELSE 'Unmatch' END Label
       FROM tbl_A;

     --EXISTS述語の場合
     SELECT keyCol,
            CASE WHEN EXISTS ( SELECT * From tbl_B WHERE tbl_A.keyCol = tbl_B.keyCol ) 
                 THEN 'Match'
            ELSE 'Unmatch' END Label
       FROM tbl_A;

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

     --条件１．
     SELECT emp_id, MAX(skill_id)
       FROM Team
      GROUP BY emp_id
     HAVING COUNT(*) = 1;

     --条件２．
     SELECT emp_id, skill_id
       FROM Team
      WHERE main_skill_flg = 'Y'
        AND emp_id IN ( SELECT
                          FROM Team
                         GROUP BY emp_id
                        HAVING COUNT(*) > 1);

     SELECT emp_id,
            CASE WHEN COUNT(*) = 1  --特技を一つだけ持つ社員の場合
                   THEN MAX(skill_id)
                   ELSE MAX(CASE WHEN main_skill_flg = 'Y'
                                 THEN skill_id
                                 ELSE NULL END)
            END main_skill 
       FROM Team
      GROUP BY emp_id;

8．やってはいけない間違い

     CASE col_1
       WHEN 1    THEN '○'
       WHEN NULL THEN '×'
     ELSE NULL END

     CASE WHEN col_1 = 1 THEN '○'
          WHEN col_1 IS NULL THEN '×'
     ELSE NULL END

おわりに

註
[1] J.セルコ『プログラマのためのSQL 第2版』(ピアソン・エデュケーション　2001) p.117
　私は同書から、CASE 式に限らず、SQL とデータベースについて多くのことを学びました。このテキストは詰まるところ、この本のへの入門ないしは解説として書かれたものです。未読の方には、一読をすすめます。

[2] DECODE 関数が CASE 式に比べて劣っていると思う点は、次の4つです。第一に、Oracle の方言なので互換性がありません。第ニに、分岐の数が127に限定されています（引数の上限数は255ですが、一つの分岐を表現するのに二つの引数を要します）。第三に、分岐の数が増えるとソースが非常に読みづらくなります。第四に、記述力が貧弱です。具体的には、引数に述語を使った式を取ることができません。当然、サブクエリを作ることもできません。

[3] 正解：論理積の CHECK制約を付けると、この会社は男性を雇用できなくなる。条件法であれば、男性も働ける。
　解説：論理積「P ∧ Q」を満たす場合は、命題 P と 命題 Q が共に真か、どちらかが真でもう一方が不明である場合です。つまりこの会社で働けるのは「女性であり、かつ、給料が20万円以下」の社員か、性別または給料の値が不明の社員の場合です（どちらかの条件が偽になるなら、もう片方の条件が不明な人でも働けません。）
　一方、条件法「P ならば Q」を満たす場合は、P と Q が共に真の場合と、P が偽または不明な全ての場合です。以下に両者の真理値表を示すので参考にしてください。なお、U は、SQL の3値論理に特有の真理値 unknown の略です。（3値論理については、私の「3値論理」を参照してください。）

論理積 P Q P ∧ Q T T T T F F T U U F T F F F F F U F U T U U F F U U U

条件法 P Q P → Q T T T T F F T U F F T T F F T F U T U T T U F T U U T

　見てのとおり、条件法はそもそも社員の性別が女性でない（あるいは性別が分からない）場合は真となります。その意味で、論理積よりも緩い制約である、と言えます。

[4] 条件１．の SQL の skill_id になぜ MAX関数を使っているかというと、skill_id を GROUP BY のキーに含められないため、文法エラーを防ぐための便宜的措置です。だから別にここは MIN でもかまいません。どうせ一行しかないので集計結果は同じです。ただし、SUM や AVG などの集約関数を使うことは避けた方が良いでしょう。数値型にしか使えないからです。
　SQLが提供する二つの極値関数 MAX と MIN は、数値型でも文字型でも日付型でも使用することが可能です。