DataCenterGame/example/bytedance_info_tmp_insert.py

from model.DateUtils import DateUtils
from model.DataBaseUtils import MysqlUtils, CkUtils

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


def insert_data():
    bt_sql = '''
    select b.pitcher,b.channel ,DATE_FORMAT(a.`date`, '%Y-%m-%d'),
    round(sum(cost)/100,2) as cost,sum(view_count) as view_count ,
    sum(valid_click_count) as click_count from daily_tt a 
    left join bytedance_pitcher_change b
    on a.account_id =b.advertiser_id 
    group by b.pitcher ,a.`date` ,b.channel 
    '''
    byte_list = db.quchen_text.get_data_list(bt_sql)
    isql = "insert into bytedance_info_tmp values (%s,%s,%s,%s,%s,%s)"
    db.dm.execute("truncate table bytedance_info_tmp")
    db.dm.executeMany(isql, byte_list)


def dw_daily_bytedance_cost(ymd):
    dt_sql = f'''
    select b.pitcher,b.channel ,a.`date` ,round(sum(cost)/100,2) as cost,sum(view_count) as view_count ,
    sum(valid_click_count) as click_count ,c.stage as stage,d.book as book, e.current_platform as platform
    from daily_tt a 
    left join bytedance_pitcher_change b on a.account_id =b.advertiser_id 
    left join stage_change c on b.channel =c.channel 
    left join book_change d on b.channel =d.name 
    left join platform_change e on b.channel = e.name
    where a.`date`='{ymd}'
    group by b.pitcher ,a.`date` ,b.channel 
    '''

    data_list = db.quchen_text.get_data_list(dt_sql)
    byte_list = []
    for _ in data_list:
        _[2] = str(_[2]) if _[2] else 0
        _[3] = round(float(_[3]), 2) if _[3] else 0
        _[4] = round(float(_[4]), 2) if _[4] else 0
        _[5] = round(float(_[5]), 2) if _[5] else 0
        _[6] = str(_[6]) if _[6] else ''
        _[7] = str(_[7]) if _[7] else ''
        _[8] = str(_[8]) if _[8] else ''
        _.append('BYTEDANCE')
        byte_list.append(tuple(_))
    col = '''pitcher,channel,dt,cost,view_count,click_count,stage,book,platform,type'''
    print(byte_list)
    ck.execute(f"alter table dw_daily_bytedance_cost drop  partition '{ymd}' ")

    ck.insertMany("dw_daily_bytedance_cost", col, tuple(byte_list))


def replace_data():
    channel_name='BYTEDANCE'
    sql = f"""
select
       dt1,channel1,pitcher,stage,platform,book,'{channel_name}' type,
       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,
       web_view_count,platform_view_count,web_order_count,
       reg_order_user_again,
       reg_order_user_again3,
       reg_order_user_again4,
       reg_order_user_again5,
       reg_order_user_again6
from (
    select 
    if(dt4='1970-01-01',dt,dt4) dt1,if(channel4='',channel,channel4) channel1,
    pitcher,stage,platform,book,cost,view_count,click_count,   ---基础属性和消耗数据
        follow_user,web_view_count,platform_view_count,web_order_count,'{channel_name}' type,
        require_roi,require_mult,order_count,order_user,order_amount from

        (select * from dw_daily_bytedance_cost) aa
        left  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 where status=2    group by date,channel) dd
        on dt=dt4 and channel=channel4) a

left outer join (
    select sum(if(user_order_count>=2,1,0)) reg_order_user_again,channel channel6,toDate(reg_date) dt6,  ---复冲人数
           sum(if(user_order_count>=3,1,0)) reg_order_user_again3,
           sum(if(user_order_count>=4,1,0)) reg_order_user_again4,
           sum(if(user_order_count>=5,1,0)) reg_order_user_again5,
           sum(if(user_order_count>=6,1,0)) reg_order_user_again6
    from (select formatDateTime(reg_time,'%Y-%m-%d') reg_date,channel,count(1) user_order_count
    from order where status=2  group by formatDateTime(reg_time,'%Y-%m-%d') ,user_id,channel) x group by reg_date,channel
    ) f on dt1=dt6 and channel1=channel6

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
   from order where status=2 and reg_time>'2019-03-18 00:00:00' group by toDate(formatDateTime(reg_time,'%Y-%m-%d')),channel) b
    on dt1=dt2 and channel1=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 status=2 and toDate(reg_time)=date  group by date,channel) c
    on dt1=dt3 and channel1=channel3    

    having order_amount+cost+reg_order_amount>0"""

    data = ck.execute(sql)
    print(data)

    isql = "replace into dw_channel 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)"
    # db.dm.execute("truncate table dw_channel")
    db.dm.executeMany(isql, data)

def platform_data_sum(ymd):
    ck.execute("alter table dw_daily_platform_cost drop  partition '{}' ".format(ymd))
    sql=f'''
        insert into dw_daily_platform_cost
        select * from 
        (select * from dw_daily_bytedance_cost a where dt='{ymd}'
        union all 
        select * from dw_daily_channel_cost b where dt='{ymd}'
        AND channel not  in (select channel from dw_daily_bytedance_cost a 
        where dt='{ymd}'))
    '''
    ck.execute(sql)


if __name__ == '__main__':
    dt = DateUtils()

    # insert_data()
    for i in dt.getDateLists('2021-09-18','2021-09-23'):
        print(i)
        dw_daily_bytedance_cost(i)
        platform_data_sum(i)
    exit()
    # replace_data()
    for i in dt.getDateLists('2019-03-18','2021-10-17'):
        print(i)
        platform_data_sum(i)