# Author renyupeng # coding=utf-8 # @Time : 2022/7/27 2:49 下午 # @Site : # @File : LiveCampaignHourlyReport.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 LiveCampaignHourlyReport: def __init__(self): self.conn = TidbConn.get_connection() self.parm = Utils.get_live_args() self.stat_date = self.parm def handler(self): try: sql = """select account_id from kuaishou_live.kuaishou_live_campaign_report_hourly where report_date={stat_date} group by account_id""".format(stat_date=self.stat_date) account_list = TidbConn.quary(self.conn, sql) for account_id in account_list: sql = """ replace into ruixuan.kuaishou_live_campaign_report_hourly_dw select report.account_id, account_name, campaign_id, campaign_name, campaign_type_str, project_id, project_name, ks_id, ks_name, sale_leader_id, sale_leader, operator_leader_id, operator_leader, designer_leader_id, designer_leader, enterprise_name, collaborator_id, collaborator, cost_total/100, ad_show, ad_show1k_cost/100, impression, photo_click, photo_click_ratio, click, actionbar_click, actionbar_click_cost/100, esp_click_ratio, action_ratio, ad_item_click_count, esp_live_played_seconds, played_three_seconds, play3s_ratio, played_five_seconds, play5s_ratio, played_end, play_end_ratio, share, comment, likes, report, block, item_negative, live_share, live_comment, live_reward, effective_play_count, effective_play_ratio, ad_live_played1m_num, conversion_num, conversion_cost_esp/100, gmv/100, t0_gmv/100, t1_gmv/100, t7_gmv/100, t15_gmv/100, t30_gmv, roi, t0_roi, t1_roi, t7_roi, t15_roi, t30_roi, paied_order, order_ratio, t0_order_cnt, t0_order_cnt_cost/100, t0_order_cnt_ratio, t1_order_cnt, t7_order_cnt, t15_order_cnt, t30_order_cnt, merchant_reco_fans, t1_retention, t7_retention, t15_retention, t30_retention, t1_retention_ratio, t7_retention_ratio, t15_retention_ratio, t30_retention_ratio, reservation_success, reservation_cost/100, standard_live_played_started, live_audience_cost/100, live_event_goods_view, goods_click_ratio, direct_attr_plat_new_buyer_cnt, t30_attr_plat_total_buyer_cnt, direct_attr_seller_new_buyer_cnt, t30_attr_seller_total_buyer_cnt, fans_t0_gmv/100, fans_t1_gmv/100, fans_t7_gmv/100, fans_t15_gmv/100, fans_t30_gmv/100, fans_t0_roi, fans_t1_roi, fans_t7_roi, fans_t15_roi, fans_t30_roi, report_date, `hour` from kuaishou_live.kuaishou_live_campaign_report_hourly report left join (select acc.account_id, account_name, project_id, project_name, ks_id, ks_name, sale_leader_id, sale.user_name as sale_leader, operator_leader_id, user.user_name as operator_leader, designer_leader_id, designer.user_name as designer_leader, enterprise_name, collaborator_id, collaborator from ruixuan.kuaishou_live_user_account acc left join ruixuan.kuaishou_live_project pro on acc.project_id = pro.id left join ruixuan.sys_user sale on acc.sale_leader_id = sale.user_id left join ruixuan.sys_user user on acc.operator_leader_id = user.user_id left join ruixuan.sys_user designer on acc.designer_leader_id = designer.user_id left join( select account_id, group_concat(collaborator_id) as collaborator_id, group_concat(collaborator) as collaborator from ruixuan.kuaishou_live_user_account_part group by account_id ) collaborator on acc.account_id = collaborator.account_id) acc_info on report.account_id = acc_info.account_id where report.report_date={stat_date} and report.account_id={account_id}; """.format(stat_date=self.stat_date, account_id=account_id[0]) print(sql) 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__': status = LiveCampaignHourlyReport().handler() if status == 0: print("作业执行成功") exit(0) else: print("作业执行失败") exit(-1)