# Author renyupeng # coding=utf-8 # @Time : 2022/2/17 4:13 下午 # @Site : # @File : KuaishouUnitAudienceAgeReportDaily.py # @Software: PyCharm # @contact: renyupeng@c-top.com.cn # @Tel 1501435553 import logging from Apietl.conn.TidbConn import TidbConn from Apietl.utlis.Utils import Utils class KuaishouUnitAudienceAgeReportDaily: def __init__(self): self.conn = TidbConn.get_connection() self.parm = Utils.get_args() self.start_time = self.parm[0] def ageReport(self): try: sql = """ replace into application.kuaishou_unit_audience_age_report_daily_d select age.account_id, campaign_id, age.unit_id, event_register_cost, age_segment, photo_click, key_action_cost, event_next_day_stay, photo_click_ratio, photo_click_cost, charge, event_pay, action_cost, conversion_cost, bclick, key_action_ratio, play_3s_ratio, play_5s_ratio, activation, event_app_invoked_ratio, `show`, event_new_user_pay_cost, aclick, deep_conversion_num, action_ratio, conversion_ratio, event_pay_purchase_amount_first_day, event_pay_first_day, conversion_num_cost, event_new_user_pay_ratio, event_app_invoked, event_next_day_stay_ratio, event_register, event_new_user_pay, event_next_day_stay_cost, deep_conversion_ratio, deep_conversion_cost, `like`, event_app_invoked_cost, key_action, ad_photo_played_10s, form_cost, event_pay_purchase_amount, click_1k_cost, impression_1k_cost, conversion_num, stat_date, ad_photo_played_2s_ratio, follow, signature, project_id, project_name from (select account_id, campaign_id, unit_id, event_register_cost, age_segment, photo_click, key_action_cost, event_next_day_stay, photo_click_ratio, photo_click_cost, charge, event_pay, action_cost, conversion_cost, bclick, key_action_ratio, play_3s_ratio, play_5s_ratio, activation, event_app_invoked_ratio, `show`, event_new_user_pay_cost, aclick, deep_conversion_num, action_ratio, conversion_ratio, event_pay_purchase_amount_first_day, event_pay_first_day, conversion_num_cost, event_new_user_pay_ratio, event_app_invoked, event_next_day_stay_ratio, event_register, event_new_user_pay, event_next_day_stay_cost, deep_conversion_ratio, deep_conversion_cost, `like`, event_app_invoked_cost, key_action, ad_photo_played_10s, form_cost, event_pay_purchase_amount, click_1k_cost, impression_1k_cost, conversion_num, stat_date, ad_photo_played_2s_ratio, follow from media_api.kuaishou_unit_audience_age_report_daily where stat_date = {stat_date}) age inner join (select account_id, unit_id, photo_id from `jeecg-boot`.ctop_ai_kuaishou_creative_level_operation_record group by account_id, unit_id, photo_id) operation on age.account_id = operation.account_id and age.unit_id = operation.unit_id inner join (select account_id, signature, photo_id from media_api.kuaishou_video_list group by account_id, signature, photo_id) video on operation.account_id = video.account_id and operation.photo_id = video.photo_id left join ( select account_id, project_id from `jeecg-boot`.ctop_user_allocation ) acc_info on age.account_id = acc_info.account_id left join ( select id, project_name from `jeecg-boot`.ctop_project ) pro on acc_info.project_id = pro.id """.format(stat_date=self.start_time) TidbConn.upsert(conn=self.conn, sql_buff=sql) self.conn.commit() TidbConn.conn_close(conn=self.conn) return 0 except Exception as e: logging.error(e) return -1 if __name__ == '__main__': KuaishouUnitAudienceAgeReportDaily().ageReport()