現場で役立つデータ分析を学ぶ

データ分析大学

Treasure Data

トレジャーデータ 自動でMAUを集計する

更新日:

トレジャーデータ(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



データ分析備忘録より記事を移管中

-Treasure Data

Copyright© データ分析大学 , 2020 All Rights Reserved Powered by STINGER.