今回は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 |
1 2 3 4 5 | SELECT order_date ,DATE_TRUNC(order_date,MONTH) as order_month FROM `sample_table` |
order_dateの各値がDATE_TRUNC関数により、その月の最初の日＝1日に統一されたことがお分かりいただけると思う。
もし月単位ではなく、年単位に切り捨てたい場合は、下記のようにDATE_TRUNC関数でYEARを指定すればOKだ。
1 2 3 4 5 | SELECT order_date ,DATE_TRUNC(order_date,YEAR) as order_year FROM `sample_table` |
DATE_TRUNC関数により、各値が年単位に丸められ、その年の最初の日＝1月1日に丸められている。
このようにDATE_TRUNC関数は日付データを指定した単位に切り捨てたい時に非常に便利な関数だ。
DATE_TRUNC関数の使い方
DATE_TRUNC関数は下記を指定して使用する。
- 対象カラム：値の切り捨てを行いたいDATE型カラムを指定
- 単位：どの日付の単位まで丸めるかの指定
具体例に当てはめて確認してみよう。
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関数で月単位に切り捨てたい場合のクエリとその指定の詳細は下記の通りだ。
1 2 3 4 5 | SELECT order_date ,DATE_TRUNC(order_date,MONTH) as order_month FROM `sample_table` |
上記クエリの結果は下記となり、order_dateの各値から日以下の値が切り捨てられ、1日で統一されている。
DATE_TRUNC関数で指定できる日付の単位は下記の通りだ。
個人的には月単位に丸めるためにDATE_TRUNC関数を使用することが圧倒的に多い。
| 日付の単位 | 説明 |
|---|---|
YEAR | 年単位に丸める |
| QUARTER | 四半期単位に丸める |
| MONTH | 月単位に丸める |
| WEEK | 週単位に丸める（週の開始曜日は指定可能） |
| DAY | 日単位に丸める |
WEEKを指定する場合は週の開始曜日を指定して切り捨てを行うことが出来る。
例を確認しよう。
週の開始曜日を指定する場合のクエリと結果は下記の通りだ。
1 2 3 4 5 6 7 | SELECT order_date ,DATE_TRUNC(order_date,WEEK(SUNDAY)) as order_week_sunday ,DATE_TRUNC(order_date,WEEK(TUESDAY)) as order_week_tuesday FROM `sample_table` ; |
こちらの例では週の開始曜日を日曜日と火曜日に設定して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関数を使用して月別売上として集計する場合のクエリとその結果が下記の通りだ。
1 2 3 4 5 6 7 8 | SELECT DATE_TRUNC(order_date,MONTH) as order_month ,SUM(sales) as sales_amount FROM `sample_table` GROUP BY order_month /*order_dateを月単位に丸めたカラムで集計*/ ORDER BY order_month /*年月を古い順（昇順）で並び替え*/ ; |
DATE_TRUNC関数により、日別の売上データが月別に集計されていることがお分かりいただけると思う。
このようにDATE_TRUNC関数はデータを集約するためにWHERE句やGROUP BY句でよく活用される。
まとめ
BigQueryにおけるDATE_TRUNC関数の要点は下記の通りだ。
- DATE_TRUNC関数は指定した日付単位まで日付データを丸めてくれる関数
- 日別の売上データを月別で集計するなどデータを集約したい時に便利
他のBigQueryに関する記事は下記にまとめているので是非参考にして欲しい。
