LiveUnitDailyReport.py 5.3 KB

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