CASE式のすすめ

SQL実践入門を7割がたぐらい終えたところで同著者の方の「達人に学ぶSQL徹底指南書」を学習しようと思っています。

この方の著書は気づいたら結構読んでましたね。

Contents

1 CASE式での注意ポイント
- 1.1 同一条件で返すデータは統一させる
- 1.2 ELSE～ENDの書き忘れ
2 サンプル例

CASE式での注意ポイント

SQL実践入門でも書かれていましたが、条件分岐の中にはWHERE句でゴリゴリ書くよりもCASE式を使ったほうが有効なパターンがいくつかあります。

そんな使えるCASE式ですが、以下のことに注意。以前にも自分なりにこのエントリーで書いてますので、重複することあるかも。

同一条件で返すデータは統一させる

当たり前ですが、同一条件内で数値と文字列みたいな、そういうのはダメです。

ELSE～ENDの書き忘れ

ELSEは書かなくても動くっぽいですが、書いておいたほうがよいでしょう。ENDは書かないと動かないようです。ただし、本書では明示的に書くようにアドバイスをしています。エラーにならないけど結果が違うというケースが出てしまうからでしょう。

サンプル例

※著者の方のページに全部情報が載っているのでここ見たほうが早いかも(爆)。

http://mickindex.sakura.ne.jp/database/db_support_sinan.html

テーブル名 poptbl

pref_name | population
———–+————

徳島 | 100
香川 | 200
愛媛 | 150
高知 | 200
福岡 | 300
佐賀 | 100
長崎 | 200
東京 | 400
群馬 | 50

よくあるケースとしては上記のようなテーブルで地方ごとに集計したい、といったときでしょう。こういったときに別カラム(地方コードなど)を追加して判別する方法もありますが、こういうときにこそダイレクトにCASE式が使えます。

SELECT
  pref_name,
  CASE pref_name
    WHEN  '東京'  THEN  '関東'
    WHEN  '徳島'  THEN  '四国'
    WHEN  '香川'  THEN  '四国'
    WHEN  '愛媛'  THEN  '四国'
    WHEN  '高知'  THEN  '四国'
    WHEN  '福岡'  THEN  '九州'
    WHEN  '佐賀'  THEN  '九州'
    WHEN  '長崎'  THEN  '九州'
    WHEN  '群馬'  THEN  '関東'
 ELSE 'その他' END AS district 
FROM
   poptbl

SELECT

pref_name,

CASE pref_name

WHEN '東京' THEN '関東'

WHEN '徳島' THEN '四国'

WHEN '香川' THEN '四国'

WHEN '愛媛' THEN '四国'

WHEN '高知' THEN '四国'

WHEN '福岡' THEN '九州'

WHEN '佐賀' THEN '九州'

WHEN '長崎' THEN '九州'

WHEN '群馬' THEN '関東'

ELSE 'その他' END AS district

FROM

poptbl

上記のSQLで下記のような結果を出すことができます。

あとはこれをGROUP BYしてSUMをとればいいのです。

SELECT
  CASE pref_name
    WHEN  '東京'  THEN  '関東'
    WHEN  '徳島'  THEN  '四国'
    WHEN  '香川'  THEN  '四国'
    WHEN  '愛媛'  THEN  '四国'
    WHEN  '高知'  THEN  '四国'
    WHEN  '福岡'  THEN  '九州'
    WHEN  '佐賀'  THEN  '九州'
    WHEN  '長崎'  THEN  '九州'
    WHEN  '群馬'  THEN  '関東'
 ELSE 'その他' END AS district ,
 SUM(population)
FROM
   poptbl
GROUP BY
   CASE pref_name
    WHEN  '東京'  THEN  '関東'
    WHEN  '徳島'  THEN  '四国'
    WHEN  '香川'  THEN  '四国'
    WHEN  '愛媛'  THEN  '四国'
    WHEN  '高知'  THEN  '四国'
    WHEN  '福岡'  THEN  '九州'
    WHEN  '佐賀'  THEN  '九州'
    WHEN  '長崎'  THEN  '九州'
    WHEN  '群馬'  THEN  '関東'
 ELSE 'その他' END

SELECT

CASE pref_name

WHEN '東京' THEN '関東'

WHEN '徳島' THEN '四国'

WHEN '香川' THEN '四国'

WHEN '愛媛' THEN '四国'

WHEN '高知' THEN '四国'

WHEN '福岡' THEN '九州'

WHEN '佐賀' THEN '九州'

WHEN '長崎' THEN '九州'

WHEN '群馬' THEN '関東'

ELSE 'その他' END AS district ,

SUM(population)

FROM

poptbl

GROUP BY

CASE pref_name

WHEN '東京' THEN '関東'

WHEN '徳島' THEN '四国'

WHEN '香川' THEN '四国'

WHEN '愛媛' THEN '四国'

WHEN '高知' THEN '四国'

WHEN '福岡' THEN '九州'

WHEN '佐賀' THEN '九州'

WHEN '長崎' THEN '九州'

WHEN '群馬' THEN '関東'

ELSE 'その他' END

district | sum
———-+—–
四国 | 650
関東 | 450
九州 | 600

カラムを増やさなくても見事にできました。

なおGROUP BYの部分にはSELECT式で作ったカラム名(districtをそのまま使えます。)

SELECT
  CASE pref_name
    WHEN  '東京'  THEN  '関東'
    WHEN  '徳島'  THEN  '四国'
    WHEN  '香川'  THEN  '四国'
    WHEN  '愛媛'  THEN  '四国'
    WHEN  '高知'  THEN  '四国'
    WHEN  '福岡'  THEN  '九州'
    WHEN  '佐賀'  THEN  '九州'
    WHEN  '長崎'  THEN  '九州'
    WHEN  '群馬'  THEN  '関東'
 ELSE 'その他' END AS district ,
 SUM(population)
FROM
   poptbl
GROUP BY district

SELECT

CASE pref_name

WHEN '東京' THEN '関東'

WHEN '徳島' THEN '四国'

WHEN '香川' THEN '四国'

WHEN '愛媛' THEN '四国'

WHEN '高知' THEN '四国'

WHEN '福岡' THEN '九州'

WHEN '佐賀' THEN '九州'

WHEN '長崎' THEN '九州'

WHEN '群馬' THEN '関東'

ELSE 'その他' END AS district ,

SUM(population)

FROM

poptbl

GROUP BY district

この書き方は便利ですが、厳密にいうと非推奨のようでMySQLやPostgresでは動きますが、他のデータベースでは動かないようです。

なお、階級ごとに分けるなんてこともできます。

SELECT
  CASE 
    WHEN  population  <=  100 THEN '小都市'
    WHEN  population BETWEEN   101  and  399  THEN  '中都市'
    WHEN  population >=  400 THEN  '大都市'
 ELSE 'その他' END AS city_rank ,
 COUNT(*)
FROM
   poptbl
GROUP BY
  CASE 
    WHEN  population  <=  100 THEN '小都市'
    WHEN  population BETWEEN   101  and  399  THEN  '中都市'
    WHEN  population >= 400  THEN  '大都市'
 ELSE 'その他' END

SELECT

CASE

WHEN population <= 100 THEN '小都市'

WHEN population BETWEEN 101 and 399 THEN '中都市'

WHEN population >= 400 THEN '大都市'

ELSE 'その他' END AS city_rank ,

COUNT(*)

FROM

poptbl

GROUP BY

CASE

WHEN population <= 100 THEN '小都市'

WHEN population BETWEEN 101 and 399 THEN '中都市'

WHEN population >= 400 THEN '大都市'

ELSE 'その他' END

city_rank | count
———–+——-
小都市 | 3
中都市 | 5
大都市 | 1

また下記のようなテーブル(PopTbl2)があり、

pref_name | sex | population
———–+—–+————
徳島 | 1 | 60
徳島 | 2 | 40
香川 | 1 | 100
香川 | 2 | 100
愛媛 | 1 | 100
愛媛 | 2 | 50
高知 | 1 | 100
高知 | 2 | 100
福岡 | 1 | 100
福岡 | 2 | 200
佐賀 | 1 | 20
佐賀 | 2 | 80
長崎 | 1 | 125
長崎 | 2 | 125
東京 | 1 | 250
東京 | 2 | 150

これを

pref_name| man | woman
徳島 | 60 | 40
・・・・・・

のように分けたいとします。1回のSQLで。

前段階として、男ならman列に出し、女ならwoman列に出すというのは下記のように書いてあげます。
この場合も下記のようにCASE式を使い、別の列に出力してあげればOKです。

※今回は条件判定をする対象列は一列ですが、2列に出すのでCASE式を2つに書きます。

SELECT
  pref_name,
  sex,
  CASE WHEN  sex = '1' THEN population  ELSE 0 END as man ,
  CASE WHEN  sex = '2' THEN population  ELSE 0 END as woman
FROM PopTbl2

SELECT

pref_name,

sex,

CASE WHEN sex = '1' THEN population ELSE 0 END as man ,

CASE WHEN sex = '2' THEN population ELSE 0 END as woman

FROM PopTbl2

pref_name | sex | men | women
———–+—–+—–+——-
徳島 | 1 | 60 | 0
徳島 | 2 | 0 | 40

この段階で上記のように出力することができます。

ここでさらに、これをpref_nameでSUMします。

SELECT
  pref_name,
  SUM( CASE WHEN  sex = '1' THEN population  ELSE 0 END ) as man,
  SUM( CASE WHEN  sex = '2' THEN population  ELSE 0 END ) as woman
FROM PopTbl2
 GROUP BY pref_name

SELECT

pref_name,

SUM( CASE WHEN sex = '1' THEN population ELSE 0 END ) as man,

SUM( CASE WHEN sex = '2' THEN population ELSE 0 END ) as woman

FROM PopTbl2

GROUP BY pref_name

pref_name | man | woman
———–+—–+——-
長崎 | 125 | 125
東京 | 250 | 150
香川 | 100 | 100
高知 | 100 | 100
徳島 | 60 | 40
愛媛 | 100 | 50
福岡 | 100 | 200
佐賀 | 20 | 80

プログラムと近いですが、一気にやらず一つずつ組み立てていくと楽でしょう。