| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159 | # Author renyupeng# coding=utf-8# @Time    : 2022/2/17 4:13 下午# @Site    : # @File    : KuaishouUnitAudienceGenderReportDaily.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cn# @Tel 1501435553import loggingfrom Apietl.conn.TidbConn import TidbConnfrom Apietl.utlis.Utils import Utilsclass KuaishouUnitAudienceGenderReportDaily:    def __init__(self):        self.conn = TidbConn.get_connection()        self.parm = Utils.get_args()        self.start_time = self.parm[0]    def genderReport(self):        try:            sql = """            replace into application.kuaishou_unit_audience_gender_report_daily_dselect gender.account_id,       campaign_id,       gender.unit_id,       event_register_cost,       gender,       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_namefrom (select account_id,             campaign_id,             unit_id,             event_register_cost,             gender,             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_gender_report_daily      where stat_date = {stat_date}) gender         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 gender.account_id = operation.account_id and gender.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 gender.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 -1if __name__ == '__main__':    KuaishouUnitAudienceGenderReportDaily().genderReport()
 |