【SQL】BigQueryにおける DATE_TRUNC関数とは?使い方をわかりやすく解説!

今回はGoogle BigQueryにおける DATE_TRUNC関数の意味や使い方を紹介する。

DATE_TRUNC関数はPostgreSQLなど他のRDBMSでも利用可能な関数だが、使い方や設定できる内容が異なる。

そのため、今回はGoogle BigQueryにおけるDATE_TRUNC関数の使い方に絞って解説していきたいと思う。

DATE_TRUNC関数とは?

DATE_TRUNC関数は指定された日付の値を指定した単位まで切り捨てる(丸める)ことが出来る関数だ。

「日別の売上データを月単位に集計し、月ごとの合計売上を集計したい」

という時など、データを集約したい場合に活躍する。

例えば、下記データ(order_dateはDATE型)をDATE_TRUNC関数を使用して月単位に切り捨てた場合のクエリとその結果は下記だ。

index

order_date

1
2022-05-15
2
2022-05-20
3
2022-06-10
4
2022-06-25
5
2023-01-10
6
2023-01-20
7
2023-02-05
8
2023-02-20
9
2023-03-10

SQL_DATE_TRUNC①

order_dateの各値がDATE_TRUNC関数により、その月の最初の日=1日に統一されたことがお分かりいただけると思う。

もし月単位ではなく、年単位に切り捨てたい場合は、下記のようにDATE_TRUNC関数でYEARを指定すればOKだ。

SQL_DATE_TRUNC②

DATE_TRUNC関数により、各値が年単位に丸められ、その年の最初の日=1月1日に丸められている。

このようにDATE_TRUNC関数は日付データを指定した単位に切り捨てたい時に非常に便利な関数だ。

スポンサーリンク

DATE_TRUNC関数の使い方

DATE_TRUNC関数は下記を指定して使用する。

  • 対象カラム:値の切り捨てを行いたい日時型カラム(TIME型以外)を指定
  • 単位:どの日付の単位まで丸めるかの指定

DATE_TRUNC⑦

具体例に当てはめて確認してみよう。

index

order_date

1
2022-05-15
2
2022-05-20
3
2022-06-10
4
2022-06-25
5
2023-01-10
6
2023-01-20
7
2023-02-05
8
2023-02-20
9
2023-03-10

上記データのorder_dateカラム(DATE型)をDATE_TRUNC関数で月単位に切り捨てたい場合のクエリとその指定の詳細は下記の通りだ。

DATE_TRUNC④

上記クエリの結果は下記となり、order_dateの各値から日以下の値が切り捨てられ、1日で統一されている。

DATE_TRUNC⑤

DATE_TRUNC関数で指定できる日付の単位は下記の通りだ。

個人的には月単位に丸めるためにDATE_TRUNC関数を使用することが圧倒的に多い。

日付の単位説明

YEAR

年単位に丸める
QUARTER四半期単位に丸める
MONTH月単位に丸める
WEEK週単位に丸める(週の開始曜日は指定可能)
DAY日単位に丸める

WEEKを指定する場合は週の開始曜日を指定して切り捨てを行うことが出来る。

例を確認しよう。

週の開始曜日を指定する場合のクエリと結果は下記の通りだ。

DATE_TRUNC_WEEK

こちらの例では週の開始曜日を日曜日と火曜日に設定してDATE_TRUNC関数を実行している。

週の開始曜日を変えることにより、値が変わっていることがお分かりいただけると思う。

このようにDATE_TRUNC関数は様々な日付単位を指定して対象カラムの切り捨てを行うことが出来る。

スポンサーリンク

DATE_TRUNC関数の活用シーン

DATE_TRUNC関数の活用シーンとしては例えば、下記が挙げられる。

  • トレンド分析(日別の売上データの年月ごとの集計など)

  • 時系列データの整理(日別データを週単位で集計したり、月単位のデータを年単位に集計する)

例えば、下記の日別の売上データを「月別に集計したい」という場合にDATE_TRUNC関数が活躍する。

index

order_date

sales

1
2023-01-20
23000
2
2022-06-25
21000
3
2022-06-10
20000
4
2023-02-05
24000
5
2023-02-20
25000
6
2023-03-10
26000
7
2023-01-10
22000
8
2022-05-15
18000
9
2022-05-20
19000

上記データに対して、DATE_TRUNC関数を使用して月別売上として集計する場合のクエリとその結果が下記の通りだ。

DATE_TRUNC⑧

DATE_TRUNC関数により、日別の売上データが月別に集計されていることがお分かりいただけると思う。

このようにDATE_TRUNC関数はデータを集約するためにWHERE句やGROUP BY句でよく活用される。

まとめ

BigQueryにおけるDATE_TRUNC関数の要点は下記の通りだ。

  • DATE_TRUNC関数は指定した日付単位まで日付データを丸めてくれる関数
  • 日別の売上データを月別で集計するなどデータを集約したい時に便利

他のBigQueryに関する記事は下記にまとめているので是非参考にして欲しい。

おすすめの記事