今回はBigQuery におけるCONCAT関数の意味や使い方を紹介する。
CONCAT関数とは？
CONCAT関数とは複数の文字列を連結して1つの文字列にしてくれる関数だ。
列同士の値をシンプルに結合したり、ー(ハイフン)などの記号と組み合わせて文字列結合したい場合に活躍してくれる。
CONCATは英語の「concatenate」という単語の略だ。
「concatenate」は日本語に訳すと「連結する」「つなげる」という意味なので、単語の意味が分かっていれば、比較的覚えやすい関数だと思われる。
CONCAT関数は例えば、下記テーブルの「last_name」の値と「first_name」と値を連結してフルネームを作りたい、という時に活用できる。
- テーブル
- sample_concat
- カラム
- first_name：STRING型
- last_name：STRING型
| index | first_name | last_name |
|---|---|---|
| 1 | 花子 | 田中 |
| 2 | 太郎 | 山田 |
| 3 | 浩 | 高橋 |
| 4 | 由美 | 鈴木 |
| 5 | 健二 | 佐藤 |
上記データをCONCAT関数を使用して、last_nameとfirst_nameを連結したfull_nameという新しいカラムを作成したクエリとその結果が下記だ。
1 2 3 4 5 6 | SELECT first_name ,last_name ,CONCAT(last_name,first_name) AS full_name FROM `sample_dataset.sample_concat` |
上記の例では、CONCAT関数により、first_nameとlast_nameの値が「last_name→first_name」の順で連結され、フルネームが作られている。
このようにCONCAT関数はSTRING型（文字列型）のカラムの値同士を指定した順序で結合する時などに便利な関数だ。
CONCAT関数の使い方
CONCAT関数は下記を指定して使用する。
- 任意の文字列：連結したい文字列型カラムor任意の文字列（連結したい順序通りに指定）
CONCAT関数では連結したい文字列を何個でも指定することが出来る。
とはいっても、実務で文字列結合する場合、10個以上も連結することはほぼ無いとは思うが、、、。
また、CONCAT関数が対象にできるデータ型はSTRING型（文字列型）とBYTES型だ。
ただ、初心者の方が使うのは基本的にはSTRING型だと思うので、本記事ではSTRING型だけに絞って説明をしていこうと思う。
このCONCAT関数の具体的な使い方としては下記2パターンが考えられる。
- カラム同士の結合
- カラムと任意の文字列の結合（ハイフンや空白など）
それぞれ具体的に見ていこう！
カラム同士の結合
１つ目はシンプルにカラム同士（列同士）の値を結合する使い方だ。
前述の例のように下記のfirst_nameカラムとlast_nameカラムをCONCAT関数で結合する場合がこの使い方に該当する。
- テーブル
- sample_concat
- カラム
- first_name
- last_name
| index | first_name | last_name |
|---|---|---|
| 1 | 花子 | 田中 |
| 2 | 太郎 | 山田 |
| 3 | 浩 | 高橋 |
| 4 | 由美 | 鈴木 |
| 5 | 健二 | 佐藤 |
①first_name、②last_nameの順序でCONCAT関数に指定して結合する場合(concat_1)のクエリとその指定の仕方は下記の通りだ。
1 2 3 4 5 6 | SELECT first_name ,last_name ,CONCAT(first_name,last_name) AS concat_1 FROM `sample_dataset.sample_concat` |
このクエリの結果は下記の通りとなる。
CONCAT関数にはカラム名のみを指定している。
そして、①first_name、②last_nameの順にCONCAT関数で指定したので、その順序通りに結合されている。
連結の順番を変更したい場合はCONCAT関数に指定するカラムの順序を変更すればOKだ。
例えば、①last_name、②first_nameの順序でCONCAT関数に指定し、結合する場合（concat_2）のクエリと指定の仕方は下記となる。
1 2 3 4 5 6 | SELECT first_name ,last_name ,CONCAT(last_name,first_name) AS concat_2 FROM `sample_dataset.sample_concat` |
このクエリの結果は下記となる。
先ほどと異なり、last_nameの値が先に来て、その後にfirst_nameの値が連結されていることがお分かりいただけると思う。
これがシンプルにカラム同士を結合するCONCAT関数の使い方だ。
CONCAT関数に指定したカラム順に連結される点は覚えておきたい。
カラムと任意の文字列の結合
2つ目はカラムの値と任意の文字列を結合をする場合の使い方だ。
任意の文字列というのはー(ハイフン)や空白などのことだ。
例えば、先ほどと同じデータを使用し、名字（last_name)と名前(first_name)の間に半角スペースを入れたfull_nameを作りたい場合のクエリと指定の詳細は下記の通りだ。
full_name |
|---|
田中 花子 |
山田 太郎 |
高橋 浩 |
鈴木 由美 |
佐藤 健二 |
1 2 3 4 5 6 | SELECT first_name ,last_name ,CONCAT(last_name,' ',first_name) AS full_name FROM `sample_dataset.sample_concat` |
上記クエリの結果は下記となる。
単純にlast_nameとfirst_nameを結合した時と違い、名字と名前の間に半角スペースが入っていることがお分かりいただけると思う。
任意の文字列はもちろん空白や_などの記号だけでなく、例えば、名前：～という文字列を先頭に追加することだって可能だ。
1 2 3 4 5 6 | SELECT first_name ,last_name ,CONCAT('名前:',last_name,first_name) AS full_name FROM `sample_dataset.sample_concat` |
full_nameの各値の先頭に 「名前：」が結合されたことがお分かりいただけると思う。
このようにCONCAT関数は任意の文字列と指定したカラムの各値を結合するということも出来る。
この使い方は実務でよく使うので覚えておくと便利だ。
CONCAT関数を使用する際の注意点
CONCAT関数を使用する際に注意したいのは値にNULLがある場合だ。
CONCAT関数でカラム同士を結合する場合に1つのカラムの値にNULLがある場合、結果もNULLになってしまう。
例えば、先ほどのfirst_nameとlast_nameの値の一部に下記のようにNULLが含まれている場合は要注意だ。
| index | first_name | last_name |
|---|---|---|
| 1 | 由美 | null |
| 2 | null | 高橋 |
| 3 | 健二 | 佐藤 |
| 4 | 太郎 | 山田 |
| 5 | 花子 | 田中 |
このようなデータの場合、先ほどと同様にfull_nameを作成するクエリを実行すると下記の結果となる。
1 2 3 4 5 6 | SELECT first_name ,last_name ,CONCAT(last_name,first_name) AS full_name FROM `sample_dataset.sample_concat` |
上記のように、CONCAT関数で結合しようとする値に1つでもNULLがある場合、結果は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_concat` |
今度はCONCAT関数の結果がnullになっていないことがお分かりいただけると思う。
CONCAT関数でNULLが含まれるカラムを結合対象とする場合、結果がNULLにならないようにしたい場合は上記のような工夫が必要となる。
まとめ
今回紹介したBigQuery におけるCONCAT関数の要点は下記だ。
- CONCAT関数は複数の文字列を連結して1つの文字列にしてくれる関数
- 対象はSTRING型とBYTES型だが、STRING型を対象とする使うことが圧倒的に多い
- CONCAT関数ではカラム同士を連結する使い方と任意の文字列とカラムの値を連結する2通りの使い方がある
- 値に1つでもNULLがあるとCONCATの結果はNULLとなるので、IFNULL関数等を利用してNULLの場合の処理を検討しておく必要がある
他のBigQueryに関する記事は下記にまとめているので是非参考にして欲しい。
