TreasureDataで前月から日毎で集計したい場合のクエリ

利用ケース

例えば、2014-04-01から本日である2014-05-19までのユーザidを集計や累計したい場合に、
TreasureDataが提供するUDFであるTD_TIME_ADDを使いたい。
例えば、前月の指定には、TD_TIME_ADD(time, '-1M', 'JST')など。
しかし、現在は上記のような、TD_TIME_ADDでMonthやYearでの加減が行えない。
http://docs.treasuredata.com/articles/udfs#tdtimeadd

そのため、TD_TIME_ADD(time, '-30d', 'JST')を使っての指定が必要になるが、月の日数は不定であるため、下記サンプルクエリのような一工夫がいる。

(2017-05-29)下記のサンプルは古い。TD_DATE_TRUNCを使うことで前月をもう少しまともにかける。（まだ複雑だが・・・）

WHERE
TD_TIME_RANGE(time,
  TD_DATE_TRUNC('month', TD_TIME_ADD(TD_DATE_TRUNC(month, TD_SCHEDULED_TIME, 'JST'), '-1d', 'JST'), 'JST'),
  TD_SCHEDULED_TIME(),
  'JST'(

やっていることは単純に、TD_DATE_TRUNCで月初を指定し、そこから1日引いて、再度月初にTRUNCしているだけで、
必ず前月の月初を指定できる。（ちょっとめんどくさいが、こっちのほうがよさげ。

古いサンプルクエリ

前月から各日毎のユーザ数の集計を行うクエリ

SELECT 
  date_time,
  COUNT(1) AS cnt
FROM
(
 SELECT 
   uid,
   TD_TIME_FORMAT(time,'yyyy-MM-dd','JST') AS date_time,
   TD_TIME_FORMAT(TD_SCHEDULED_TIME(),'yyyy-MM-dd','JST') AS now_datetime
 FROM access
 WHERE 
  time < TD_SCHEDULED_TIME()
  AND time >= TD_TIME_ADD(TD_SCHEDULED_TIME(),'-64d','JST')
) monthly
WHERE (MONTH(now_datetime)- MONTH(date_time)) IN (0, 1, -11)
GROUP BY
  date_time

古いサンプルクエリ説明

サブクエリ部

このサブクエリでは、テーブルから少なくとも前月までのデータが入る期間を指定している。
2ヶ月*31日 = 62, Timezoneなどを考慮し、念のため+2もしている。

TD_SCHEDULED_TIME()をスケジュールクエリではなく利用すると、現在時刻が格納される。

(
 SELECT 
   uid,
   TD_TIME_FORMAT(time,'yyyy-MM-dd','JST') AS date_time,
   TD_TIME_FORMAT(TD_SCHEDULED_TIME(),'yyyy-MM-dd','JST') AS now_datetime
 FROM access
 WHERE 
  time < TD_SCHEDULED_TIME()
  AND time >= TD_TIME_ADD(TD_SCHEDULED_TIME(),'-64d','JST')
) monthly

条件部

Monthは、日付の文字列から月のみをintで出力する関数。
そこで、(集計日の月 - レコードの日付の月)を実施し、その差分を見て同月か前月かそれ以外かを判断している。

• 0: 同月
• 1: 前月
• -11: 集計日の月が1月、レコードの日付の月が12月の場合

WHERE (MONTH(now_datetime)- MONTH(date_time)) IN (0, 1, -11)