今回はGoogle BigQueryにおける DATETIME_TRUNC関数の意味や使い方を紹介する。
BigQueryにおいて日時を丸める（切り捨てる）ことが出来る関数は対象のデータ型に応じて専用の関数がそれぞれ用意されている。
- DATE_TRUNC：DATE型
- DATETIME_TRUNC：DATETIME型
- TIMESTAMP_TRUNC：TIMESTAMP型
- TIME_TRUNC：TIME型
その内、今回はDATETIME_TRUNC関数に焦点を当てて説明していく。
DATETIME_TRUNC関数とは？
DATETIME_TRUNC関数は指定された日時の値を指定した単位まで切り捨てる（丸める）ことが出来る関数だ。
- 「1時間単位のデータを日別に集約したい」
- 「日別データを月別に集約したい」
という時などデータ分析やレポート作成において活躍してくれる。
例えば、下記の時間別のデータをDATETIME_TRUNC関数を使用して、日別に集約する場合のクエリとその結果は下記の通りだ。
| index | created_at |
|---|---|
| 1 | 2023-11-27 09:00 |
| 2 | 2023-11-27 15:00 |
| 3 | 2023-11-27 21:00 |
| 4 | 2023-11-28 03:00 |
| 5 | 2023-11-28 09:00 |
| 6 | 2023-11-28 15:00 |
| 7 | 2023-11-28 21:00 |
| 8 | 2023-11-29 03:00 |
| 9 | 2023-11-29 09:00 |
| 10 | 2023-11-29 15:00 |
1 2 3 4 5 | SELECT created_at ,DATETIME_TRUNC(created_at,DAY) as created_day FROM `sample_table` |
DATETIME_TRUNC関数により、6時間おきのデータが日単位に丸められ、どの値も時刻は00:00:00に切り捨てられている。
このようにDATETIME_TRUNC関数は日時データを指定した単位に切り捨てたい時に便利だ。
DATETIME_TRUNC関数の使い方
DATETIME_TRUNC関数はDATE_TRUNC関数と同様に下記を指定して使用する。
- 対象カラム：値の切り捨てを行いたい日時型カラム（TIME型以外）を指定
- 単位：どの日時の単位まで丸めるかの指定
DATETIME_TRUNCなので、DATETIME型にしか使えないと勘違いしがちだが、実際はDATETIME型以外にもDATE型、TIMESTAMP型にも使用することが可能だ。
ただし、TIME型のデータには使用することが出来ない。
それでは、具体的なDATETIME_TRUNC関数の指定の仕方を例に当てはめて確認してみよう。
| index | created_at |
|---|---|
| 1 | 2023-11-27 09:00 |
| 2 | 2023-11-27 15:00 |
| 3 | 2023-11-27 21:00 |
| 4 | 2023-11-28 03:00 |
| 5 | 2023-11-28 09:00 |
| 6 | 2023-11-28 15:00 |
| 7 | 2023-11-28 21:00 |
| 8 | 2023-11-29 03:00 |
| 9 | 2023-11-29 09:00 |
| 10 | 2023-11-29 15:00 |
上記データのcreated_atカラム（DATETIME型）をDATETIME_TRUNC関数で月単位に丸めたい場合のクエリとその指定の仕方は下記の通りだ。
1 2 3 4 5 | SELECT created_at ,DATETIME_TRUNC(created_at,MONTH) as created_month FROM `sample_table` |
上記クエリの結果は下記となる。
created_atの各値から日以下の値が切り捨てられたcreated_monthカラムが作成されていることがお分かりいただけると思う。
このDATETIME_TRUNC関数で指定できる日付の単位は下記の通りだ。
※HOUR以下は対象をDATE型にする場合は指定不可
| 日付の単位 | 説明 |
|---|---|
| YEAR | 年単位に丸める |
| QUARTER | 四半期単位に丸める |
| MONTH | 月単位に丸める |
| WEEK | 週単位に丸める（週の開始曜日は指定可能） |
| DAY | 日単位に丸める |
| HOUR | 時間単位に丸める |
| MINUTE | 分単位に丸める |
| SECOND | 秒単位に丸める |
| MILLISECOND | ミリ秒単位に丸める |
| MICROSECOND | マイクロ秒単位に丸める（現状は値から何も切り捨てられない） |
使用する場面はかなり限られるかもしれないが、MILLISECONDやMICROSECONDも指定することが可能だ。
ただし、現状（2023年11月時点）はBigQueryでサポートされている日時の最小粒度はマイクロ秒だ。
そのため、DATETIME_TRUNCでMICROSECONDを指定しても実際は特に値は何も切り捨てられない＝元の値のまま、となる。
また、WEEKを指定する場合、週の開始曜日を指定することも可能だ。
週の開始曜日を指定する場合のクエリの指定の仕方や結果はDATE_TRUNC関数の記事で説明しているので、参考にしてもらえれば幸いだ。
まとめ
BigQueryにおけるDATETIME_TRUNC関数の要点は下記の通りだ。
- DATETIME_TRUNC関数は指定した日時単位まで日時データを丸めてくれる関数
- 対象はDATE型、DATETIME型、TIMESTAMP型
- 返す値は対象のデータ型と同じ
- DATETIME型に対してDATETIME_TRUNC関数を使用したら、返す値のデータ型はDATETIME型となる
- 日別の売上データを月別で集計するなどデータを集約したい時に便利
他のBigQueryに関する記事は下記にまとめているので是非参考にして欲しい。
