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

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

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

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

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

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

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

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

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

この記事を書いている人

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

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

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

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

SUBSTR

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

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

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

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

EXTRACT

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

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

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

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

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

FORMAT_TIMESTAMP

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

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

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

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

まとめ

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

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

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

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

YYYYMM 形式のような文字列として取得するなら FORMAT_TIMESTAMP が適切でしょうね

数値として MM を抽出したいなら EXTRACT(MONTH FROM ...) がいいと思います

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