DataCenterGame/app/etl/dm/dm_pitcher_daily_overview.py

"""
@desc 投手累计数据
@auth ck
"""
from model.DateUtils import DateUtils
from model.DataBaseUtils import MysqlUtils,CkUtils
du = DateUtils()
db= MysqlUtils()
ck = CkUtils()

def 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 = 0, 0, round(this_month_amount / this_month_cost, 4)) this_month_roi,
       last_month_cost,
       last_month_amount,
       if(last_month_cost = 0, 0, last_month_amount / last_month_cost) last_month_roi,
       last_month_far_amount,
       follow_user,
       if(last_month_cost = 0, 0, last_month_far_amount / last_month_cost) last_month_far_roi
from (select pitcher,
             sum(follow_user) follow_user
      from dw_daily_channel
      group by pitcher) q
        left outer join
     (select count(distinct channel) channel_count, pitcher
      from 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 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 dw_daily_channel
    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 dw_daily_channel
    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 order a
             left outer join 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'
    group by pitcher
    ) y on q.pitcher = y.pitcher
left outer join (
    select b.pitcher, sum(amount) last_month_amount
    from order a
             left outer join 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 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 order a
             left outer join 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' 
    group by pitcher
) g on q.pitcher=g.pitcher
"""
    data=ck.execute(sql)
# print(data)
    db.quchen_text.executeMany("replace into dm_pitcher_daily_overview values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",data)

if __name__ == '__main__':
    dm_pitcher_daily_overview()