[SQL/BigQuery] caseの書き方・使い方

caseの書き方

caseを使うと場合分けをして区分やフラグを作ることができる。実務で使う機会が多いのでまとめておく。

caseの書き方は以下の通り。

#caseの使い方の基本
select
case
when 条件1 then 値1 
(when 条件2 then 値2) #省略可
(when 条件3 then 値3) #条件は3つ以上でも可能
(else 値3)            #省略可
end
from tbl

言葉でまとめると次のようになる。が、先にこの言葉を覚えるよりは実際に使ってみて慣れた方がいい。

caseで始まりendで終わる
「when 条件 then 値」で条件を満たしたらその値を取る
条件が複数ある場合は最初に条件を満たした値が適用される
どの条件にも当てはまらない場合、elseの値をとる。elseがない場合はnullになる

caseの使い方

入門書にある簡単なパターンしかしらないと戸惑うことになるので、実務でよくみるcaseの使い方を紹介する。

条件には違うカラムをつかってもよい

最初の条件でカラム1を使い、次の条件では別のカラム2使うことも可能

#20歳以下は1、それ以外の女性なら2、あとは3とカテゴリーを分ける
select
case
when age<20 then 1
when sex=2 then 2
else 3
end
FROM tbl

条件には複数のカラムが使える

問われるのは条件に合うかどうかで、カラムが1つである必要は無い。

#20代以下の男女とそれ以外で区分を分ける
select
case
when age<30 and sex=1 then '20代以下_男性'
when age<30 and sex=2 then '20代以下_女性'
else 'その他'
end
FROM tbl

caseで作った値を集計キーに使える

caseで新しく作った区分を集計に使える。上の例で作った年代のカラムにnendaiと名付けるとその名前をgroup by で使える。

#20代以下の男女とそれ以外ごとに件数を数える
select
case
when age<30 and sex=1 then '20代以下_男性'
when age<30 and sex=2 then '20代以下_女性'
else 'その他' as nendai
,COUNT(1) as count
from tbl
group by nendai

集計した値をcaseに使える

集計結果のcaseの条件に使える。countやmax,minでも同じ。

#idごとに売上が100以上かでフラグを立てる
select 
id
,case when sum(sales)>100 then 1 else 0 end
from tbl
group by id

結合した結果をcaseに使える

結合したあとの両方のテーブルにあるカラムを条件に使える。

#tbl1のidに対してtbl2にそのidがあるかのフラグを立てる
select 
tbl1.id
,case when tbl2.id is not null then 1 else 0 end
from tbl1
left join tbl2 using(id)

caseの条件にサブクエリが使える

caseの条件の中にサブクエリを書いて値を使うことができる。

#tbl1のidがtbl2にあるかでフラグを立てる
select 
id 
,case when id in ((select id from tbl2)) then 1 else 0 end
from tbl1

caseのもう1つの書き方

caseにはもう1つ書き方がある。caseとwhenの間にカラムを書き、値を指定する。

単純である一方、複雑な条件に対応できない。またカラムを書く位置も違うため混在するとややこしい。なので自分では使わないし利用は推奨しない。とはいえ出会った時に知らないと混乱するのでここで紹介だけしておく。

#caseの違う書き方。カラムとwhenの位置に注意
select 
case id 
when 'a' then 1 
when 'b' then 2 
else 9
end
from tbl1

nullや正しくない値があることを前提にcaseを書く

sexが「1=男性、2=女性」だと聞いたら「1なら男性、そうでなければ女性」と書きたくなる。しかし何らかの理由で1,2以外の値が入ったりnullだと本来は女性ではないのに全て女性となってしまう。

対策として条件に「2は女性」を加え、それ以外は全て「不明」にする。他にも年齢から年代を作る、売上から区分を作る場合などでも気を付けること。

#正しくない値があることを前提にしたcaseの書き方
select 
sex
,case
when sex=1 then '男性'
when sex=2 then '女性'
else '不明' #null、1,2以外の値、空欄など
end
FROM tbl

カテゴリー：データ分析のその他の話題