bigquery

SQL入門 – 6)条件式

kenichiro

条件式

SQL入門の6回目は条件式です。Case, If, Coalesceなどについて学習します。where句の後ろに条件式を記述することで、条件を満たす行だけを抽出することが可能です。havingを使用すればグループ化したあとのグループに対して条件式を適用することもできます。

このように行やグループに対して条件を記述するだけでなく、select命令で列を記述するところに条件を記述することができます。

image 75
SQL入門 – 6)条件式 10

本章では上記イメージに示すよう、「列を記述するところに、条件式を使って出力を加工する方法」について説明します。

本記事はFuture Coders独自教材からの引用です。

CASE

一般的なプログラミングにおけるswitchやif ~ else if ~ elseといった文法を記述することで、列に表示する内容を制御する命令です。CASE命令は2つの使い方があります。どちらの方法を使用しているか混乱しないよう注意してください。

  • if ~ else if ~ else的な使い方
  • switch的な使い方

CASE1: if ~ else if ~ else的な使い方

条件を列挙してゆき、条件を満たしたところの値を返します。文法は以下の通りです。

case 
    when 条件1　then 値1
    when 条件2　then 値2
    when 条件3　then 値3
    else 値4
end

JavaScritpなどの一般的なプログラミング言語であれば以下のような記述と似ています。

if (条件1) {
    return 値1
}elseif (条件2){
    return 値2
}elseif (条件3){
    return 値3
}else{
    return 値4
}

以下年齢に応じてyoung, middle, seniorと値を設定するサンプルです。

SELECT 
  dob_age,
  casewhen dob_age <30then "young"
    when dob_age <60then "middle"
    else "senior"
  endas generation
FROM `sample.people`
image 76
SQL入門 – 6)条件式 11

CASE2: switch命令的な使い方

ある値（式）を指定して、その値に応じて変換後の値を記述する方法です。caseの直後にwhenが来るのではなく、列名などの式がくることに注意してください。

case 式
    when 値1　then 戻り値1
    when 値2　then 戻り値2
    when 値3　then 戻り値3
    else 戻り値4
end

JavaやCなどの一般的なプログラミング言語であれば以下のようなswitch文と似ています。

switch (式) {
    case 値1: return 戻り値1;
    case 値2: return 戻り値2;
    case 値3: return 戻り値3;
    default: return 戻り値4;
}

以下はArea_IDを別の名前に変換する例です。

SELECT Area_ID,
case Area_ID
  when "A" then "Park1"
  when "B" then "Park2"
  when "C" then "Park3"
  else "Park4"
endas Area
FROM `sample.squirrel-data`
image 77
SQL入門 – 6)条件式 12

IF

IF命令は以下のように条件を記述し、成立したとき、成立しなかったときと値を記述します。

IF(条件, 成立時の値, 非成立時の値)

以下はpeopleテーブルでgenderの値(female/male)をM/Fに変換する例です。

SELECT gender, if (gender="male", "M", "F") as MF
FROM `sample.people`
image 78
SQL入門 – 6)条件式 13

COALESCE

COALESCE関数は、複数の引数を受け取り、左から順番に値を調べ、最初にNULLでなかった値を返します。一般的に、値がない場合にはNULLが埋められます。そのNULLをデフォルト値に変換するときなどによく利用されます。

文法は以下の通りです。

COALESCE(値1, 値2, ...)

以下はpeopleテーブルでid_nameがnullの時に”default”という文字で埋める例です。

SELECT id_name, coalesce(id_name, "default") as id_name
FROM `sample.people`
image 79
SQL入門 – 6)条件式 14

演習

ex-cond1.py

peopleテーブルで、id_nameの値に応じて、CPF=1, SIN=2, SSN=3, TFN=4, null=5となるid_num列を追加してください。switchと同じパターンのcaseを使用してください。

ex-cond2.py

peopleテーブルで40歳未満が”中堅”, 60歳未満が”ベテラン”, それ以上が”シニア”となる列、age_category列を追加してください。

image 80
SQL入門 – 6)条件式 15

ex-cond3.py

squirrel-dataテーブルでPrimary_Fur_Color列の値がBlackなら”黒”, Grayなら”グレー”, Cinnamonなら”シナモン”, nullなら”不明”と変換してください。switchと同じパターンのcaseを使用してください。

ex-cond4.py

if命令を使って、peopleテーブルで60歳未満なら通常料金、そうでなければシニア料金と表示する列priceを追加してください。

image 81
SQL入門 – 6)条件式 16

ex-cond5.py

squirrel-dataテーブルでcoalesceを使って、Specific_Locationがnullの時に”unknown”と表示する列locationを追加してください。

image 82
SQL入門 – 6)条件式 17

解答例

ex-cond1.py

SELECT id_name, 
case id_name
 when "CPF" then1when "SIN" then2when "SSN" then3when "TFN" then4else5endFROM `sample.people` LIMIT 1000

ex-cond2.py

SELECT dob_age, 
casewhen dob_age <40then "中堅"
 when dob_age <60then "ベテラン"
 else "シニア"
endas age_category
 FROM `sample.people` LIMIT 1000

ex-cond3.py

SELECT Primary_Fur_Color,
case Primary_Fur_Color
  when "Black" then "黒"
  when "Gray" then "グレー"
  when "Cinnamon" then "シナモン"
  else "不明"
endas Iro
 FROM `sample.squirrel-data`

ex-cond4.py

SELECT dob_age, if(dob_age <60, "通常料金", "シニア料金") as price
 FROM `sample.people`

ex-cond5.py

SELECT Specific_Location, COALESCE(Specific_Location, "unknown") as location
 FROM `sample.squirrel-data`
Categories: Programming, SQL