目次
はじめに
DATE_TRUNC関数を使うと、日付/時刻型のデータから、任意のフィールドまでデータを切り捨てることが可能です。年や月という単一の項目でなく、年と月をセットにしてグルーピングしたい場合などに便利です。
構文
|
1 |
DATE_TRUNC（精度、切り捨て対象） |
| パラメータ名 | 内容 |
|---|---|
| 精度 | 後述の表の値のいずれかを指定します。 指定した精度より小さい値を切り捨てます。 ※日と月は1に、それ以外のフィールドは０に設定されます。 |
| 切り捨て対象 | 有効な日付/時刻データ |
| 精度 | 内容 |
|---|---|
| MILLENNIUM | 千年紀(西暦の1000年区切り) |
| CENTURY | 世紀(西暦の100年区切り) |
| DECADE | 十年紀(西暦の10年区切り) |
| YEAR | 年 |
| QUARTER | 1年を4半期に区切った数値 1-3月＝1 |
| MONTH | 月(1-12) |
| WEEK | 週の番号(1-52,53,54) ※最大値は年により変動 |
| DAY | 日(1-31) |
| HOUR | 時間(0-23) |
| MINUTE | 分(0-59) |
| SECOND | 秒(0-59) |
| MILLISECONDS | 小数部を含む秒フィールドに1000を掛けた値 |
| MICROSECONDS | 小数部を含む秒フィールドに1,000,000を掛けた値 |
使用例
TIMESTAMP型のデータを対象に、任意の精度での切り捨てを行います。|
1 2 3 4 5 6 |
分以下を切り捨て dbadmin=> SELECT DATE_TRUNC('HOUR', TIMESTAMP '2019-03-30 13:38:40') AS HOUR; HOUR --------------------- 2019-03-30 13:00:00 (1 row) |
|
1 2 3 4 5 6 |
日数以下を切り捨て 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秒以下の位までデータを保持している点に注目してください。
|
1 2 3 4 5 6 7 |
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 （以下省略） |
日付データの任意のフィールド以下を切り捨て、切り捨て後の値でグループ化を行うことで、任意の日付単位で集計処理が行えます。
日ごとの累計売上を確認する場合
|
1 2 3 4 5 6 7 8 9 10 11 12 |
日ごとに売上高を集計(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 （以下省略） |
月ごとの累計売上を確認する場合
|
1 2 3 4 5 6 7 8 9 10 11 12 |
月ごとに売上を集計(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でも、同様の結果を得られます。|
1 2 3 4 5 6 7 8 9 10 11 12 |
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を本記事の集計クエリに使用した場合のコスト(※)
| 関数 | コスト |
|---|---|
| TO_CHAR | 285 |
| DATE_TRUNC | 135 |
※クエリのコストの確認方法は、以下の記事をご確認ください。
SQLの実行計画を確認する方法
http://vertica-tech.ashisuto.co.jp/sql-plan/
分析関数への活用
ここからの内容は、分析関数の理解が前提知識として必要です。分析関数については以下の記事をご確認ください。
分析関数(ウィンドウ関数)の概要
http://vertica-tech.ashisuto.co.jp/window_function/
DATE_TRUNC関数では、日付データを任意の粒度に変換できるため、分析関数のPARTITION BY句にも活用できます。
使用例は以下です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
日ごとの売上を集計(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_TRUNChttps://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/DATE_TRUNC.htm
検証バージョンについて
この記事の内容はVertica 9.2で確認しています。更新履歴
2019/05/15 本記事を公開- 投稿タグ
- 関数, 分析関数, ウィンドウ関数, date_trunc
