SQLのCASE式をざっくりと理解する

SQLのCASE式は便利です。しかし、どこが便利なのかよく分からなかったので、ざっくりとまとめてみました。

CASE式のサンプル

対象テーブル

都道府県別の人口テーブルを対象にします。

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

欲しい結果

九州、四国、その他、という３つのラベルを作り、作ったラベルごとに人口を集計します。

district	sum(population)
九州	600
四国	650
その他	900

実行SQL

select
  case pref_name
    when '徳島' then '四国'
    when '香川' then '四国'
    when '愛媛' then '四国'
    when '高知' then '四国'
    when '福岡' then '九州'
    when '佐賀' then '九州'
    when '長崎' then '九州'
    else 'その他' end as district, -- district means parts of nation
  sum(population)
from poptbl
group by district
;

CASE式でやっていることのイメージ

つまり、CASE式がやっていることは、データのラベルを読みかえることだと言えます。
こちらを踏まえて、いくつか具体例を見ていきましょう。

例２．データを横持ちにして集計する

やりたいこと

今度は、性別の列を設けて、都道府県別の性別ごとの人口を表現するテーブルにしました。
sex .. 1 が男、sex .. 2が女を指します。

pref_name	sex	population
佐賀	1	20
佐賀	2	80
徳島	1	60
徳島	2	40
愛媛	1	100
愛媛	2	50
東京	1	210
東京	2	190
福岡	1	100
福岡	2	200
群馬	1	240
群馬	2	260
長崎	1	150
長崎	2	50
香川	1	100
香川	2	100
高知	1	100
高知	2	100

こちらのテーブルから、さきほど作った新しい集合を、男女別に適用してみましょう。

結果のイメージ

徳島、香川、愛媛、高知を比較用に新しく用意しました。
その他と九州を削り、四国と、total（すべて）を追加しました。

sex	tokushima	kagawa	ehime	kochi	shikoku	total
男	60	100	100	100	360	1080
女	40	100	50	100	290	1070

実行SQL

select
  case when sex = '1' then '男' else '女' end as sex,
  sum(case when pref_name = '徳島' then population else 0 end) as tokushima,
  sum(case when pref_name = '香川' then population else 0 end) as kagawa,
  sum(case when pref_name = '愛媛' then population else 0 end) as ehime,
  sum(case when pref_name = '高知' then population else 0 end) as kochi,
  sum(case when pref_name in ('徳島', '香川', '愛媛', '高知') then population else 0 end) as shikoku,
  sum(population) as total
from
  poptbl2
group by sex;

SQLのポイント

in句を使って、四国に読みかえるデータをまとめて記述しています。

  sum(case when pref_name in ('徳島', '香川', '愛媛', '高知') then population else 0 end) as shikoku,

また、すべて(total)については、条件式が不要なので、pref_nameをすべて集計しています。

  sum(population) as total

こうしてみると、CASE式というのは、ExcelのVLookupに何となく似ているように思えます。

SQLのイメージ

例３．任意のソート順を実現する

データのラベルを読みかえる、というCASE式の特徴をよく現したのがこの方法だと思います。

やりたいこと

人事評価のランキングテーブルを作りました。データの中身に、そこまで意味はありません。
こちらのデータのidの順番を、任意の値に変更してみましょう 🍌

id	num
A	1
A	2
A	3
B	1
N	1
N	2

結果のイメージ

任意の値に変わりましたね 🍌🍌🍌

id	num
B	1
A	1
N	1
A	2
N	2
A	3

実行SQL

select
  id,
  num
from
  ranks
order by
  case
    when id = 'B' and num = 1 then 1
    when id = 'A' and num = 1 then 2
    when id = 'N' and num = 1 then 3
    when id = 'A' and num = 2 then 4
    when id = 'N' and num = 2 then 5
    when id = 'A' and num = 3 then 6
    else null end
;

SQLのポイント

order by にCASE式を導入していることがポイントです。then の後の数字を付けることで、レコードをラベルとして読み替えているのです。

order by
  case
    when id = 'B' and num = 1 then 1

order by は、読み替えられたデータの順番でなされます。

SQLのイメージ

おわりに - CASE式は何が便利か -

CASE式について、何が便利なのかを「ラベルの読み替え」という観点から見てみました。ラベルを読みかえると、既存のデータに独自のフラグを立てることが可能となり、立てたフラグの単位で集合演算を行えます。CASE式は、この点が便利だとわかりました。

なお、CASE式は、構文によってうまく動かないことがあるとのことです。ですので、構文を検証する記事も書きました。もし宜しければ、併せてご覧ください。