【BigQuery】日付型データを月単位、年単位に変換するサンプル

こんにちは、ミナピピン(@python_mllover)です。今回は自分用のBigqueryで日付のデータを月単位や年単位で集計したいときのメモになります。

データ準備

WITH sample_data AS(
    SELECT
     DATE('2022-12-31')AS date
    ,TIMESTAMP('2022-12-25 00:00:00')AS timestamp_at
    ,TIMESTAMP('2022-12-01 00:00:00', 'Asia/Tokyo')AS timestamp_jp
    ,TIMESTAMP('2021-11-01 00:00:00')AS timestamp_last
)

SELECT * FROM sample_data

<実行結果>

DATE_TRUNC()で日付/月/年の単位変換

SELECT
  # タイムスタンプを日付に変換 
  DATE(timestamp_at) AS Date_
  # 日付を月単位に変換する 
  ,DATE_TRUNC(DATE(timestamp_at), MONTH) AS MONTH_
  # 日付を年単位に変換する
  ,DATE_TRUNC(DATE(timestamp_at), YEAR) AS YEAR_
FROM
  sample_data

<実行結果>

注意点としてはDATE_TRUNC()はDATETIME型に対しては仕様できないので、DATE()などでDATE型に変換してやる必要があります。

またTIMESTAMPのまま単位を変換したい場合はTIMESTAMP_TRUNC()という関数が用意されています。

SELECT
  # タイムスタンプを日付に変換
  TIMESTAMP_TRUNC(timestamp_at,　DAY) AS DAY_
  # タイムスタンプを月に変換
  ,TIMESTAMP_TRUNC(timestamp_at,　MONTH) AS MONTH_
  # タイムスタンプを年に変換
  ,TIMESTAMP_TRUNC(timestamp_at,　YEAR) AS YEAR_
FROM
sample_data

<実行結果>