DB

BigQueryで値がNULLだったら別のカラム値を使う方法

BigQueryで、あるカラムの値がNULLだったときに別のカラムの値を持ってくるのどうやるんだっけ?

データの分析や処理に便利なBigQuery(BQ)

大規模データの処理に優れており、テーブルに保存した大きなデータでも短時間で集計することができる。

ただしデータは常にキレイに処理できるとは限らない。

ときにはカラムの値がNULLになっていて、別の値を代用しなければいけないこともある。

しかしどうやって値を代用すればいいのか

そこで今回はBigQueryで値がNULLだったら別のカラム値を使う方法について紹介する。

この記事を書いている人


システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。PythonとSQLが得意。

記事を読むメリット

BigQueryで値がNULLだったら別のカラム値を使う方法がわかり、データ処理が楽になる


データ準備

ではまずデータを準備する。

以下のCSVを null_sample というテーブルとして取り込む。

col1,col2,col3
a,b,c
,b,c
,,c
a,b,
a,,


すると以下のようなテーブルとなる。

Row col1 col2 col3
1 null b c
2 null null c
3 a b c
4 a b null
5 a null null



BigQueryで値がNULLだったら別のカラム値を使う方法

BigQueryでカラムの値がNULLだったら別のカラム値を使う方法は2つある。

NULLだったら別の値を使う方法

  • IFNULL
  • COALESCE
  • 個々に紹介していく。



    IFNULL

    「NULLだったら」という文言を聞いてまず思い浮かぶのが IF だろう。

    そして値がNULLのときは専用関数として IFNULL が用意されている。

    使い方は IFNULL() の第1引数にNULL判定したいカラム、第2引数に第1引数がNULLだっだときの代わりの値を指定する。

    SELECT 
    col1,
    col2,
    col3,
    IFNULL(col1,col2) as f
    FROM test.null_sample
    


    結果は以下の通り。

    Row col1 col2 col3 f
    1 null b c b
    2 null null c null
    3 a b c a
    4 a b null a
    5 a null null a


    col1がnullである1行目と2行目はcol2の値を持ってきている。


    しかしここで問題が。

    col1がNULLだったらcol2、さらにcol2がNULLだったらcol3を持ってくるという場合はどうしたらいいのか。

    その際は IFNULL をネストして使うことになる。

    SELECT 
    col1,
    col2,
    col3,
    IFNULL(col1,IFNULL(col2,col3)) as f
    FROM test.null_sample
    

    Row col1 col2 col3 f
    1 null b c b
    2 null null c c
    3 a b c a
    4 a b null a
    5 a null null a


    ネストして関数が階層化されると見づらくなるなぁ
    そういうときのためにもう一つあります



    COALESCE

    BigQueryで値がNULLだったら別のカラム値を使うもうひとつの方法が COALESCE だ。

    COALESCE() には複数の引数が指定でき、その中で最初のNULLでない値を返す。


    なので IFNULL と違ってネストせずに使うことができる。

    SELECT 
    col1,
    col2,
    col3,
    COALESCE(col1,col2,col3) as f
    FROM test.null_sample
    

    Row col1 col2 col3 f
    1 null b c b
    2 null null c c
    3 a b c a
    4 a b null a
    5 a null null a


    こっちの方がSQLが見やすいね



    まとめ

    今回はBigQueryで値がNULLだったら別のカラム値を使う方法について紹介した。

    BigQueryで、値がNULLだったら別のカラム値を使う方法は2つ。

    NULLだったら別の値を使う方法

  • IFNULL
  • COALESCE
  • IFNULLIF と同じような感覚で使用できるが、NULL判定できるカラムまたは計算式は1つ。

    COALESCE は2つ以上の引数を指定でき、その中で最初のNULLではない値を返すので、複数カラムのNULL判定したいときに便利。

    ITipsと同じようなブログを作る方法

    ブログに興味がありますか?

    もしブログに興味がある場合は↓このページ↓を参考にすれば、ITipsと同じ構成でブログを作ることができます

    サーバー、ドメイン、ASPと【ブログに必要なものは全て】このページに書きました。
    同じ構成でブログ作るのはいいけど、記事はマネしないでネ (TДT;)

    ランキング参加中

    にほんブログ村 IT技術ブログへ

    他にもブログやSNSで紹介してくれると励みになります。

    はてブのコメントで酷評されると泣きます(´;ω;`)

    -DB
    -

    Translate »

    © 2021 ITips