今回はGoogle BigQueryにおけるIFNULL関数の意味や使い方について紹介する。
実務において、NULL（何もない状態）が含まれるデータを扱うことは非常に多い。
その場合はNULLを除外したり、別の値に置き換えたりするなど工夫が必要な場合があるが、IFNULL関数はそのような場面で非常に便利な関数だ。
IFNULL関数とは？
BigQueryのIFNULL関数は、ある値がNULL（つまり、何もない状態）かどうかを判定し、もしNULLなら代わりに別の値を返してくれる関数だ。
例えば、下記のsample_tableの内、「address」カラムに含まれているNULL値を調整したい時などにIFNULL関数は活躍してくれる。
- テーブル
- sample_table
- カラム
- id ：STRING型
- name：STRING型
- address：STRING型
| id | name | |
|---|---|---|
001 | 山田 貴志 | 東京都渋谷区神宮前1-2-3 |
002 | 田中 幸子 | null |
003 | 鈴木 健二 | 大阪府大阪市中央区難波5-6-7 |
004 | 中村 由美 | null |
005 | 佐藤 博史 | 京都府京都市左京区吉田本町10-11-12 |
上記データをIFNULL関数を使用して、「address」カラムの値がnullの場合は代わりに「住所未定」と表示させるクエリとその結果は下記の通りだ。
1 2 3 4 5 6 7 | SELECT id ,name ,address ,IFNULL(address,'住所未定') AS address_2 FROM `sample_dataset.sample_table` |
上記の例では、IFNULL関数を使用して、addressカラムの「null」を「住所未定」に変換したaddress_2というカラムを作成している。
「null」が「住所未定」という値に変換されていることがお分かりいただけると思う。
このようにIFNULL関数はnullを他の値に置き換えたい時に非常に便利な関数だ。
IFNULL関数の使い方
IFNULL関数は下記2つを指定して使用する。
- 対象カラム
- 変換する値（nullの代わりに）
上図にようにIFNULLの使い方は比較的シンプルで、①どのカラムを対象にするか？②nullの代わりにどんな値を設定するか？の2点を指定すればよい。
先ほどの例に当てはめて確認してみよう。
| id | name | |
|---|---|---|
001 | 山田 貴志 | 東京都渋谷区神宮前1-2-3 |
002 | 田中 幸子 | null |
003 | 鈴木 健二 | 大阪府大阪市中央区難波5-6-7 |
004 | 中村 由美 | null |
005 | 佐藤 博史 | 京都府京都市左京区吉田本町10-11-12 |
上記データのaddressカラム（STRING型）にはnullが複数含まれている。
これをIFNULL関数を使用して、nullを「住所未定」という文字列に変換するクエリと具体的な指定の仕方が下記となる。
1 2 3 4 5 6 7 | SELECT id ,name ,address ,IFNULL(address,'住所未定') AS address_2 FROM `sample_dataset.sample_table` |
上記の結果は先ほどと同様だ。
IFNULL関数を使用して作成した「address_2」カラムにはnullの代わりに「住所未定」が表示されている。
対象カラムのデータ型を変えて、もう1つ具体例を確認しよう。
下記データの中で、年齢を表す「age」カラム（INT64型）にはnullが含まれている。
| id | name | age |
|---|---|---|
| 001 | 佐藤 太郎 | 25 |
| 002 | 鈴木 花子 | null |
| 003 | 高橋 次郎 | 30 |
| 004 | 田中 さくら | null |
| 005 | 伊藤 陽子 | 22 |
| 006 | 渡辺 雄太 | null |
| 007 | 山本 真紀 | 28 |
| 008 | 中村 亜希 | null |
| 009 | 小林 慎吾 | 19 |
| 010 | 加藤 ゆき | null |
IFNULL関数を使用して、「age」カラムの値がnullの場合は0に変換するクエリとその具体的な指定の仕方が下記となる。
1 2 3 4 5 6 7 | SELECT id ,name ,age ,IFNULL(age,0) AS age_2 FROM `sample_dataset.sample_table` |
上記クエリの結果が下記となる。
IFNULL関数を使用してnull変換した「age_2」カラムではnullが0に変換されていることがお分かりいただけると思う。
今回nullを変換する対象の「age」カラムはINT64型(INT型)のため、変換後の値（今回は0）を指定する際にシングル（ダブル）クォーテーションで囲う必要がない点も覚えておきたい点だ。
IFNULL関数を使用する際の注意点
IFNULL関数を使用する際は対象カラムのデータ型に注意する必要がある。
なぜなら、対象カラムがINT64型の場合に、null変換後の値にSTRING型の「年齢不詳」など異なるデータ型を指定するとエラーになるからだ。
基本的にIFNULL関数を使用してnull変換する場合、STRING型のカラムを対象にする場合は、変換後の値もSTRING型にする必要がある。
INT64型を対象とする場合はINT64型の値をnull変換後の値として指定する。
例えば、下記のような指定の仕方はBigQuery 上でエラーとなる。
上記がエラーになるのは対象カラムがINT64型の「age」カラムなのに、nullを置き換える値にSTRING型の「年齢不詳」を指定してしまっているからだ。
対象カラムをSTRING型のカラムに変更すれば、エラーは解消される。
このようにIFNULL関数でnull変換する場合、変換後の値は対象カラムと同じデータ型の値を指定する必要があるのだ。
IFNULL関数の活用シーン
IFNULL関数は様々な用途で使うことが出来るが、例えば、下記のような活用シーンが考えられる。
- 合計や平均を行う集計時のNULL値の除外（nullを0に変換）
- アンケートデータ等でのNULL値除外（住所のnullを「住所未記入」に変換）
- CONCAT関数での文字列結合の際のNULL値除外
3つ目の活用シーンとして例を挙げたCONCAT関数での文字列結合の際のNULL値除外について具体例で説明しよう。
CONCAT関数は複数の文字列を連結して1つの文字列にしてくれる関数だが、その内の1つにnullがあると結合結果がnullになるという特徴がある。
例えば、下記first_nameカラムとlast_nameカラムの値を結合して、full_nameカラムを作成する場合、nullの処理をせずにそのままCONCAT関数で結合する場合、下記結果となる。
| index | first_name | last_name |
|---|---|---|
| 1 | 由美 | null |
| 2 | null | 高橋 |
| 3 | 健二 | 佐藤 |
| 4 | 太郎 | 山田 |
| 5 | 花子 | 田中 |
1 2 3 4 5 6 | SELECT first_name ,last_name ,CONCAT(last_name,first_name) AS full_name FROM `sample_dateset.sample_table` |
上記のようにCONCAT関数単体だと結合するカラムどれかにNULLがあると結合結果もNULLとなってしまう。
そのため、このような結果を防ぎたい場合はIFNULL関数を組み合わせるのが効果的だ。
下記の例はIFNULL関数を利用して
「もし値にNULLがあれば、空白に置き換える」
という処理を加えた上でCONCAT関数を利用している。
1 2 3 4 5 6 | SELECT first_name ,last_name ,CONCAT(IFNULL(last_name,""),IFNULL(first_name,"")) AS full_name FROM `sample_dataset.sample_table` |
IFNULL関数とCONCAT関数を組み合わせた場合、CONCAT関数で結合するカラムの片側にNULLがあっても結合結果はNULLにならないことがお分かりいただけると思う。
このように「NULL値を何とかしたい！」という様々な場面でIFNULL関数は活用することできる。
まとめ
今回紹介したBigQuery におけるIFNULL関数の要点は下記だ。
- ある値がNULLかどうかを判定し、もしNULLなら代わりに指定した値を返してくれる関数
- 使い方は①対象カラム、②nullの場合に代わりに返す値、の2つを指定すればOK
- nullの代わりに返す値は対象カラムのデータ型と一致している必要がある
- CONCAT関数との組み合わせなどNULL値の処理をしたい様々な場面で利用可能
他のBigQueryに関する記事は下記にまとめているので是非参考にして欲しい。
