【トレジャーデータ：Presto】自動でMAUを集計する（TD_TIME_TRUNC）

トレジャーデータ（Presto）で日数を計算する方法としてTD_TIME_ADDがありますが、月や年単位の計算をサポートしていません。そこで、TD_DATE_TRUNCを使って月単位の期間を指定してMAUを自動で集計する方法を紹介します。

◇TD_DATE_TRUNCの使い方（トレジャーデータサイトより）

1. SELECT
2.   TD_TIME_FORMAT(time,'yyyy-MM-dd HH:mm:ss','JST') AS t,
3.   TD_DATE_TRUNC('minute',  time, 'JST' ) AS mnt,
4.   TD_DATE_TRUNC('hour',    time, 'JST' ) AS h,
5.   TD_DATE_TRUNC('day',     time, 'JST' ) AS d,
6.   TD_DATE_TRUNC('week',    time, 'JST' ) AS w,
7.   TD_DATE_TRUNC('month',   time, 'JST' ) AS m,
8.   TD_DATE_TRUNC('quarter', time, 'JST' ) AS q,
9.   TD_DATE_TRUNC('year',    time, 'JST' ) AS y
10. FROM access_log
11. WHERE TD_TIME_RANGE(time,'2017-01-15','2017-01-16','JST')
12. ORDER BY t DESC
13. LIMIT 1

実行結果（トレジャーデータサイトより）

◇TD_DATE_TRUNCを使ったMAU自動集計例

timeは”2019-10-17 08:21:36.000”のような時間文字列を想定。そのため、TD_TIME_PARSEでタイムスタンプに変換しています（TD_TIME_PARSEとは）。また、トレジャーデータのスケジュール機能（TD_SCHEDULED_TIME）で月初に自動で実行するようにしているため、月初の日付に-1dとすることで前月の月初から月末まで集計できるようにしています。

SELECT
  TD_TIME_FORMAT(TD_TIME_PARSE(time,
      'UTC'),
    'yyyy-MM',
    'UTC') AS time,
  COUNT(DISTINCT user_id) AS UU
FROM
  all_beaconlog
WHERE
  TD_TIME_RANGE(TD_TIME_PARSE(time,
      'UTC'),
    TD_DATE_TRUNC('month',
      TD_TIME_ADD(TD_DATE_TRUNC('month',
          TD_SCHEDULED_TIME(),
          'UTC'),
        '-1d'),
      'UTC'),
    TD_DATE_TRUNC('month',
      TD_SCHEDULED_TIME(),
      'UTC'),
    'UTC')
GROUP BY
  TD_TIME_FORMAT(TD_TIME_PARSE(time,
      'UTC'),
    'yyyy-MM',
    'UTC')
ORDER BY
  time