【初心者向けSQL】条件分岐を使い分けよう【COALESCE,IF,CASE】

この記事では、下記の部分まで理解することを目指しましょう。

今あるデータから新しい列を作ってみたいの！

条件ごとにグループを分けたり、操作する方法を教えてほしいなあ。

ぐま子ちゃんがやろうとしているのは、「条件分岐」という操作だよ。

これをマスターすれば、データ分析の前処理が楽になるよ！

ahilog

このシリーズの記事はこちらです。

SQLで使う条件文について概説

条件文とは、その名前の通り条件を達成していた場合、指定した処理をするというものです。

りんご　なら　くだもの、しいたけ　なら　やさい　みたいな感じ？

その通り！

何かの条件を満たしている場合に、特定の処理をするのって、データ分析ではよくあることなんだ。

ahilog

SQLでは、以下のように構文を使い分けます。

• 場合分けが単純な２択：COALESCE文とIF文
• 細かく場合分けをする：CASE文

ここまで来た皆様であれば、難しくないので、ひとつずつ見ていきましょうね。

SQLにおける条件文：２択の場合【COALECSE / IF】

NULLを埋めるのに使う：COALESCE()

COALECE文は一番シンプルな条件文です。

NULL（データがない）部分に、指定した文字や数字を入れることが出来ます。

この場合は「社員番号」という列に、COALESCEを適用します。

実際に書くとこんな感じになります。

SELECT 名前,COALESCE(社員番号,0) as 社員番号2
FROM table1

こんなことして何の意味があるの・・？

データは必ずしも完全じゃないんだ。データが無いことを「欠損」っていいます。

ぐま子ちゃんも、アンケートとか適当に書いてることあるでしょ？

ahilog

確かに・・！

たまにアンケートとかで、電話番号をあえて書かないこともあるね。

答えはこのようになりますね。

条件を満たしたデータに処理：IF文

IF文はその名の通り、「もし〜だったら」という処理をするという構文です。

この例題のように、条件を達成しているか否かで処理を分ける時に使うのがIF文です。

if文はこんな感じで使います。

IF(条件,達成時の処理,未達成時の処理)

CREATE TABLE IF NOT EXSITS table2 AS
SELECT 名前,年齢,IF(年齢 >= 30,"必要","不要") as 健康診断
FROM table1

SQLくん

年齢>=30　が条件で、達成していたら"必要"・そうでなければ"不要"　って入れればええんやな！了解やで！

SQLにおける条件文：３択以上の場合【CASE】

例えば年齢を10歳刻みに分ける場合、IFやCOALECSEのように２択で収まりません。

このような場合はCASE文を使います。

CASE文は何パターンでも分けることができるので、とても便利な関数です。

CASE文は文の書き方が決まっていて、下記のように書きましょう。

WHENで定義する条件はいくつあってもOKです！

CASE
WHEN 条件式1 THEN A
WHEN 条件式2 THEN B
WHEN 条件式3 THEN C
ELSE D
END AS 列名

ahilog

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でやってみよう

使用するデータについて

BigQueryにtitanicのデータを格納しており、こちらをもとに分析します。

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

ahilog

この場合、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文の使い方は先程說明したとおりですね。

生存率を計算するには、下記３つの情報が必要となってきます。

• 乗客数の合計
• 生存者数の合計
• 生存率 = 生存者数 / 乗客数

この３つを性年代別に集計しましょう。

下記の箇所がそれに該当します。

count(Passengerid) as cnt_all,
sum(Survived) as cnt_survive,
round(sum(Survived)/count(Passengerid),3) as survive_rate

round(列名,桁数)は、小数点を丸める時に使うものです。

ageの列にはNULL（数字が入っていない）があるので、まずはwhere句でNULLを除外して集計しましょう。

その結果がこちらになります。

明らかに女性の生存率が高いですよね。

これは女子供は優先して逃したという歴史的事実にも当てはまっています。

高齢者は男性の死亡率が高いので、高齢だからといって優遇されたわけではなさそうです。

ahilog

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

まとめ：SQLで使う条件分岐について

いかがでしたでしょうか。

本記事では、データを条件ごとにまとめるのに使う条件文の使い方を学びました。

データアナリストは以下の３つの処理をよく使います。

これを使いこなせれば、もらったデータを自在に加工できます。

分析の幅を広げるためにマスターしましょう！

皆様のSQLライフに幸あれ！

より体系的にガッツリと勉強したい方にはUdemyがおすすめです！

リッチな動画コンテンツで豊富な知識を手に入れましょう。