LiveAccountDailyReport.py 4.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2022/7/27 2:49 下午
  4. # @Site :
  5. # @File : LiveAccountDailyReport.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import logging
  10. from Apietl.conn.TidbConn import TidbConn
  11. from Apietl.utlis.Utils import Utils
  12. class LiveAccountDailyReport:
  13. def __init__(self):
  14. self.conn = TidbConn.get_connection()
  15. self.parm = Utils.get_live_args()
  16. self.stat_date = self.parm
  17. def handler(self):
  18. try:
  19. sql = """select account_id from kuaishou_live.kuaishou_live_account_report_daily where report_date={stat_date}
  20. group by account_id""".format(stat_date=self.stat_date)
  21. account_list = TidbConn.quary(self.conn, sql)
  22. for account_id in account_list:
  23. sql = """
  24. replace into ruixuan.kuaishou_live_account_report_daily_dw
  25. select report.account_id,
  26. account_name,
  27. project_id,
  28. acc_info.project_name,
  29. ks_id,
  30. ks_name,
  31. account_type,
  32. user_id,
  33. user_name,
  34. leader as lead_name,
  35. pro_user_id,
  36. pro_user_name,
  37. sale_id,
  38. sale_name,
  39. cost_total / 100,
  40. ad_show,
  41. ad_show1k_cost / 100,
  42. impression,
  43. photo_click,
  44. photo_click_ratio,
  45. click,
  46. actionbar_click,
  47. actionbar_click_cost / 100,
  48. esp_click_ratio,
  49. action_ratio,
  50. ad_item_click_count,
  51. esp_live_played_seconds,
  52. played_three_seconds,
  53. play3s_ratio,
  54. played_five_seconds,
  55. play5s_ratio,
  56. played_end,
  57. play_end_ratio,
  58. share,
  59. comment,
  60. likes,
  61. report,
  62. block,
  63. item_negative,
  64. live_share,
  65. live_comment,
  66. live_reward,
  67. effective_play_count,
  68. effective_play_ratio,
  69. ad_live_played1m_num,
  70. conversion_num,
  71. conversion_cost_esp / 100,
  72. gmv / 100,
  73. t0_gmv / 100,
  74. t1_gmv / 100,
  75. t7_gmv / 100,
  76. t15_gmv / 100,
  77. t30_gmv,
  78. roi,
  79. t0_roi,
  80. t1_roi,
  81. t7_roi,
  82. t15_roi,
  83. t30_roi,
  84. paied_order,
  85. order_ratio,
  86. t0_order_cnt,
  87. t0_order_cnt_cost / 100,
  88. t0_order_cnt_ratio,
  89. t1_order_cnt,
  90. t7_order_cnt,
  91. t15_order_cnt,
  92. t30_order_cnt,
  93. merchant_reco_fans,
  94. t1_retention,
  95. t7_retention,
  96. t15_retention,
  97. t30_retention,
  98. t1_retention_ratio,
  99. t7_retention_ratio,
  100. t15_retention_ratio,
  101. t30_retention_ratio,
  102. reservation_success,
  103. reservation_cost / 100,
  104. standard_live_played_started,
  105. live_audience_cost / 100,
  106. live_event_goods_view,
  107. goods_click_ratio,
  108. direct_attr_plat_new_buyer_cnt,
  109. t30_attr_plat_total_buyer_cnt,
  110. direct_attr_seller_new_buyer_cnt,
  111. t30_attr_seller_total_buyer_cnt,
  112. fans_t0_gmv / 100,
  113. fans_t1_gmv / 100,
  114. fans_t7_gmv / 100,
  115. fans_t15_gmv / 100,
  116. fans_t30_gmv / 100,
  117. fans_t0_roi,
  118. fans_t1_roi,
  119. fans_t7_roi,
  120. fans_t15_roi,
  121. fans_t30_roi,
  122. order_cost,
  123. reco_fans_cost,
  124. conversion_live_event_goods_view_cost,
  125. report_date
  126. from kuaishou_live.kuaishou_live_account_report_daily report
  127. left join
  128. (select acc.account_id,
  129. account_name,
  130. project_id,
  131. pro.project_name,
  132. leader,
  133. ks_id,
  134. ks_name,
  135. account_type,
  136. user.user_id as user_id,
  137. user.nick_name as user_name,
  138. dep.leader as leader_name,
  139. pro.operator_id as pro_user_id,
  140. pro.operator_name as pro_user_name,
  141. pro.sale_id as sale_id,
  142. pro.sale_name as sale_name
  143. from ruixuan.kuaishou_account acc
  144. left join
  145. ruixuan.kuaishou_project pro
  146. on acc.project_id = pro.id
  147. left join ruixuan.sys_user user
  148. on acc.operator_id=user.user_id
  149. left join ruixuan.sys_dept dep
  150. on user.dept_id=dep.dept_id
  151. ) acc_info
  152. on report.account_id = acc_info.account_id
  153. where report.report_date = {stat_date}
  154. and report.account_id = {account_id};
  155. """.format(stat_date=self.stat_date, account_id=account_id[0])
  156. print(sql)
  157. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  158. self.conn.commit()
  159. TidbConn.conn_close(conn=self.conn)
  160. return 0
  161. except Exception as e:
  162. logging.error(e)
  163. return -1
  164. if __name__ == '__main__':
  165. status = LiveAccountDailyReport().handler()
  166. if status == 0:
  167. print("作业执行成功")
  168. exit(0)
  169. else:
  170. print("作业执行失败")
  171. exit(-1)