LiveAccountHourlyReport.py 5.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2022/7/27 2:49 下午
  4. # @Site :
  5. # @File : LiveAccountHourlyReport.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 LiveAccountHourlyReport:
  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_hourly 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_hourly_dw
  25. select report.account_id,
  26. account_name,
  27. project_id,
  28. project_name,
  29. ks_id,
  30. ks_name,
  31. sale_leader_id,
  32. sale_leader,
  33. operator_leader_id,
  34. operator_leader,
  35. designer_leader_id,
  36. designer_leader,
  37. enterprise_name,
  38. collaborator_id,
  39. collaborator,
  40. cost_total/100,
  41. ad_show,
  42. ad_show1k_cost/100,
  43. impression,
  44. photo_click,
  45. photo_click_ratio,
  46. click,
  47. actionbar_click,
  48. actionbar_click_cost/100,
  49. esp_click_ratio,
  50. action_ratio,
  51. ad_item_click_count,
  52. esp_live_played_seconds,
  53. played_three_seconds,
  54. play3s_ratio,
  55. played_five_seconds,
  56. play5s_ratio,
  57. played_end,
  58. play_end_ratio,
  59. share,
  60. comment,
  61. likes,
  62. report,
  63. block,
  64. item_negative,
  65. live_share,
  66. live_comment,
  67. live_reward,
  68. effective_play_count,
  69. effective_play_ratio,
  70. ad_live_played1m_num,
  71. conversion_num,
  72. conversion_cost_esp/100,
  73. gmv/100,
  74. t0_gmv/100,
  75. t1_gmv/100,
  76. t7_gmv/100,
  77. t15_gmv/100,
  78. t30_gmv,
  79. roi,
  80. t0_roi,
  81. t1_roi,
  82. t7_roi,
  83. t15_roi,
  84. t30_roi,
  85. paied_order,
  86. order_ratio,
  87. t0_order_cnt,
  88. t0_order_cnt_cost/100,
  89. t0_order_cnt_ratio,
  90. t1_order_cnt,
  91. t7_order_cnt,
  92. t15_order_cnt,
  93. t30_order_cnt,
  94. merchant_reco_fans,
  95. t1_retention,
  96. t7_retention,
  97. t15_retention,
  98. t30_retention,
  99. t1_retention_ratio,
  100. t7_retention_ratio,
  101. t15_retention_ratio,
  102. t30_retention_ratio,
  103. reservation_success,
  104. reservation_cost/100,
  105. standard_live_played_started,
  106. live_audience_cost/100,
  107. live_event_goods_view,
  108. goods_click_ratio,
  109. direct_attr_plat_new_buyer_cnt,
  110. t30_attr_plat_total_buyer_cnt,
  111. direct_attr_seller_new_buyer_cnt,
  112. t30_attr_seller_total_buyer_cnt,
  113. fans_t0_gmv/100,
  114. fans_t1_gmv/100,
  115. fans_t7_gmv/100,
  116. fans_t15_gmv/100,
  117. fans_t30_gmv/100,
  118. fans_t0_roi,
  119. fans_t1_roi,
  120. fans_t7_roi,
  121. fans_t15_roi,
  122. fans_t30_roi,
  123. report_date,
  124. `hour`
  125. from kuaishou_live.kuaishou_live_account_report_hourly report
  126. left join
  127. (select acc.account_id,
  128. account_name,
  129. project_id,
  130. project_name,
  131. ks_id,
  132. ks_name,
  133. sale_leader_id,
  134. sale.user_name as sale_leader,
  135. operator_leader_id,
  136. user.user_name as operator_leader,
  137. designer_leader_id,
  138. designer.user_name as designer_leader,
  139. enterprise_name,
  140. collaborator_id,
  141. collaborator
  142. from ruixuan.kuaishou_live_user_account acc
  143. left join
  144. ruixuan.kuaishou_live_project pro
  145. on acc.project_id = pro.id
  146. left join ruixuan.sys_user sale
  147. on acc.sale_leader_id = sale.user_id
  148. left join ruixuan.sys_user user
  149. on acc.operator_leader_id = user.user_id
  150. left join ruixuan.sys_user designer
  151. on acc.designer_leader_id = designer.user_id
  152. left join(
  153. select account_id,
  154. group_concat(collaborator_id) as collaborator_id,
  155. group_concat(collaborator) as collaborator
  156. from ruixuan.kuaishou_live_user_account_part
  157. group by account_id
  158. ) collaborator
  159. on acc.account_id = collaborator.account_id) acc_info
  160. on report.account_id = acc_info.account_id
  161. where report.report_date={stat_date} and report.account_id={account_id};
  162. """.format(stat_date=self.stat_date, account_id=account_id[0])
  163. print(sql)
  164. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  165. self.conn.commit()
  166. TidbConn.conn_close(conn=self.conn)
  167. return 0
  168. except Exception as e:
  169. logging.error(e)
  170. return -1
  171. if __name__ == '__main__':
  172. status = LiveAccountHourlyReport().handler()
  173. if status == 0:
  174. print("作业执行成功")
  175. exit(0)
  176. else:
  177. print("作业执行失败")
  178. exit(-1)