123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178 |
- # 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)
|