| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191 | # Author renyupeng# coding=utf-8# @Time    : 2022/7/27 2:49 下午# @Site    :# @File    : LiveUnitDailyReport.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cn# @Tel 1501435553import loggingfrom Apietl.conn.TidbConn import TidbConnfrom Apietl.utlis.Utils import Utilsclass LiveUnitDailyReport:    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_unit_report_daily 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_unit_report_daily_dwselect report.account_id,       account_name,campaign_id,campaign_name,unit_id,unit_name,ocpc_action_type_str,speed_type_str,creative_build_type_str,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_datefrom kuaishou_live.kuaishou_live_unit_report_daily 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_idwhere 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 -1if __name__ == '__main__':    status = LiveUnitDailyReport().handler()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |