DataCenterGame/app/etl/dm/dm_channel_summary.py

import time
from model.DateUtils import DateUtils
from model.DataBaseUtils import MysqlUtils,CkUtils
du = DateUtils()
db= MysqlUtils()
ck = CkUtils()




def dm_channel_summary():
    sql="""	 SELECT channel,
                if(end>date_sub(now(),interval 10 day),'在投','停投') state,
                location,start,end,total_cost,total_amount,
                total_amount-total_cost profit,
                if(total_cost=0,0,total_amount/total_cost) roi,
                follow_user,
                if(follow_user=0,0,total_cost/follow_user) follow_per_cost,
                order_user,
                if(follow_user=0,0,order_user/follow_user) order_tran_rate,
                if(order_user=0,0,total_cost/order_user)  order_tran_cost,
                pitcher,stage
            FROM
                (select
                    channel,pitcher,stage,
                    case when type ='vx' then 'MP' when type ='qq' then 'GDT' end location,
                    min(if(cost>0,dt,null)) start,
                    max(if(cost>0,dt,null)) end,
                    sum(cost) total_cost,
                    sum(order_amount) total_amount,
                    sum(follow_user) follow_user,
					sum(reg_order_user) order_user
               from dw_daily_channel   GROUP BY channel,type,pitcher,stage) a"""

    data=ck.execute(sql)
    db.quchen_text.executeMany("replace into dm_channel_summary values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",data)




if __name__ == '__main__':
    dm_channel_summary()