TDでの月次ユニークユーザーの日次集計

やりたいこと

アクセスログの集計をさかのぼってやりたい。Tableauに出すために。
ユニークユーザーって範囲が変わるたびに計算しなおさなきゃならないから本当こういうとき面倒。一日ずつずらしながら集計しなおさなきゃならない。
しかも、昨年対比したいから、昨年分までさかのぼってデータつくらなきゃならない。さてどうしたもんか。

アウトプットイメージはこんな感じ (tableau 用に縦持ちね)

dt	key	date_range	val
2019-06-05	monthly_uu	2019-06-01 - 2019-06-05	290358
2019-06-05	monthly_pv	2019-06-01 - 2019-06-05	2214702
2019-06-06	monthly_uu	2019-06-01 - 2019-06-06	390358
2019-06-06	monthly_pv	2019-06-01 - 2019-06-06	2364702
2019-06-07	monthly_uu	2019-06-01 - 2019-06-07	3264702
2019-06-07	monthly_pv	2019-06-01 - 2019-06-07	434702
・	・	・
・	・	・
2018-06-07	monthly_uu	2018-06-01 - 2018-06-07	543555
2018-06-07	monthly_pv	2018-06-01 - 2018-06-07	2264702

日付を変数にしながらループする（だけ）

roop.dig

_export:
  td:
    database: webcounter

+step1:
  td_ddl>:
  empty_tables: ["weblog_cnt"]

+repeat1:
  loop>: 365
  _do:
    +step1_pv:
      td>: queries/01_pv.sql
      database: ${td.database}
      adddate: ${i}
      engine: presto
      insert_into: weblog_cnt

+repeat2:
  loop>: 365
  _do:      
    +step2_uu:
      td>: queries/02_uu.sql
      database: ${td.database}
      adddate: ${i}
      engine: presto
      insert_into: weblog_cnt

てな感じで、loop>オペレーターを使うとiに変数いれてくれますね。

loop> operator runs subtasks multiple times.
This operator exports ${i} variable for the subtasks. Its value begins from 0. For example, if count is 3, a task runs with i=0, i=1, and i=2.

01_pv.sql

select 
  TD_TIME_FORMAT(td_time_add(TD_SCHEDULED_TIME(),'-${i}d','jst'),'yyyy-MM-dd','jst') as dt, --${i}はloopがくれる変数 0 から始まる
  'monthly_pv' as key,
   TD_TIME_FORMAT(TD_DATE_TRUNC('MONTH',td_time_add(TD_SCHEDULED_TIME(),'-${i}d','jst'),'jst'),'yyyy-MM','jst') as ym ,
   CONCAT(TD_TIME_FORMAT(TD_DATE_TRUNC('MONTH',td_time_add(TD_SCHEDULED_TIME(),'-${i}d','jst'),'jst'),'yyyy-MM-dd','jst') ,' - ',TD_TIME_FORMAT(td_time_add(TD_SCHEDULED_TIME(),'-${i}d','jst'),'yyyy-MM-dd','jst')) as date_range,
   count(1) as val --pvなので単純カウント 該当日時点のPV
from 
  weblog 
where 
  TD_TIME_RANGE(time, 
　　　　TD_DATE_TRUNC('MONTH',
　　　　　　td_time_add(TD_SCHEDULED_TIME(),'-${i}d','jst')　-- time_rangeのFROM:td_date_trunkでスケジュールタイムマイナス{i}の日が存在する月の最初の日にする ex)2019-06-01
　　　　,'jst')
　　, td_time_add(TD_SCHEDULED_TIME(),'-${i}d','jst')-- time_rangeのto:スケジュールタイムマイナス{i}の日まで
　　)

02_uu.sql

select 
  TD_TIME_FORMAT(td_time_add(TD_SCHEDULED_TIME(),'-${i}d','jst'),'yyyy-MM-dd','jst') as dt, --${i}はloopがくれる変数 0 から始まる
  'monthly_pv' as key,
   TD_TIME_FORMAT(TD_DATE_TRUNC('MONTH',td_time_add(TD_SCHEDULED_TIME(),'-${i}d','jst'),'jst'),'yyyy-MM','jst') as ym ,
   CONCAT(TD_TIME_FORMAT(TD_DATE_TRUNC('MONTH',td_time_add(TD_SCHEDULED_TIME(),'-${i}d','jst'),'jst'),'yyyy-MM-dd','jst') ,' - ',TD_TIME_FORMAT(td_time_add(TD_SCHEDULED_TIME(),'-${i}d','jst'),'yyyy-MM-dd','jst')) as date_range,
   count(distinct(td_client_id)) as val --uuなのでdistinctカウント 該当日時点のUU
from 
  weblog 
where 
  TD_TIME_RANGE(time, 
　　　　TD_DATE_TRUNC('MONTH',
　　　　　　td_time_add(TD_SCHEDULED_TIME(),'-${i}d','jst')　-- time_rangeのFROM:td_date_trunkでスケジュールタイムマイナス{i}の日が存在する月の最初の日にする ex)2019-06-01
　　　　,'jst')
　　, td_time_add(TD_SCHEDULED_TIME(),'-${i}d','jst')-- time_rangeのto:スケジュールタイムマイナス{i}の日まで
　　)

こりゃいいやってことで365回ﾙｰﾌﾟしましたのです。