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

2023.11.17 2024.11.06

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

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

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

Contents

1 DATE_TRUNC関数とは？
2 DATE_TRUNC関数の使い方
3 DATE_TRUNC関数の活用シーン
4 まとめ

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

SELECT 
  order_date
  ,DATE_TRUNC(order_date,MONTH) as order_month
FROM
  `sample_table`

SELECT

order_date

,DATE_TRUNC(order_date,MONTH) as order_month

FROM

`sample_table`

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

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

SELECT 
  order_date
  ,DATE_TRUNC(order_date,YEAR) as order_year
FROM
  `sample_table`

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関数で月単位に切り捨てたい場合のクエリとその指定の詳細は下記の通りだ。

SELECT
  order_date
  ,DATE_TRUNC(order_date,MONTH) as order_month
FROM
  `sample_table`

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を指定する場合は週の開始曜日を指定して切り捨てを行うことが出来る。

例を確認しよう。

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

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`
;

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関数を使用して月別売上として集計する場合のクエリとその結果が下記の通りだ。

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 /*年月を古い順（昇順）で並び替え*/
;

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句でよく活用される。