SQLのCASE式を使って集計する場合のテクニック

ポイント

• CASE式は、単純にラベルを読み替えているだけではない
• 既存のコード体系を新しい体系に変換して集計することができる
  • 例えば、以下のような都道府県テーブルと、市区町村テーブルがあるが
    • 地方区分、つまり東北地方や東海地方ごとの（人口などの）件数を出したい場合
    • リージョンズ（regions）というモデルを新設するのは手間
      • 集計だけが目的ならその選択肢はない

都道府県や市区町村ごとではなく地方ごとに集計したい場合

地方	件数
中国	10
中部	11
九州	20
北海道	60
四国	92
東北	176
近畿	1700
関東	2001

こんなとき case式が役に立ちます

実例

SELECT
    CASE p.name
        WHEN '北海道' THEN '北海道'
        WHEN '青森県' THEN '東北'
        WHEN '岩手県' THEN '東北'
        WHEN '宮城県' THEN '東北'
        WHEN '秋田県' THEN '東北'
        WHEN '山形県' THEN '東北'
        WHEN '福島県' THEN '東北'
        WHEN '東京都' THEN '関東'
        WHEN '茨城県' THEN '関東'
        WHEN '栃木県' THEN '関東'
        WHEN '群馬県' THEN '関東'
        WHEN '埼玉県' THEN '関東'
        WHEN '千葉県' THEN '関東'
        WHEN '神奈川県' THEN '関東'
        WHEN '新潟県' THEN '中部'
        WHEN '富山県' THEN '中部'
        WHEN '石川県' THEN '中部'
        WHEN '福井県' THEN '中部'
        WHEN '山梨県' THEN '中部'
        WHEN '長野県' THEN '中部'
        WHEN '岐阜県' THEN '中部'
        WHEN '静岡県' THEN '中部'
        WHEN '愛知県' THEN '中部'
        WHEN '京都府' THEN '近畿'
        WHEN '大阪府' THEN '近畿'
        WHEN '三重県' THEN '近畿'
        WHEN '兵庫県' THEN '近畿'
        WHEN '滋賀県' THEN '近畿'
        WHEN '奈良県' THEN '近畿'
        WHEN '和歌山県' THEN '近畿'
        WHEN '鳥取県' THEN '中国'
        WHEN '島根県' THEN '中国'
        WHEN '岡山県' THEN '中国'
        WHEN '広島県' THEN '中国'
        WHEN '山口県' THEN '中国'
        WHEN '徳島県' THEN '四国'
        WHEN '香川県' THEN '四国'
        WHEN '愛媛県' THEN '四国'
        WHEN '高知県' THEN '四国'
        WHEN '福岡県' THEN '九州'
        WHEN '佐賀県' THEN '九州'
        WHEN '長崎県' THEN '九州'
        WHEN '大分県' THEN '九州'
        WHEN '熊本県' THEN '九州'
        WHEN '宮崎県' THEN '九州'
        WHEN '鹿児島県' THEN '九州'
        WHEN '沖縄県' THEN '九州'
        ELSE NULL -- ELSE は必ず書く
    END AS region, -- END 忘れずに
    count(s.id)
FROM prefectures AS p

-- 色んなテーブルを紐づけていく
LEFT JOIN cities AS c ON c.prefecture_id = p.id
LEFT JOIN shops AS s ON s.city_id = c.id

-- グループ化
GROUP BY region -- AS つけておくと ２箇所に同じ case文を書く手間がない

注意点

• 分岐が返すデータ型は統一する
  • エラーにならないので自分で気を付ける
• ENDの書き忘れをしないように。しても構文エラーになる。
• ELSEは必ず書く
  • 書かなくてもエラーにはならない、なぜなら暗黙的に ELSE NULL 扱いになるから
  • 「エラーにはならないけど結果が違う」原因を探すのに時間かかる可能性があるから
    • 明示的にNULLを返すようにした方が気づきやすい

参照

1-7p

アウトプット100本ノック実施中