DataCenterGame/app/etl/dw/image_day.py

import logging
from model.DataBaseUtils import MysqlUtils,CkUtils
from model.DateUtils import DateUtils
# logging.getLogger().setLevel(logging.WARNING)
import pandas as pd
db = MysqlUtils()
ck = CkUtils()
du=DateUtils()
def run(dt):
    # 单图片的消耗
    sql=f"""replace into dm_image_cost_day 
            select a.dt,type,book,signature,sum(cost),sum(view_count),sum(click_count),sum(follow_count),min(preview_url) from 
            (SELECT a.dt,a.ad_id,a.cost,c.image_id,preview_url,signature,view_count,click_count,follow_count,ad_name,b.type,title,description,e.channel,stage,pitcher,platform,book
            from 
            ad_cost_day a 
            left join ad_info b on a.ad_id=b.ad_id
            left join adcreative_info c on b.adcreative_id=c.adcreative_id
            left join channel_by_account_daily e on b.account_id=e.account_id and a.dt=e.dt
            left join channel_info_daily f on e.channel=f.channel and e.dt=f.dt
            left join image_info g on c.image_id=g.image_id
            where a.dt='{dt}' and INSTR(c.image_id,',')=0 and preview_url is not null  ) a
            GROUP BY signature,book,type,a.dt 
            
                        """
    db.quchen_text.execute(sql)

    # 多图的

def run2(dt):
    sql2 = f"""
            SELECT dt,image_id,sum(cost),sum(view_count),sum(click_count),sum(follow_count),type,book
            FROM
            
            (SELECT a.dt,a.ad_id,a.cost,c.image_id,view_count,click_count,follow_count,ad_name,b.type,title,description,e.channel,stage,pitcher,platform,book
            from 
            ad_cost_day a 
            left join ad_info b on a.ad_id=b.ad_id
            left join adcreative_info c on b.adcreative_id=c.adcreative_id
            left join channel_by_account_daily e on b.account_id=e.account_id and a.dt=e.dt
            left join channel_info_daily f on e.channel=f.channel and e.dt=f.dt
            
            where a.dt='{dt}' and INSTR(c.image_id,',')>0 ) a
            group by image_id,dt,type,book
"""

    data =db.quchen_text.get_data_list(sql2)
    # print(data)
    li=[]
    for i in data:
        li.extend(i[1].split(','))
    # print(set(li))

    sql3 =f"select image_id,preview_url,signature from image_info where  image_id in ({str(set(li))[1:-1]})"

    image_di={}
    di2 = {}
    image_data =db.quchen_text.getData(sql3)
    for x in image_data:
        image_di[x[0]]=x[1]
        di2[x[0]]=x[2]
    # print(image_di)


    for i in data:
        y = ''
        p =''
        for j in i[1].split(','):
            if image_di.get(j):
                y = y+','+image_di.get(j)
                p = p+','+di2.get(j)
        i.append(y[1:])
        i.append(p[1:])


    df = pd.DataFrame(data)
    # print(df)

    df2 =df.groupby([0,6,7,9],as_index=False).agg({2:'sum',3:'sum',4:'sum',5:'sum',8:'min'})
    # print(df2)
    # print()

    db.quchen_text.executeMany('replace into dm_image_cost_day values(%s,%s,%s,%s,%s,%s,%s,%s,%s)',df2.values.tolist())



def day():
    for i in du.getDateLists('2021-03-26','2021-04-08'):
        run(i)
        run2(i)


if __name__ == '__main__':

    day()