[SQL] 場合分けする（CASE）
Contents
カラムの値を使って場合分けをする
CASE文を使うと「〇〇ならば□□」ができる。 条件文にはいくつかの書き方があるが一番利用頻度が高いCASE文を中心に話を進める。CASE文の書き方は
SELECT CASE WHEN 条件1 THEN 値1 (WHEN 条件2 THEN 値2) (ELSE 値3) END FROM テーブル
のように、CASEで始まりWHENで場合分けしてENDで終わる、が基本となる。
サンプルデータ
次のファイルをDLしてコンソールにコピーする。
CASEの書き方
初めにCASE文の書き方を文章でまとめておく。初めての人は文章だけ読んでもピンとこないと思うので無視しても良い。
- CASEで始まりENDで終わる（必須）
- 「WHEN 条件 THEN 値」で「その条件を満たしたら指定した値をとる」
- 複数条件は「WHEN 条件 THEN 値」を必要なだけ書く。最初に条件を満たした値が適用される
- ELSEがあってWHENのどの条件にも当てはまらない場合はすべてELSEの値になる
- ELSEは無くてもかまわない。その場合WHENのどれにも当てはまらなければNULLになる
- CASE文は新しくカラムが出来るので既存のカラムはそのまま残る（のでSELECT文に書けばでてくる）
- JOINした後や集計結果を条件に使うこともできる
文字だけで読むと大変そうだが、実際にやってみるとそうでもないのでやってみるのがいい。
CASE文の使い方いろいろ
CASE文が使えると、テーブルに入っている値を抽出、集計するだけでなく加工して欲しい値を作ることができる。例えば次のようなことだ。
コードから名称を作る
よくある使い方の１つがコードを名称に変換することだ。性別は通常コードになっているがそのままではわからない人も出てくるので、1なら男性、2なら女性と日本語での名称を作る。新しい名前をseibetsuとして作ると次のようになる。
SELECT
sex
, CASE WHEN sex=1 THEN '男性' ELSE '女性' END as seibetsu
FROM di_sql_sample01
| id | sex | seibetu |
| 1 | 2 | 女性 |
| 2 | 1 | 男性 |
| 3 | 1 | 男性 |
| 4 | 1 | 男性 |
| 5 | 1 | 男性 |
| 6 | 2 | 女性 |
| 7 | 2 | 女性 |
マスタが別にある場合は結合するのが自然だが、無い場合や直接書いた方がてっとり早い時はCASE文で書くことが多い。
新しい区分を作る
もう１つよくあるのが区分の追加で、年齢から年代を作ったり、売上からランクを作ったりする。
SELECT
CASE
WHEN age<20 THEN '10代'
WHEN age<30 THEN '20代'
WHEN age<40 THEN '30代'
WHEN age<50 THEN '40代'
ELSE '不明'
END as nendai
FROM di_sql_sample01
| id | age | nendai |
| 1 | 24 | 20代 |
| 2 | 18 | 10代 |
| 3 | 35 | 30代 |
| 4 | 46 | 40代 |
| 5 | 20 | 20代 |
| 6 | 30 | 30代 |
| 7 | 31 | 30代 |
ageを条件に新しく年代を作っている。最初の条件では10歳未満や本来ありえないマイナスのデータが入ってしまうので最初に「マイナスなら不明」「120以上なら不明」などを付け加えたりすることがある。決め方はその企業によるので要確認。
年代は10代刻みでなく5歳とか、70歳以上は一くくりにするなど柔軟に作れる。
フラグを立てる
区分や名称に比べると頻度は少ないが、何等かのフラグを立てることも覚えておくとよい。会員リストにある特定の行動をとったかのフラグを立てたり、そのフラグを使って集計することでフラグの有り無し（例えば会員と非会員）の違いを見たりする。
SELECT
id,age,
CASE WHEN age>=30 THEN 1 ELSE 0 END as flag_over_30
FROM di_sql_sample01
| id | age | flag_over_30 |
| 1 | 24 | 0 |
| 2 | 18 | 0 |
| 3 | 35 | 1 |
| 4 | 46 | 1 |
| 5 | 20 | 0 |
| 6 | 30 | 1 |
| 7 | 31 | 1 |
30歳以上ならばフラグを立てている。BIツールで特定の〇〇な会員などの値があったらこのフラグがどこかで作られている。
CASE WHEN age>=30 THEN True ELSE False END as is_over_30
0/1のフラグではなくTrue/Falseの論理型にするやり方もある。その場合カラム名は「is_～」のような表記といったカラム名にしているのをよく見かける。
CASE文の条件の指定方法
条件には違うカラムをつかってもよい
カラムは１つだけしか条件に使えないということはない。最初の条件でカラム１をつかい、次の条件でカラム２を・・・といった使い方も可能
SELECT
id,
CASE
WHEN age<20 THEN 1
WHEN sex=2 THEN 2
ELSE 3
END as category
FROM di_sql_sample01
20未満なら1、女性なら2、それ以外は3というカテゴリーに分類した。
複数指定できる
問われるのはそのレコードが条件に合うかどうかなので、複数でもかまわない。
SELECT
CASE
WHEN age<30 AND sex=1 THEN '20代以下_男性'
WHEN age<30 AND sex=2 THEN '20代以下_女性'
ELSE 'その他'
END as nendai
FROM di_sql_sample01
20代以下を男女別に、それ以外はその他にまとめた。性年代別
CASE文で作った値を集計キーに使える
CASE文で新しく作った区分を集計に使えばその区分で集計できる。例えば上で作ったnendai別の人数は
SELECT
CASE
WHEN age<30 AND sex=1 THEN '20代以下_男性'
WHEN age<30 AND sex=2 THEN '20代以下_女性'
ELSE 'その他'
END as nendai
,COUNT(1) as count
FROM di_sql_sample01
GROUP BY nendai
これで集計できる。
NULLや正しくない値があることを前提にCASE文を書く
「コードから名称を作る」では「1なら男性、それ以外は女性」という条件で新しいカラムが作られたが、もしsexに何らかの理由で1,2以外の値が入ったりNULLだったりした場合、本来は女性ではないのに全て女性となってしまう。
そこで条件に「2は女性」を加え、それ以外は全て「不明」とするといった対策が必要になる。
SELECT
sex
,CASE
WHEN sex=1 THEN '男性'
WHEN sex=2 THEN '女性'
ELSE '不明' END as seibetsu
FROM di_sql_sample01
データには抜け漏れが発生しているのが普通なので、テーブルが事前にきちんと整えられていることが保証されていない限りは正しくない値が入っていることを前提に考えたほうがいいだろう。
最初にNULLではない値を取る（COALESCE）
条件文にはCASE文以外にもいくつかの書き方があるので紹介する。よく使うものはそのうち覚えるのでこういうのもある、ぐらいでいいだろう。
まずは最初にNULLではない値を取るCOALESCE（”コアレス”とか”コゥアレス”と読む）だ。全部NULLになった場合はやはりNULLが返る。書き方は
SELECT COALESCE(カラム１,カラム２,・・・）
順番に「NULLでなければその値そのもの」を返して、NULLではないカラムが無い（＝全部NULL）ならNULLを返すのだからCASE文を使って書くと
SELECT
CASE
WHEN カラム１ IS NOT NULL THEN カラム１
WHEN カラム２ IS NOT NULL THEN カラム２
・・・
（ELSE NULL）
FROM di_sql_sample01
と同じだ。どこにも該当しなければNULLにしたいのでELSE分は無くてもかまわない。
COALESCEの方がすっきりするので素直に使えばいいと思う。なお、COALESCEでもCASEでもどちらでも書けるようになっておくと、他の人がどちらで書いても読める。
FULLJOINした時にどちらかが欠けているとNULLになってしまうのでCOALESCEを使ってNULLではない値を取れるのが便利。
条件に当てはまるかどうかで場合分けする（IF）
IFは条件を満たせばTrueの場合の値が、そうでなければFalseの場合の値が入る。BigQueryにおけるIFはExcelと全く同じ。入れ子もできる。
簡単な条件ならIFを使うとすっきりするが、複雑な条件を書く場合はCASE文の方がいいだろう。
SELECT
IF(条件文,Trueの場合,Falseの場合）
FROM テーブル
CASEで書くと条件を判別してTrueかそうでないかなので
SELECT
CASE
WHEN 条件文 THEN Trueの場合
ELSE Falseの場合
END
FROM テーブル
となる。入れ子については
SELECT
IF(条件文1,条件文1がTrueの場合,IF(条件文2,条件文2がTrueの場合,条件文がFalseの場合))
FROM テーブル
とこれもExcelと同じ。
カラムがNULLだった場合は指定した値を入れる（IFNULL）
もしカラムがNULLだった場合は指定した値を入れる、そうでなければカラムの値をそのまま返す。数値型のカラムで欠損していたら0を入れたり、IDが不明な場合に99999を入れたりできる。
SELECT
IFNULL(カラム,NULLだったら入れる値）
FROM テーブル
CASEを使うならNULLなら値を入れる、NULLでなければそのままなので次のようになる。
SELECT
CASE
WHEN カラム IS NULL THEN NULLだったら入れる値
ELSE カラム
END
FROM テーブル
IF同じで条件が簡単なら良いがちょっと複雑になると見通しが悪くなるので注意したい。
その他の条件文
リファレンスにはもう１つNULLIFが書かれているが、使ったことがないのと挙動がよくわからないので割愛する。わかったら追記する。