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