DataCenterGame/app/etl/data_stat_task.py

from model.DataBaseUtils import MysqlUtils,CkUtils
from model.DateUtils import DateUtils
from model.log import logger
log=logger()
db = MysqlUtils()
ck = CkUtils()
dt = DateUtils()
from datetime import datetime


def dw_daily_channel(dt):
    sql = """select '{0}' as dt,c.channel as channel,pitcher,stage,order_count,order_user,order_amount,first_order_count,first_order_user,first_order_amount,
          view_count,click_count, cost

           from
    (select channel,order_count,order_user,order_amount,first_order_count,first_order_user,first_order_amount from
    (select channel,count(1) as order_count,count(distinct user_id) as order_user,sum(amount) as order_amount from order
    where date='{0}'
    group by channel) a
        left outer JOIN

    (select channel,count(1) as first_order_count,
    count(distinct user_id) as first_order_user,
    sum(amount) as first_order_amount from order
     where formatDateTime(reg_time,'%Y-%m-%d')='{0}' and date ='{0}'
     group by channel )  b
        on a.channel=b.channel) p

      right OUTER JOIN

    ( select channel,pitcher,stage,sum(cost) as cost, sum(view_count) as view_count,sum(valid_click_count) as click_count from
    (select account_id,cost,view_count,valid_click_count  from daily_qq where date='{0}'
     union all
     select account_id,cost,view_count,valid_click_count from daily_vx where date='{0}') h
    right outer join
    (select account_id,name as channel,stage,pitcher from advertiser_vx_qq where name!='') j on h.account_id=j.account_id
        group by channel,stage,pitcher) c
        on p.channel=c.channel""".format(dt)

    data = ck.execute(sql)

    data1 = []
    for i in data:
        li = list(i)
        li[6] = int(li[6])
        li[9] = int(li[9])
        data1.append(tuple(li))

    insert_sql = "insert into dw_daily_channel values {}".format(str(data1)[1:-1])
    # print(insert_sql)
    db.quchen_text.execute(insert_sql)


def clean_order(dt):
    sql="""	replace into ods_order
			select 
			case platform when '掌中云' then DATE_FORMAT(STR_TO_DATE(order_time,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d') 
            when '掌读' then from_unixtime(order_time, '%Y-%m-%d') 
            ELSE left(order_time,10) end  date,
            stage,platform,channel,channel_id,user_id,
            case when platform='掌中云' then DATE_FORMAT(STR_TO_DATE(order_time,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d %H:%i:%s') 
            when platform='掌读' then from_unixtime(order_time, '%Y-%m-%d %H:%i:%s') 
            ELSE order_time end order_time,
            case when platform='掌中云' then DATE_FORMAT(STR_TO_DATE(reg_time,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d %H:%i:%s') 
            when platform='掌读' then from_unixtime(reg_time, '%Y-%m-%d %H:%i:%s') 
            ELSE reg_time end reg_time,
			amount,from_novel,order_id from `order` where date=UNIX_TIMESTAMP('{}')
			""".format(dt)
    db.quchen_text.execute(sql)

if __name__ == '__main__':

    li=dt.getDateLists('2019-03-01','2020-01-01')
    for i in  li:
        print(i)
        clean_order(i)