DataCenterGame/app/etl/dw/dw_channel_daily.py

"""
@desc 号维度全量表 ck上跑完dw_daily_channel 并同步到mysql dw_channel_daily
@auth ck
"""
import time
from model.DateUtils import DateUtils
from model.DataBaseUtils import MysqlUtils,CkUtils
du = DateUtils()
db= MysqlUtils()
ck = CkUtils()


def dw_daily_channel():

    sql="""insert into dw_daily_channel
select
       dt,channel,pitcher,stage,platform,book,
       order_count,order_user,order_amount,first_order_count,first_order_user,first_order_amount,
       view_count,click_count,follow_user,cost,reg_order_count,reg_order_user,
       reg_order_amount,reg_order_amount30,web_view_count,platform_view_count,web_order_count,total_cost,
       total_amount,reg_order_user_again,reg_order_user7,reg_order_user30,reg_order_amount7,type,
       total_first_amount 
from
(select dt,channel, pitcher,stage,platform,book,cost,view_count,click_count,follow_user,web_view_count,platform_view_count,web_order_count,type from dw_daily_channel_cost) a
    left outer join
    (select toDate(formatDateTime(reg_time,'%Y-%m-%d')) as dt2,channel as channel2,
       sum(amount) as reg_order_amount,
       count(distinct user_id) as reg_order_user,
       count(1) as reg_order_count,
       sum(if(subtractDays(date, 30)>reg_time,0,amount)) as reg_order_amount30,
       count(distinct if(subtractDays(date, 7)>reg_time,'',user_id))-1 reg_order_user7,
       sum(if(subtractDays(date, 7)>reg_time,0,amount)) as reg_order_amount7,
       count(distinct if(subtractDays(date, 30)>reg_time,'',user_id))-1 reg_order_user30
       from order where reg_time>'2019-03-18 00:00:00' group by toDate(formatDateTime(reg_time,'%Y-%m-%d')),channel) b
        on dt=dt2 and channel=channel2 left outer join
     (select date as dt3,channel as channel3,
     count(1) as first_order_count,
        count(distinct user_id) as first_order_user,
             sum(amount) as first_order_amount
       from order where toDate(reg_time)=date  group by date,channel) c
        on dt=dt3 and channel=channel3
    left outer join
    (select date as dt4,channel as channel4,
     count(1) as order_count,
        count(distinct user_id) as order_user,
             sum(amount) as order_amount
       from order  group by date,channel) d
        on dt=dt4 and channel=channel4
left outer join
     (select dt dt5,channel channel5,total_cost,total_amount,total_first_amount from dw_channel_daily_total ) e
         on dt=dt5 and channel=channel5

left outer join (
select sum(if(user_order_count>1,1,0)) reg_order_user_again,channel channel6,toDate(reg_date) dt6  from (
select formatDateTime(reg_time,'%Y-%m-%d') reg_date,channel,count(1) user_order_count
    from order group by formatDateTime(reg_time,'%Y-%m-%d') ,user_id,channel
    ) x group by reg_date,channel
    ) f on dt=dt6 and channel=channel6
        """
    ck.execute("truncate table dw_daily_channel")
    ck.execute(sql)
    print("ok")

    data=ck.execute('select * from dw_daily_channel')
    insert_sql="replace into dw_channel_daily values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    st=time.time()
    db.quchen_text.executeMany(insert_sql,data)
    print(time.time()-st)


if __name__ == '__main__':

    dw_daily_channel()