# Author renyupeng # coding=utf-8 # @Time : 2022/2/17 4:13 下午 # @Site : # @File : KuaishouUnitAudienceProvinceReportDaily.py # @Software: PyCharm # @contact: renyupeng@c-top.com.cn # @Tel 1501435553 import logging from math import ceil from Apietl.conn.TidbConn import TidbConn from Apietl.utlis.Utils import Utils class KuaishouUnitAudienceProvinceReportDaily: def __init__(self): self.conn = TidbConn.get_connection() self.parm = Utils.get_args() self.start_time = self.parm[0] def provinceReport(self): try: sql = """select count(1) from media_api.kuaishou_unit_audience_province_report_daily where stat_date = {start_time}""".format( start_time=self.start_time) totalNum = TidbConn.quary(self.conn, sql)[0][0] pageCount = ceil(totalNum / 40000) for i in range(pageCount): sql = """ replace into application.kuaishou_unit_audience_province_report_daily_d select province.account_id, campaign_id, province.unit_id, event_register_cost, province, 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, province, 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_province_report_daily where stat_date = {stat_date}) province 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 province.account_id = operation.account_id and province.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 province.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 order by unit_id limit {num},20000 """.format(stat_date=self.start_time, num=i * 40000) 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__': KuaishouUnitAudienceProvinceReportDaily().provinceReport()