| 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 1501435553
 
- import logging
 
- from Apietl.conn.TidbConn import TidbConn
 
- from Apietl.utlis.Utils import Utils
 
- class 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_dw
 
- select 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_date
 
- from 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_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 = LiveUnitDailyReport().handler()
 
-     if status == 0:
 
-         print("作业执行成功")
 
-         exit(0)
 
-     else:
 
-         print("作业执行失败")
 
-         exit(-1)
 
 
  |