# Author renyupeng # coding=utf-8 # @Time : 2022/7/27 2:49 下午 # @Site : # @File : LiveAccountHourlyReport.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 LiveAccountHourlyReport: 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_account_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_account_report_hourly_dw select report.account_id, account_name, project_id, acc_info.project_name, ks_id, ks_name, account_type, user_id, user_name, leader as lead_name, pro_user_id, pro_user_name, sale_id, sale_name, 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, order_cost, reco_fans_cost, conversion_live_event_goods_view_cost, report_date, `hour` from kuaishou_live.kuaishou_live_account_report_hourly report left join (select acc.account_id, account_name, project_id, pro.project_name, leader, ks_id, ks_name, account_type, user.user_id as user_id, user.nick_name as user_name, dep.leader as leader_name, pro.operator_id as pro_user_id, pro.operator_name as pro_user_name, pro.sale_id as sale_id, pro.sale_name as sale_name from ruixuan.kuaishou_account acc left join ruixuan.kuaishou_project pro on acc.project_id = pro.id left join ruixuan.sys_user user on acc.operator_id=user.user_id left join ruixuan.sys_dept dep on user.dept_id=dep.dept_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 = LiveAccountHourlyReport().handler() if status == 0: print("作业执行成功") exit(0) else: print("作业执行失败") exit(-1)