Zenn
🦖

📝 BigQuery恐怖症脱却のための関数

2023/11/28に公開
BigQuery
tech

BigQuery functions

Timestamp functions

  • CURRENT_TIMESTAMP
    • 現在の日付と時刻を表すタイムスタンプ
// 現在時刻
CURRENT_TIMESTAMP()
  • TIMESTAMP
    • さまざまなタイプの引数をタイムスタンプに変換する
// 文字列から変換
TIMESTAMP('2023-01-01 10:00:00 UTC')

// 日付型から変換
TIMESTAMP(DATE '2023-01-01')
  • FORMAT_TIMESTAMP
    • タイムスタンプを文字列に変換する
    • 先に文字列のフォーマットを指定し、次にその形式で変換したいタイムスタンプを渡す
// 分秒まで詳細に変換
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP())

// 日付まで
FORMAT_TIMESTAMP('%Y-%m-%d', CURRENT_TIMESTAMP())

// 日本時間でフォーマット
FORMAT_TIMESTAMP('%Y-%m-%d', CURRENT_TIMESTAMP(), 'Asia/Tokyo')
  • PARSE_TIMESTAMP
    • 文字列をタイムスタンプに変換する
    • 先に文字列のフォーマットを指定し、次にそのフォーマットに従った文字列を渡す
// '2023-01-01'(文字列型)を変換
PARSE_TIMESTAMP('%Y-%m-%d', '2023-01-01')
  • TIMESTAMP_SUB
    • 指定されたタイムスタンプから特定の期間※1 を減算する
// 一週間前
TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

// 一ヶ月前
TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 MONTH)

※1 WEEKのサポートはしていない

Date functions

  • CURRENT_DATE
    • 現在の日付（UTC）
// 日本時間の日付
CURRENT_DATE('Asia/Tokyo')
  • DATE
    • さまざまなタイプの引数から日付型のデータを生成・抽出する
// '2023-01-01'を生成
DATE(2023, 1, 1)

// タイムスタンプから日本時間で日付抽出 ※2
DATE(CURRENT_TIMESTAMP(), 'Asia/Tokyo')

※2 FORMAT_TIMESTAMPで変換した日付は文字列型、DATEでは日付型になることに注意

  • FORMAT_DATE
    • 日付型のデータを文字列に変換する
    • 先に文字列のフォーマットを指定し、次のその形式で変換したい日付型を渡す
// 文字列に変換
FORMAT_DATE('%b-%d-%Y', DATE '2023-01-01')
  • PARSE_DATE
    • 文字列を日付型に変換する
    • 先に文字列のフォーマットを指定し、次にそのフォーマットに従った文字列を渡す
// '2023-01-01'(文字列型)を変換
PARSE_DATE('%Y-%m-%d', '2023-01-01')
  • DATE_SUB
    • 指定された日付から特定の期間※3 を減算する
// 一日前
DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY)

// 一週間前
DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 WEEK)

// 一ヶ月前
DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH)

※3 WEEKのサポートあり

String functions

  • CONCAT
    • 複数の値を連結して一つの文字列(BYTES)を生成する
// 'Hello World'を生成
CONCAT('Hello', ' ', 'World')
  • REPLACE
    • 文字列内の特定の部分を別の文字列で置き換える
// Hello BigQueryへ変換
REPLACE('Hello World', 'World', 'BigQuery')

Mathematical functions

  • SAFE_DIVIDE
    • 割る数が0の場合にエラーではなくNULLが返される徐算関数
// 10/2
SAFE_DIVIDE(10, 2)

// NULLが返る
SAFE_DIVIDE(10, 0)

その他

  • CAST
    • 他の型に変換する
    • 先に変換する値を渡し、ASを挟み変換後のデータ型を渡す
// 文字列を数字に変換
CAST('123' AS INT64)
  • IF
    • 条件式を評価し、その結果に基づいて値を返す
    • 条件式を渡し、その後に真の場合に返される値と義の場合に返される値を渡す
// 60点以上が合格
IF(score >= 60, 'Passed', 'Failure')
  • IFNULL
    • 第一引数がNULLかどうかをチェックし、NULLの場合に第二引数を返し、NULLでない場合には第一引数の値をそのまま返す
// 名前の確認
IFNULL(name, 'Unknown')

Discussion