今回はCAST関数について説明します。
CAST関数とは
CAST関数とは、データ型を変換する関数です。
そもそもデータ型とは何か、といったことについては、こちらの記事をご覧ください。
CAST関数を使うことで、データを指定したデータ型に変換できます。
引数（expression）で変換対象のデータを指定し、引数（data_type）で変換するデータ型を指定します。
「AS」の前でデータ型を変更したいフィールドや値を指定し、「AS」の後ろに変換後のデータ型を指定します。
適用事例
CAST関数を使った変換は下記のような場合に用いられます。
- データ型に合わないデータを保存してしまった場合、エラーを回避するため
- 集計したいフィールドが文字列型であるため、数値演算を実行できない場合
- 数値が文字列で保存されており、数値で指定することができない場合
- 日付が日時型やタイムスタンプ型で保存されており、日付計算ができない場合
CAST関数を用いることで、これらのデータ型を適切な状態に変換し、行いたい動作を正常に実行することができます。
CASTとSAFE_CAST
CAST関数には、「CAST」と「SAFE_CAST」という2種類の関数があります。
「CAST」と「SAFE_CAST」の違いとして、データを変換できない場合の処理が異なります。
「CAST」では、変換できないデータがあった場合に「エラー」になり、クエリは実行されません。
「SAFE_CAST」では、変換できないデータがあった場合に、対象のデータを「NULL」に置き換えて、クエリが実行されます。
「SAFE_CAST」では、エラーが表示されずに「NULL」に置き換わって表示されるところが「CAST」との違いになります。
設定方法
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
【流れ】
①関数選択
②引数選択
③AS句の設定
④FORMAT句の設定
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
①関数選択
フィールド選択欄右のプルダウンから関数モードを選択し、「CAST」関数を選びます。
検索画面に関数名を入力することで、絞り込むことができます。(画像参照)
「関数」欄で「CAST」関数を選ぶ
②引数選択
「CAST」を選択すると、下画像のように関数ビルダが立ち上がります。
まず、「CAST」下の選択欄に、データ型を変換したいカラムや数値を設定します。
③AS句の設定
次に、変更したいデータ型を指定します。
「AS」の選択欄右のプルダウンを選択すると、下画像のようになります。
変換したいデータ型を選択する
ここでは、変換できるデータ型が一覧表記されているため、変換したいデータ型を選択するだけで済みます。
以下のMagiqで設定できるデータ型の一覧表を参考に、任意のデータ型を選択してください。
| データ型 | |
| INT64 | 整数型 |
| INTEGER | |
| FLOAT64 | 浮動小数点型 |
| NUMERIC | 10進数型 |
| BIGNUMERIC | |
| BOOL | ブール型 |
| BYTES | バイト型 |
| TIME | 時刻型 |
| DATE | 日付型 |
| DATETIME | 日時型 |
| TIMESTAMP | タイムスタンプ型 |
| INTERVAL | インターバル型 |
| STRING | 文字列型 |
| ARRAY | 配列型 |
| STRUCT | 構造体型 |
データ型によっては変換したいデータ型にできない場合もあります。
そのため、Bigqueryでデータ型の詳細を確認してから使用してください。
④FORMAT句の設定
最後に、FORMAT句の設定をします。
まず、前提として、FORMAT句は毎回設定する必要はありません。
基本的にはデータの型は自動的に変換されますが、変換前のデータ表記がデフォルトの型と一致していないと、エラーになってうまく変換ができない場合があります。そのようなときに、FORMAT句を使ってデータの表記を明示します。
例えば、文字列データを日付データに変換したい場合について考えてみます。
システムによってデフォルト設定されている日付の表し方が”(例)2020-11-30″となっているのに、変換したい文字列データが“2020/11/30”という形で表記されていたりすると、そのままではCAST関数でうまく変換できない場合があります。このようなとき、FORMAT句で「どの部分が年を表していて、どの部分が日付を表しているのか」ということを明示する必要があります。
ただし、表記を指定することができるデータタイプには条件があり、下記に示した５パターンのみになります。
- 「BYTES」を「STRING」として変換する場合
- 「STRING」を「BYTES」として変換する場合
- 「DATE」「TIME」を「STRING」として変換する場合
- 「STRING」を「DATE」「TIME」として変換する場合
- 「NUMERIC」を「STRING」として変換する場合
今回は例として、「DATE」「TIME」を「STRING」として変換する場合のFORMAT句を取り上げます。
下図が、「DATE」「TIME」を「STRING」として変換する場合に使用できるFORMAT句の一部になります。
| format句 | 意味 | 例 |
| YYYY | 年、4 桁以上で表示 | 入力: DATE ‘2018-01-30’出力: 2018 |
| 入力: DATE ’76-01-30’出力: 0076 | ||
| 入力: DATE ‘10000-01-30’出力: 10000 | ||
| YY | 年、下 2 桁のみ表示 | 入力: DATE ‘2018-01-30’出力: 18 |
| 入力: DATE ‘8-01-30’出力: 08 | ||
| MM | 月、2桁で表示 | 入力: DATE ‘2018-01-30’出力: 01 |
| MON | 3文字に省略された月の名前 | 入力: DATE ‘2018-01-30’出力: JAN |
| MONTH | 月の名前 | 入力: DATE ‘2018-01-30’出力: JANUARY |
| DAY | 日付の曜日を表示 | 入力: DATE ‘2020-12-31’出力: THURSDAY |
| DY | 日付の曜日を3文字に省略して表示 | 入力: DATE ‘2020-12-31’出力: THU |
| DD | 日付のみ表示 | 入力: DATE ‘2018-12-02’出力: 02 |
「DATE」「TIME」を「STRING」として変換する場合では、日付型で保存されているデータから任意の部分を文字列型として抽出することになります。
ここで、Magiqで「日付型の”2023-10-01″から”4桁の年”に変換する」クエリを作成してみます。
ここでは、まず引数にDATE関数を用いて、日付型の”2023-10-01″のデータが入っています。
次にAS句に文字列型を示す「STRING」を指定します。
最後に、FORMAT句に「DATE」のデータから4桁以上の年を表示する”YYYY”を設定します。
上記のクエリを実行すると以下のような結果がでます。
今回取り上げたFORMAT句の内容は一例になります。
GoogleCloud上にFORMAT句の説明がありますので、指定したいデータ表記に合わせて適宜ご参照ください。https://cloud.google.com/?hl=ja
まとめ
MagiqでのCAST関数の使い方は下記の画像のようになります。
適したデータ型でデータを操作するために、CAST関数を利用しましょう。
