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

13595 ワード
TreasureData Workflow digdag Presto TreasureData テキストリンク

やりたいこと

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

Linuxの下でC++コードを書いてopencvヘッダファイルを引用して、undefined reference to`cv::imreadの解決方法をコンパイルします
Scala学習14日目Scalaでインタフェースとしてのtrait,対象にtraitコードを混入して実戦