SQL データを学ぶ

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

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

この記事の目標

  • 条件文の仕組みを理解する
  • COALESCEとIF文の使い分けができるようになる
  • CASE文を使いこなせるようになる

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

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

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

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

ahilog

DWH(データウェアハウス)にはBigQuery / Redash / Treasure Data などありますが、本記事ではBigQuery環境を想定して執筆します。

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

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

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

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

その通り!

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

ahilog

テレビなどでも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

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

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

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

ahilog

確かに・・!

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

欠損したデータは除外してしまうか、何かの数字で仮埋めして使うことが多いです。

そんなときに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
SQLくん

年齢>=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 列名
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でやってみよう

DWH(データウェアハウス)にはBigQuery / Redash / Treasure Data などありますが、本記事では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文の使い方は先程說明したとおりですね。

生存率を計算するには、下記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

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

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

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

ahilog

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

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

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

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

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

POINT

  • COALESCE(列,A):NULLならば指定した文字や数字(A)で埋める
  • IF(条件文,A,B):条件を満たしていればA、そうでなければBで埋める
  • CASE WHEN X THEN Y:Xという条件を満たしていればYで埋める

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

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

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

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

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

キャリアアップに必要な知識を身につけよう | Udemyオンライン動画コース

  • この記事を書いた人

yutasukun

京都橘高校からの史上初東大受験・現役合格を成し遂げ 「リアルドラゴン桜」として横断幕が出たことアリ。 そんな過去の栄光も虚しく10年が経過。 今はデータアナリストとして汐留の広告代理店で バリバリはたらく毎日。

-SQL, データを学ぶ

© 2021 ahilog Powered by AFFINGER5