やりたいこと
アクセスログの集計をさかのぼってやりたい。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回ﾙｰﾌﾟしましたのです。