SQL修行vol.2

『達人に学ぶSQL徹底指南書』ー1.CASE式のススメ

CASE式は『これならわかるSQL』で、特定の値と同じ場合に抽出される値を切り替えるために使うものと学んだ。が、それ以上の価値がありそうだ。今後きっと使うことになるであろうから、自分なりまとめ的に綴っておこうと思う。

CASE式の構文

単純CASE式と検索CASE式の２種類と、書き方の注意

🐣単純CASE式
単純CASE式は特定の値を条件として、その値に一致するか否かを判断する。一致する値がある場合はそれに対応する結果を返し、ない場合は、ELSEで指定されたデフォルト結果を返す。「一致するか」の判断でのみ使える。

CASE sex
   WHEN '1' THEN '男'
   WHEN '2' THEN '女'
ELSE 'その他' END

🐣検索CASE式
値ではなく、指定された条件をもとに評価し、結果を返す。不等号やNULL値、ほかのSQL関数や演算子と組み合わせることもでき、さまざまな条件式で判別可能である。単純CASE式で書ける条件は、検索CASE式で書けるので、基本的にはこっちを使えば良さそう。

CASE
   WHEN sex = '1' THEN '男'
   WHEN sex = '2' THEN '女'
ELSE 'その他' END

🐣書き方の注意

• 真になるWHEN句が見つかった時点で打ち切られて残りのWHEN句は無視してされる（評価されない）ので、WHEN句は排他的に記述する。

• CASE式の返すデータ型は全ての分岐において一致している必要がある。（ある分岐では文字列、次の分岐では数値を返すというのはだめ）（CASE「式」というように最終的には1つの値に定まらないといけない）

• 最後の「END」は忘れない。書き忘れると構文エラーになる。

• ELSE句はオプションなので書かなくてもエラーにならないが、厄介なバグの温床になるので例えNULLでも書いた方が良い。（書かないと「ELSE NULL」と判断される）

活用ケース１：既存のコード体系を新しい体系に変換する

例1：都道府県[pref_name]別の人口の表をエリア[area]別に集計する

SELECT
   CASE pref_name                    /*列「area」の条件を指定*/
        WHEN 'pref_A' THEN 'Area1'
        WHEN 'pref_B' THEN 'Area1'
        WHEN 'pref_C' THEN 'Area2'
        WHEN 'pref_D' THEN 'Area3'
　　ELSE 'その他' END AS area,
　　SUM(population)
FROM
   PopTbl
GROUP BY
   CASE pref_name                    /*SUMの条件を指定*/
        WHEN 'pref_A' THEN 'Area1'
        WHEN 'pref_B' THEN 'Area1'
        WHEN 'pref_C' THEN 'Area2'
        WHEN 'pref_D' THEN 'Area3'
　　ELSE 'その他' END;

例2：人口階級[pop_class]ごとの都道府県の数を集計する

SELECT
   CASE                                           /*列「pop_class」の条件を指定*/
      WHEN population < 100 THEN '1'
      WHEN population >= 100 AND population < 200 THEN '2'
      WHEN population >= 200 AND population < 300 THEN '3'
      WHEN population >= 300 THEN '4'
   ELSE NULL END AS pop_class,
   COUNT(*),
FROM
   PopTBl
GROUP BY
   CASE                                          /*COUNTの条件を指定*/
      WHEN population < 100 THEN '1'
      WHEN population >= 100 AND population < 200 THEN '2'
      WHEN population >= 200 AND population < 300 THEN '3'
      WHEN population >= 300 THEN '4'
   ELSE NULL END;   

SELECT句とGROUP BY句で同じCASE式を書かないといけないのは面倒だが、SELECT句で指定したエイリアスをGROUP BY句で使うと、DBMSによっては構文エラーになる（PostgreSQL/MySQLはOK、Oracle/Db2/SQL ServerはNG）。

活用ケース２：異なる条件の集計を１つのSQLで行う

例：性別列のある都道府県別人口のテーブルから、男女別・県別の人数の合計を求める

SELECT
   pref_name,
   SUM( CASE WHEN SEX = '1' THEN population ELSE 0 END ) AS cnt_m /*男性の人口*/
   SUM( CASE WHEN SEX = '2' THEN population ELSE 0 END ) AS cnt_f /*女性の人口*/
FROM
   PopTbl2
GROUP BY
   pref_name;

CASE式にはレコードの集約機能はないので、SUMを使わないと元のテーブルのレコード数がそのまま結果に出てくる。

活用ケース３：CHECK制約で複数の列の条件関係を定義する

CHECK制約
カラムに配置できる値の範囲を制限する

例：「一般職員の給料は20万円以上」という給与体系を持つ会社の人事テーブル

--条件法
CONSTRAINT
   check_salary CHECK (
      CASE WHEN position = '1'
           THEN CASE WHEN salary >=200000
                     THEN 1
                ELSE 0 END
      ELSE 1 END = 1
   )

--論理積
CONSTRAINT
   check_salary CHECK (
      position = '1'
      AND
      salary <= 200000

「条件法」「論理積」のどちらも同じ条件のように見えるが、後者は論理積なのでこの条件に合致するレコードしか登録できなくなる。

活用ケース４：条件を分岐させたUPDATE

例1：次のような条件でPersonnel.salaryを更新する。
1.現在の給料が30万以上の社員は10%の減給とする。
2.現在の給料が25万以上28万未満の社員は20%の昇給とする。

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

条件1、2のUPDATE文を2回実行すれば良さそうにも思えるが、それをやると条件1の結果の上に条件2が実行されることになるのでだめ。

例2：主キーを入れ替える

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');

CASE式の分岐による更新は「一気」に行われるので主キー重複によるエラーを回避できる（そもそも主キーを入れ替える必要のあるテーブル設計はどうかという話だが）

活用ケース５：テーブル同士のマッチング

例：講座一覧のテーブルと月々に開講される講座を管理するテーブルから各月の開講状況がわかるクロス表を作成する

--IN述語の利用
SELECT
   course_name,
   CASE WHEN course_id IN (
           SELECT course_id
           FROM OpenCourses
           WHERE month = '2018-06')
        THEN '◯'
　　　　　ELSE '×' AS '6月',
   CASE WHEN course_id IN (
           SELECT course_id
           FROM OpenCourses
           WHERE month = '2018-07')
        THEN '◯'
　　　　　ELSE '×' END AS '7月'
FROM
   CourseMaster


--EXSITS述語の利用
SELECT
   CM.course_name,
   CASE WHEN EXISTS (
                SELECT course_id
                FROM OpenCourses OC
                WHERE month = '2018-06'
                   AND OC.course_id = CM.course_id )
        THEN '◯'
　　　　　ELSE '×' END AS '6月',
   CASE WHEN EXISTS (
                SELECT course_id
                FROM OpenCourses OC
                WHERE month = '2018-07'
                   AND OC.course_id = CM.course_id )
        THEN '◯'
　　　　　ELSE '×' END AS '7月',
FROM
   CourseMaster CM;

EXSITS句
指定された条件にあてはまるレコードが存在するか否かを調べるのに使用する。必ずサブクエリと併用され、サブクエリで1つ以上あてはまるレコードが存在した場合は「TRUE」を返し、そうでない場合は「FALSE」を返す。

活用ケース６：CASE式の中で集約関数を使う

例：学生と所属クラブの一覧テーブルから次の条件でクエリを発行する。
1.1つだけのクラブに所属している学生については、そのクラブIDを取得する。
2.複数のクラブを掛け持ちしている学生については、主なクラブのIDを取得する。

SELECT
   std_id,
   CASE
      WHEN COUNT(*) = 1
      THEN MAX(club_id)
   ELSE MAX(CASE WHEN main_club_flg = 'Y'
                 THEN club_id
            ELSE NULL END AS main_club
FROM
   StudentClub
GROUP BY
   std_id;