DB

BigQueryで日付からYYYYMM形式のデータを取得する方法

date型の日付データからYYYYMMの部分だけ取るにはどうするんだっけ?

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

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

SQLに対応しているのでRDBに馴染みがある人には扱いやすいのも特徴だ。


BQでは他のDBと同じようにdate型の日付データを扱うことができる。

日付データが使えると、 YYYYMM のように年月部分だけ抽出したいこともあるだろう。

しかしどうやって YYYYMM 部分を抽出すればいいのか。

そこで今回はBigQueryで日付からYYYYMM形式のデータを取得する方法について紹介する。

この記事を書いている人


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

記事を読むメリット

BigQueryで日付からYYYYMM形式のデータを取得する方法がわかり、日付データの変換が少し得意になる。


BigQueryで日付からYYYYMM形式のデータを取得する方法

BigQueryで日付からYYYYMM形式のデータを取得する方法は複数あり、以下のような方法が挙げられる。

日付の一部抽出する方法

  • SUBSTR
  • EXTRACT
  • FORMAT_TIMESTAMP

  • 1つずつ使い方を紹介していく。



    SUBSTR

    SUBSTR() は文字列の一部を抽出する関数。

    なので使い方としてはdate型の日付データを文字列に変換してから使うことになる。

    SQL

    with t as(
        SELECT DATE("2021-12-08") as date_col
    )
    SELECT
    CONCAT(SUBSTR(CAST(date_col as string),0,4), SUBSTR(CAST(date_col as string),6,2))
    FROM t
    


    結果

    202112

    2021-12-08最初の4文字と6文字目から2文字を抽出して結合すると YYYYMM 形式になる。




    EXTRACT

    EXTRACT は名前の通り抽出する関数だ。

    使い方としては EXTRACT(抽出したい要素の名前 FROM 日付カラム) となる。

    SQL

    with t as(
        SELECT DATE("2021-02-08") as date_col
    )
    SELECT
    CONCAT(
        CAST(EXTRACT(YEAR FROM date_col) as string),
        LPAD(CAST(EXTRACT(MONTH FROM date_col) as string), 2, "0")
    )
    FROM t
    


    結果

    202102


    EXTRACT(YEAR FROM ...) で年を取得し、EXTRACT(MONTH FROM ...) で月を取得している。

    返り値はint型なので月をゼロ埋めする為に文字列型にしてから LPAD でゼロ埋めしている。




    FORMAT_TIMESTAMP

    FORMAT_TIMESTAMP、恐らくこれが最も楽な方法だろう。

    使い方としては FORMAT_TIMESTAMP(フォーマット指定文字列, 日付カラム)

    SQL

    with t as(
        SELECT DATE("2021-02-08") as date_col
    )
    SELECT
    FORMAT_TIMESTAMP("%Y%m", date_col)
    FROM t
    


    結果

    202102


    "%Y%m" がそのまま YYYYMM 形式を示しており、date型のデータを指定フォーマットの文字列に変換してくれる。




    まとめ

    今回はBigQueryで日付からYYYYMM形式のデータを取得する方法について解説した。

    BigQueryで日付からYYYYMM形式のデータを取得する方法には以下のようなものがある。

    日付の一部抽出する方法

  • SUBSTR
  • EXTRACT
  • FORMAT_TIMESTAMP

  • どれを使用しても結果を得ることは可能だが、オススメは FORMAT_TIMESTAMP

    FORMAT_TIMESTAMP("%Y%m", date_col) のように簡単にフォーマット指定ができるので使いやすい。


    YYYYMM 形式のような文字列として取得するなら FORMAT_TIMESTAMP が適切でしょうね
    数値として MM を抽出したいなら EXTRACT(MONTH FROM ...) がいいと思います



    他にもBigQueryのテクニックに関する記事もあるので、興味があれば見てみて欲しい。

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

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

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

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

    ランキング参加中

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

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

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

    -DB
    -

    © 2022 ITips