はじめに

DATE_TRUNC関数を使うと、日付/時刻型のデータから、任意のフィールドまでデータを切り捨てることが可能です。
年や月という単一の項目でなく、年と月をセットにしてグルーピングしたい場合などに便利です。

構文

DATE_TRUNC（精度、切り捨て対象）

使用例

TIMESTAMP型のデータを対象に、任意の精度での切り捨てを行います。

分以下を切り捨て
dbadmin=> SELECT DATE_TRUNC('HOUR', TIMESTAMP '2019-03-30 13:38:40') AS HOUR;
        HOUR
---------------------
 2019-03-30 13:00:00
(1 row)

日数以下を切り捨て
dbadmin=> SELECT DATE_TRUNC('MONTH', TIMESTAMP '2019-03-30 13:38:40') AS MONTH;
        MONTH
---------------------
 2019-03-01 00:00:00
(1 row)

集計関数への活用

切り捨てを行った結果でグループ化することにより、任意の期間での集計を行えます。
以下の、売上情報を格納するテーブルを例に確認します。
sold_date列の値が、1秒以下の位までデータを保持している点に注目してください。

dbadmin=> dbadmin=> SELECT * FROM prod_sales;
 prod_id | sell_id |         sold_date          | price
---------+---------+----------------------------+-------
 A1      |   10001 | 2019-02-15 22:25:46.996577 |  7.99
 A1      |   10002 | 2019-02-16 17:23:14.996577 |  1.99
 A1      |   10003 | 2019-02-16 22:41:45.996577 |  7.99
（以下省略）

日付データの任意のフィールド以下を切り捨て、切り捨て後の値でグループ化を行うことで、任意の日付単位で集計処理が行えます。

日ごとの累計売上を確認する場合

日ごとに売上高を集計(sold_date列から時間以下を切り捨て)
dbadmin=> SELECT prod_id, DATE_TRUNC('DAY', sold_date) AS DAY, SUM(price)
dbadmin-> FROM prod_sales
dbadmin-> GROUP BY prod_id, DAY
dbadmin-> ORDER BY prod_id, DAY
dbadmin-> ;
 prod_id |         DAY         |   SUM
---------+---------------------+---------
 A1      | 2019-02-14 00:00:00 |  116.73
 A1      | 2019-02-15 00:00:00 | 1188.92
 A1      | 2019-02-16 00:00:00 | 1154.18
（以下省略）

月ごとの累計売上を確認する場合

月ごとに売上を集計(sold_date列から日付以下を切り捨て)
dbadmin=> SELECT prod_id, DATE_TRUNC('MONTH', sold_date) AS MONTH, SUM(price)
dbadmin-> FROM prod_sales
dbadmin-> GROUP BY prod_id, MONTH
dbadmin-> ORDER BY prod_id, MONTH
dbadmin-> ;
 prod_id |        MONTH        |   SUM
---------+---------------------+----------
 A1      | 2019-02-01 00:00:00 |  8351.84
 A1      | 2019-03-01 00:00:00 | 23886.56
 A1      | 2019-04-01 00:00:00 | 28559.46
(3 rows)

同様の結果を得られる関数

変換関数のTO_CHARでも、同様の結果を得られます。

sold_date列の年月のみを表示させる→日付以下の切り捨てと同義
dbadmin=> SELECT prod_id, TO_CHAR(sold_date, 'YYYY-MM') AS MONTH, SUM(price)
dbadmin-> FROM prod_sales
dbadmin-> GROUP BY prod_id, MONTH
dbadmin-> ORDER BY prod_id, MONTH
dbadmin-> ;
 prod_id |  MONTH  |   SUM
---------+---------+----------
 A1      | 2019-02 |  8351.84
 A1      | 2019-03 | 23886.56
 A1      | 2019-04 | 28559.46
(3 rows)

ただし、TO_CHAR関数はデータ変換のオーバヘッドがあるため、クエリの性能面では、データの切り捨て処理をするだけのDATE_TRUNCに分があります。
大量データの処理であれば、DATE_TRUNCの使用をおすすめします。

参考：上記のTO_CHAR・DATE_TRUNCを本記事の集計クエリに使用した場合のコスト(※)

※クエリのコストの確認方法は、以下の記事をご確認ください。
SQLの実行計画を確認する方法
https://www.ashisuto.co.jp/cm/analytics-database/sql-plan.html

分析関数への活用

ここからの内容は、分析関数の理解が前提知識として必要です。
分析関数については以下の記事をご確認ください。

分析関数(ウィンドウ関数)の概要
https://www.ashisuto.co.jp/cm/analytics-database/window_function.html

DATE_TRUNC関数では、日付データを任意の粒度に変換できるため、分析関数のPARTITION BY句にも活用できます。
使用例は以下です。

日ごとの売上を集計(day_sales)した後、
月区切りのウィンドウごとに、現在行までの売上を集計(sum_day_sales)
dbadmin=> SELECT DATE_TRUNC('MONTH', sold_date) AS MONTH
dbadmin->       ,DATE_TRUNC('DAY', sold_date) AS DAY
dbadmin->       ,SUM(price) AS day_sales
dbadmin->       ,SUM(SUM(price)) OVER(PARTITION BY DATE_TRUNC('MONTH', sold_date)
dbadmin(>                            ORDER BY DATE_TRUNC('DAY', sold_date)) AS sum_day_sales
dbadmin-> FROM prod_sales
dbadmin-> GROUP BY MONTH, DAY
dbadmin-> ORDER BY MONTH, DAY
dbadmin-> ;
        MONTH        |         DAY         | day_sales | sum_day_sales
---------------------+---------------------+-----------+---------------
 2019-02-01 00:00:00 | 2019-02-14 00:00:00 |    116.73 |        116.73
 2019-02-01 00:00:00 | 2019-02-15 00:00:00 |   1188.92 |       1305.65
 2019-02-01 00:00:00 | 2019-02-16 00:00:00 |   1154.18 |       2459.83
（省略）
 2019-03-01 00:00:00 | 2019-03-01 00:00:00 |   2808.24 |       2808.24
 2019-03-01 00:00:00 | 2019-03-02 00:00:00 |   2550.05 |       5358.29
 2019-03-01 00:00:00 | 2019-03-16 00:00:00 |    299.28 |       5657.57
（省略）
 2019-04-01 00:00:00 | 2019-04-05 00:00:00 |    273.36 |        273.36
 2019-04-01 00:00:00 | 2019-04-06 00:00:00 |   2077.14 |       2350.50
 2019-04-01 00:00:00 | 2019-04-07 00:00:00 |   1984.28 |       4334.78
（省略）

上記のクエリの大まかな処理の流れは以下です。
1．月と日にちでグループ化(1,2,7行目)
2．日毎の売上の合計を集計(3行目：SUM集計関数を使用)
3．月でウィンドウ化し、ある月中の日毎の累計売上を表示(4,5行目：SUM分析関数を使用)

このように、DATE_TRUNC関数を活用すれば、ミリ秒までの細かい情報を保持しているTIMESTAMP型のデータであっても、月単位/日付単位/秒単位など様々な粒度でデータ分析を行えます。

参考情報

DATE_TRUNC
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/DATE_TRUNC.htm

検証バージョンについて

この記事の内容はVertica 9.2で確認しています。

更新履歴

2019/05/15　本記事を公開