| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319 | 
							- # Author renyupeng
 
- # coding=utf-8
 
- # @Time    : 2023/3/22 9:56 上午
 
- # @Site    : 
 
- # @File    : LiveAccountReport.py
 
- # @Software: PyCharm
 
- # @contact: renyupeng@c-top.com.cn
 
- # @Tel 1501435553
 
- import logging
 
- from Apietl.conn.TidbConn import TidbConn
 
- class LiveAccountReport:
 
-     def __init__(self):
 
-         self.conn = TidbConn.get_connection()
 
-     def daily_account(self, account_id, start_date, end_date):
 
-         sql = """
 
-         replace into ruixuan.kuaishou_live_account_report_daily_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
 
-         from kuaishou_live.kuaishou_live_account_report_daily 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 >= {start_date} and report.report_date<={end_date}
 
-           and report.account_id = {account_id};
 
-                         """.format(start_date=start_date, end_date=end_date, account_id=account_id)
 
-         print(sql)
 
-         try:
 
-             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
 
-     def account_hourly(self, account_id, start_date, end_date):
 
-         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 >= {start_date} and report.report_date<={end_date}
 
-           and report.account_id = {account_id};
 
-                         """.format(start_date=start_date, end_date=end_date, account_id=account_id)
 
-         print(sql)
 
-         try:
 
-             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
 
-     def handler(self, date_type, account_id, start_date, end_date):
 
-         if date_type == "daily":
 
-             self.daily_account(account_id, start_date, end_date)
 
-         else:
 
-             self.account_hourly(account_id, start_date, end_date)
 
- if __name__ == '__main__':
 
-     LiveAccountReport().handler('hourly', 14670026, 20230321, 20230321)
 
 
  |