DataCenterGame/app/etl/MaterialLibrary/ddd.py

"""
desc : 素材库数据清洗
每日运行一次

"""
from model.DataBaseUtils import MysqlUtils
db =MysqlUtils()

def title():
    sql = """select REPLACE(REPLACE(title, CHAR(10), ''), CHAR(13), '') content,
        1 type,
        0 create_by,
        sum(click_count) click_times,
        sum(view_count) view_times,
        group_concat(distinct book) novels,
        max(dt) end_date,min(dt) start_date
        from dw_image_cost_day where title!='' and title is not null  GROUP BY REPLACE(REPLACE(title, CHAR(10), ''), CHAR(13), '')  limit 100
				"""

    df =  db.dm.getData_pd(sql)
    print(df)

    key = ["content", "type"]
    tag = ["view_times", "click_times", "novels", "start_date", "end_date", "create_by"]
    table = "t_ads_content"

    db.zx_test.dfsave2mysql(df, table, key, tag)

def description():
    sql = """select REPLACE(REPLACE(description, CHAR(10), ''), CHAR(13), '') content,
            2 type,
            0 create_by,
            sum(click_count) click_times,
            sum(view_count) view_times,
            group_concat(distinct book) novels,
            max(dt) end_date,min(dt) start_date
            from dw_image_cost_day where description!='' and description is not null  GROUP BY REPLACE(REPLACE(description, CHAR(10), ''), CHAR(13), '')  limit 100
    				"""

    df = db.dm.getData_pd(sql)
    print(df)

    key = ["content", "type"]
    tag = ["view_times", "click_times", "novels", "start_date", "end_date", "create_by"]
    table = "t_ads_content"

    db.zx_test.dfsave2mysql(df, table, key, tag)


def image():
    sql="""select signature,
            sum(click_count) click_times,
            sum(view_count) view_times,
            group_concat(distinct book) novels ,
            max(dt) end_date,
            min(dt) start_date,
            min(preview_url) content,
            1 type,
            if(locate(',',signature)>0,0,1) single_img,
            0 create_by  
            from dw_image_cost_day where dt='2021-05-10'   GROUP BY  signature"""

    df = db.dm.getData_pd(sql)
    print(df)

    key = ["signature"]
    tag = ["view_times", "click_times", "novels", "start_date", "end_date", "create_by", "single_img", "content"]
    table = "t_ads_media"

    db.zx_test.dfsave2mysql(df, table, key, tag)


def adcreative():
    sql="""select signature,title,description,
sum(click_count) click_count,
sum(view_count) view_count,
group_concat(distinct book),
max(dt),min(dt),
min(preview_url)

from dw_image_cost_day where dt='2021-05-10' and signature!=''   GROUP BY  signature,title,description
				"""


if __name__ == '__main__':
    # title()
    # description()
    image()