DataCenterGame/app/etl/dm/dm_pitcher_daily_overview.py

"""
@desc 投手累计数据
@auth ck
"""
from model.DateUtils import DateUtils
from model.DataBaseUtils import MysqlUtils, CkUtils
import logging

du = DateUtils()
db = MysqlUtils()
ck = CkUtils()


def dm_pitcher_daily_overview():
    logging.info('投手累计数据处理,开始')
    logging.info("run> dm_pitcher_daily_overview")
    sql = f"""
select 
        q.pitcher,
       channel_count,
       on_channel_count,
       channel_count-on_channel_count off_channel_count,
       this_month_cost,
       this_month_amount,
       if(this_month_cost < 1, 0, round(this_month_amount / this_month_cost, 4)) this_month_roi,
       last_month_cost,
       last_month_amount,
       if(last_month_cost < 1, 0, last_month_amount / last_month_cost) last_month_roi,
       last_month_far_amount,
       follow_user,
       if(last_month_cost < 1, 0, last_month_far_amount / last_month_cost) last_month_far_roi
from (select pitcher,
             sum(follow_user) follow_user
      from game_data.dw_daily_channel_cost
      group by pitcher) q
        left outer join
     (select count(distinct channel) channel_count, pitcher
      from game_data.dw_daily_channel_cost
      where dt = '{du.get_n_days(0)}'
      group by pitcher) w
     on q.pitcher = w.pitcher
         left outer join(
    select count(distinct channel) on_channel_count, pitcher
    from game_data.dw_daily_channel_cost
    where dt >= '{du.get_n_days(-15)}'
      and cost > 0
    group by pitcher) e
                        on q.pitcher = e.pitcher
         left outer join (
    select pitcher,
           sum(cost)  this_month_cost
    from game_data.dw_daily_channel_cost
    where dt >= '{du.get_n_pre_month_first_day(0)}'
    group by pitcher) r
                         on q.pitcher = r.pitcher
         left outer join(
    select pitcher,
           sum(cost)  last_month_cost
    from game_data.dw_daily_channel_cost
    where dt >= '{du.get_n_pre_month_first_day(1)}'
      and dt < '{du.get_n_pre_month_first_day(0)}'
    group by pitcher) t on q.pitcher = t.pitcher
         left outer join (
    select b.pitcher, sum(amount) last_month_far_amount
    from game_data.order a
             left outer join game_data.dw_daily_channel_cost b on a.channel = b.channel and a.date = b.dt
    where reg_time >= '{du.get_n_pre_month_first_day(1)} 00:00:00' and reg_time<'{du.get_n_pre_month_first_day(0)} 00:00:00' and status=2 
    group by pitcher
    ) y on q.pitcher = y.pitcher
left outer join (
    select b.pitcher, sum(amount) last_month_amount
    from game_data.order a
             left outer join game_data.dw_daily_channel_cost b on a.channel = b.channel and a.date = b.dt
    where reg_time >= '{du.get_n_pre_month_first_day(1)} 00:00:00' and reg_time<'{du.get_n_pre_month_first_day(0)} 00:00:00' and status=2 
 and dt<'{du.get_n_pre_month_first_day(0)}'
    group by pitcher
) p on q.pitcher=p.pitcher
left outer join (
    select b.pitcher, sum(amount) this_month_amount
    from game_data.order  a
             left outer join game_data.dw_daily_channel_cost b on a.channel = b.channel and a.date = b.dt
    where reg_time >= '{du.get_n_pre_month_first_day(0)} 00:00:00' and status=2 
    group by pitcher
) g on q.pitcher=g.pitcher
"""
    data = ck.execute(sql)
    db.dm.executeMany("replace into dm_pitcher_daily_overview values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", data)
    logging.info('投手累计数据处理,结束')


if __name__ == '__main__':
    dm_pitcher_daily_overview()