この記事では、下記の部分まで理解することを目指しましょう。
この記事の目標
- 条件文の仕組みを理解する
- COALESCEとIF文の使い分けができるようになる
- CASE文を使いこなせるようになる

今あるデータから新しい列を作ってみたいの!
条件ごとにグループを分けたり、操作する方法を教えてほしいなあ。
ぐま子ちゃんがやろうとしているのは、「条件分岐」という操作だよ。
これをマスターすれば、データ分析の前処理が楽になるよ!

DWH(データウェアハウス)にはBigQuery / Redash / Treasure Data などありますが、本記事ではBigQuery環境を想定して執筆します。
このシリーズの記事はこちらです。
合わせて読みたい
SQLで使う条件文について概説
条件文とは、その名前の通り条件を達成していた場合、指定した処理をするというものです。

りんご なら くだもの、しいたけ なら やさい みたいな感じ?
その通り!
何かの条件を満たしている場合に、特定の処理をするのって、データ分析ではよくあることなんだ。

テレビなどでも20代男性・30代女性など、性年代ごとに調査をしています。
これはグループで括ることで、傾向がわかりやすくなるからです。
SQLでは、以下のように構文を使い分けます。
- 場合分けが単純な2択:COALESCE文とIF文
- 細かく場合分けをする:CASE文
ここまで来た皆様であれば、難しくないので、ひとつずつ見ていきましょうね。
SQLにおける条件文:2択の場合【COALECSE / IF】
NULLを埋めるのに使う:COALESCE()
COALECE文は一番シンプルな条件文です。
NULL(データがない)部分に、指定した文字や数字を入れることが出来ます。
例題
以下の社員名簿のうち、社員番号がないものを0で埋めた列を作りましょう。
名前 | 社員番号 | 身長 | 体重 | 年齢 | 趣味 |
相葉くん | 1 | 150 | 45 | 25 | 音楽 |
伊藤さん | NULL | 160 | 50 | 33 | 釣り |
上野くん | 3 | 172 | 82 | 35 | サッカー |
江頭さん | NULL | 154 | 45 | 40 | 野球 |
小山さん | 5 | 166 | 51 | 25 | ゲーム |
この場合は「社員番号」という列に、COALESCEを適用します。
実際に書くとこんな感じになります。
SELECT 名前,COALESCE(社員番号,0) as 社員番号2
FROM table1

こんなことして何の意味があるの・・?
データは必ずしも完全じゃないんだ。データが無いことを「欠損」っていいます。
ぐま子ちゃんも、アンケートとか適当に書いてることあるでしょ?


確かに・・!
たまにアンケートとかで、電話番号をあえて書かないこともあるね。
欠損したデータは除外してしまうか、何かの数字で仮埋めして使うことが多いです。
そんなときにCOALESCEは役立つ関数です。
答えはこのようになりますね。
名前 | 社員番号2 |
相葉くん | 1 |
伊藤さん | 0 |
上野くん | 3 |
江頭さん | 0 |
小山さん | 5 |
条件を満たしたデータに処理:IF文
IF文はその名の通り、「もし〜だったら」という処理をするという構文です。
例題
以下の社員名簿のうち、健康診断の有無を調べたいです。
30代以上を「必要」、それ未満を「不要」とした列を作ってみましょう。
※テーブル名はtable.2として作成してください。
名前 | 社員番号 | 身長 | 体重 | 年齢 | 趣味 |
相葉くん | 1 | 150 | 45 | 25 | 音楽 |
伊藤さん | NULL | 160 | 50 | 33 | 釣り |
上野くん | 3 | 172 | 82 | 35 | サッカー |
江頭さん | NULL | 154 | 45 | 40 | 野球 |
小山さん | 5 | 166 | 51 | 25 | ゲーム |
table.1 社員名簿
この例題のように、条件を達成しているか否かで処理を分ける時に使うのがIF文です。
if文はこんな感じで使います。
IF(条件,達成時の処理,未達成時の処理)
CREATE TABLE IF NOT EXSITS table2 AS
SELECT 名前,年齢,IF(年齢 >= 30,"必要","不要") as 健康診断
FROM table1

年齢>=30 が条件で、達成していたら"必要"・そうでなければ"不要" って入れればええんやな!了解やで!
名前 | 年齢 | 健康診断 |
相葉くん | 25 | 不要 |
伊藤さん | 33 | 必要 |
上野くん | 35 | 必要 |
江頭さん | 40 | 必要 |
小山さん | 25 | 不要 |
SQLにおける条件文:3択以上の場合【CASE】
例えば年齢を10歳刻みに分ける場合、IFやCOALECSEのように2択で収まりません。
このような場合はCASE文を使います。
CASE文は何パターンでも分けることができるので、とても便利な関数です。
例題
以下の社員名簿で、年齢を10代刻みで表現した「年代」の列を作りましょう。
名前 | 年齢 | 健康診断 |
相葉くん | 25 | 不要 |
伊藤さん | 33 | 必要 |
上野くん | 35 | 必要 |
江頭さん | 40 | 必要 |
小山さん | 25 | 不要 |
CASE文は文の書き方が決まっていて、下記のように書きましょう。
WHENで定義する条件はいくつあってもOKです!
CASE
WHEN 条件式1 THEN A
WHEN 条件式2 THEN B
WHEN 条件式3 THEN C
ELSE D
END AS 列名

CASE文は見た目も綺麗に書けるので、処理がわかりやすくていいですね。
実際に例題を解くとこんな感じになります。
SELECT
名前,年齢,健康診断,
CASE
WHEN 年齢 >= 20 and 年齢 < 30 THEN "20代"
WHEN 年齢 >= 30 and 年齢 < 40 THEN "30代"
WHEN 年齢 >= 40 and 年齢 < 50 THEN "40代"
ELSE "50代以上"
END AS 年代
FROM table2
BigQueryでやってみよう
DWH(データウェアハウス)にはBigQuery / Redash / Treasure Data などありますが、本記事ではBigQuery環境を想定して執筆します。
使用するデータについて
BigQueryにtitanicのデータを格納しており、こちらをもとに分析します。

データはtitanic_sample というフォルダの中に titanic_log という名前で格納してあります。

この場合、FROM titanic_sample.titanic_log と「.」をつけて、フォルダ名を書く必要があるので注意しましょう。
大抵の場合、データは何かしらのフォルダにまとめて置かれています。
性年代ごとに生存率を計算しよう
今回は乗船客の年齢を10歳刻みにして、性年代ごとに生存率を計算してみましょう。
年齢に関して、20歳未満と60歳以上は括ってしまって大丈夫です。
select
sex,
case
when age<20 then "20歳未満"
when 20<=age and age<30 then "20代"
when 30<=age and age<40 then "30代"
when 40<=age and age<50 then "30代"
when 50<=age and age<60 then "30代"
when 60<=age then "60歳以上" end as age_10,
count(Passengerid) as cnt_all,
sum(Survived) as cnt_survive,
round(sum(Survived)/count(Passengerid),3) as survive_rate
from
`ahilog-analysis.titanic_sample.titanic_log`
where age is not null
group by sex,age_10
CASE文の使い方は先程說明したとおりですね。
生存率を計算するには、下記3つの情報が必要となってきます。
- 乗客数の合計
- 生存者数の合計
- 生存率 = 生存者数 / 乗客数
この3つを性年代別に集計しましょう。
下記の箇所がそれに該当します。
count(Passengerid) as cnt_all,
sum(Survived) as cnt_survive,
round(sum(Survived)/count(Passengerid),3) as survive_rate
round(列名,桁数)は、小数点を丸める時に使うものです。
ageの列にはNULL(数字が入っていない)があるので、まずはwhere句でNULLを除外して集計しましょう。
その結果がこちらになります。
sex | age_10 | cnt_all | cnt_survive | survive_rate |
---|---|---|---|---|
male
|
30代
|
194
|
39
|
0.201
|
male
|
20歳未満
|
89
|
26
|
0.292
|
female
|
20歳未満
|
75
|
53
|
0.707
|
male
|
20代
|
148
|
25
|
0.169
|
male
|
60歳以上
|
22
|
3
|
0.136
|
female
|
30代
|
110
|
88
|
0.8
|
female
|
20代
|
72
|
52
|
0.722
|
female
|
60歳以上
|
4
|
4
|
1.0
|
明らかに女性の生存率が高いですよね。
これは女子供は優先して逃したという歴史的事実にも当てはまっています。
高齢者は男性の死亡率が高いので、高齢だからといって優遇されたわけではなさそうです。

差があるかどうかを判断するのに、統計学の知識が必要になるわけです。

まとめ:SQLで使う条件分岐について
いかがでしたでしょうか。
本記事では、データを条件ごとにまとめるのに使う条件文の使い方を学びました。
データアナリストは以下の3つの処理をよく使います。
POINT
- COALESCE(列,A):NULLならば指定した文字や数字(A)で埋める
- IF(条件文,A,B):条件を満たしていればA、そうでなければBで埋める
- CASE WHEN X THEN Y:Xという条件を満たしていればYで埋める
これを使いこなせれば、もらったデータを自在に加工できます。
分析の幅を広げるためにマスターしましょう!
皆様のSQLライフに幸あれ!
より体系的にガッツリと勉強したい方にはUdemyがおすすめです!
リッチな動画コンテンツで豊富な知識を手に入れましょう。
キャリアアップに必要な知識を身につけよう | Udemyオンライン動画コース