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)
Author And Source
この問題について(TreasureDataで前月から日毎で集計したい場合のクエリ), 我々は、より多くの情報をここで見つけました https://qiita.com/toru-takahashi/items/87518822542b50411d29著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .