LiveAccountReport.py 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2023/3/22 9:56 上午
  4. # @Site :
  5. # @File : LiveAccountReport.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. class LiveAccountReport:
  12. def __init__(self):
  13. self.conn = TidbConn.get_connection()
  14. def daily_account(self, account_id, start_date, end_date):
  15. sql = """
  16. replace into ruixuan.kuaishou_live_account_report_daily_dw
  17. select report.account_id,
  18. account_name,
  19. project_id,
  20. acc_info.project_name,
  21. ks_id,
  22. ks_name,
  23. account_type,
  24. user_id,
  25. user_name,
  26. leader as lead_name,
  27. pro_user_id,
  28. pro_user_name,
  29. sale_id,
  30. sale_name,
  31. cost_total / 100,
  32. ad_show,
  33. ad_show1k_cost / 100,
  34. impression,
  35. photo_click,
  36. photo_click_ratio,
  37. click,
  38. actionbar_click,
  39. actionbar_click_cost / 100,
  40. esp_click_ratio,
  41. action_ratio,
  42. ad_item_click_count,
  43. esp_live_played_seconds,
  44. played_three_seconds,
  45. play3s_ratio,
  46. played_five_seconds,
  47. play5s_ratio,
  48. played_end,
  49. play_end_ratio,
  50. share,
  51. comment,
  52. likes,
  53. report,
  54. block,
  55. item_negative,
  56. live_share,
  57. live_comment,
  58. live_reward,
  59. effective_play_count,
  60. effective_play_ratio,
  61. ad_live_played1m_num,
  62. conversion_num,
  63. conversion_cost_esp / 100,
  64. gmv / 100,
  65. t0_gmv / 100,
  66. t1_gmv / 100,
  67. t7_gmv / 100,
  68. t15_gmv / 100,
  69. t30_gmv,
  70. roi,
  71. t0_roi,
  72. t1_roi,
  73. t7_roi,
  74. t15_roi,
  75. t30_roi,
  76. paied_order,
  77. order_ratio,
  78. t0_order_cnt,
  79. t0_order_cnt_cost / 100,
  80. t0_order_cnt_ratio,
  81. t1_order_cnt,
  82. t7_order_cnt,
  83. t15_order_cnt,
  84. t30_order_cnt,
  85. merchant_reco_fans,
  86. t1_retention,
  87. t7_retention,
  88. t15_retention,
  89. t30_retention,
  90. t1_retention_ratio,
  91. t7_retention_ratio,
  92. t15_retention_ratio,
  93. t30_retention_ratio,
  94. reservation_success,
  95. reservation_cost / 100,
  96. standard_live_played_started,
  97. live_audience_cost / 100,
  98. live_event_goods_view,
  99. goods_click_ratio,
  100. direct_attr_plat_new_buyer_cnt,
  101. t30_attr_plat_total_buyer_cnt,
  102. direct_attr_seller_new_buyer_cnt,
  103. t30_attr_seller_total_buyer_cnt,
  104. fans_t0_gmv / 100,
  105. fans_t1_gmv / 100,
  106. fans_t7_gmv / 100,
  107. fans_t15_gmv / 100,
  108. fans_t30_gmv / 100,
  109. fans_t0_roi,
  110. fans_t1_roi,
  111. fans_t7_roi,
  112. fans_t15_roi,
  113. fans_t30_roi,
  114. order_cost,
  115. reco_fans_cost,
  116. conversion_live_event_goods_view_cost,
  117. report_date
  118. from kuaishou_live.kuaishou_live_account_report_daily report
  119. left join
  120. (select acc.account_id,
  121. account_name,
  122. project_id,
  123. pro.project_name,
  124. leader,
  125. ks_id,
  126. ks_name,
  127. account_type,
  128. user.user_id as user_id,
  129. user.nick_name as user_name,
  130. dep.leader as leader_name,
  131. pro.operator_id as pro_user_id,
  132. pro.operator_name as pro_user_name,
  133. pro.sale_id as sale_id,
  134. pro.sale_name as sale_name
  135. from ruixuan.kuaishou_account acc
  136. left join
  137. ruixuan.kuaishou_project pro
  138. on acc.project_id = pro.id
  139. left join ruixuan.sys_user user
  140. on acc.operator_id=user.user_id
  141. left join ruixuan.sys_dept dep
  142. on user.dept_id=dep.dept_id
  143. ) acc_info
  144. on report.account_id = acc_info.account_id
  145. where report.report_date >= {start_date} and report.report_date<={end_date}
  146. and report.account_id = {account_id};
  147. """.format(start_date=start_date, end_date=end_date, account_id=account_id)
  148. print(sql)
  149. try:
  150. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  151. self.conn.commit()
  152. TidbConn.conn_close(conn=self.conn)
  153. return 0
  154. except Exception as e:
  155. logging.error(e)
  156. return -1
  157. def account_hourly(self, account_id, start_date, end_date):
  158. sql = """
  159. replace into ruixuan.kuaishou_live_account_report_hourly_dw
  160. select report.account_id,
  161. account_name,
  162. project_id,
  163. acc_info.project_name,
  164. ks_id,
  165. ks_name,
  166. account_type,
  167. user_id,
  168. user_name,
  169. leader as lead_name,
  170. pro_user_id,
  171. pro_user_name,
  172. sale_id,
  173. sale_name,
  174. cost_total / 100,
  175. ad_show,
  176. ad_show1k_cost / 100,
  177. impression,
  178. photo_click,
  179. photo_click_ratio,
  180. click,
  181. actionbar_click,
  182. actionbar_click_cost / 100,
  183. esp_click_ratio,
  184. action_ratio,
  185. ad_item_click_count,
  186. esp_live_played_seconds,
  187. played_three_seconds,
  188. play3s_ratio,
  189. played_five_seconds,
  190. play5s_ratio,
  191. played_end,
  192. play_end_ratio,
  193. share,
  194. comment,
  195. likes,
  196. report,
  197. block,
  198. item_negative,
  199. live_share,
  200. live_comment,
  201. live_reward,
  202. effective_play_count,
  203. effective_play_ratio,
  204. ad_live_played1m_num,
  205. conversion_num,
  206. conversion_cost_esp / 100,
  207. gmv / 100,
  208. t0_gmv / 100,
  209. t1_gmv / 100,
  210. t7_gmv / 100,
  211. t15_gmv / 100,
  212. t30_gmv,
  213. roi,
  214. t0_roi,
  215. t1_roi,
  216. t7_roi,
  217. t15_roi,
  218. t30_roi,
  219. paied_order,
  220. order_ratio,
  221. t0_order_cnt,
  222. t0_order_cnt_cost / 100,
  223. t0_order_cnt_ratio,
  224. t1_order_cnt,
  225. t7_order_cnt,
  226. t15_order_cnt,
  227. t30_order_cnt,
  228. merchant_reco_fans,
  229. t1_retention,
  230. t7_retention,
  231. t15_retention,
  232. t30_retention,
  233. t1_retention_ratio,
  234. t7_retention_ratio,
  235. t15_retention_ratio,
  236. t30_retention_ratio,
  237. reservation_success,
  238. reservation_cost / 100,
  239. standard_live_played_started,
  240. live_audience_cost / 100,
  241. live_event_goods_view,
  242. goods_click_ratio,
  243. direct_attr_plat_new_buyer_cnt,
  244. t30_attr_plat_total_buyer_cnt,
  245. direct_attr_seller_new_buyer_cnt,
  246. t30_attr_seller_total_buyer_cnt,
  247. fans_t0_gmv / 100,
  248. fans_t1_gmv / 100,
  249. fans_t7_gmv / 100,
  250. fans_t15_gmv / 100,
  251. fans_t30_gmv / 100,
  252. fans_t0_roi,
  253. fans_t1_roi,
  254. fans_t7_roi,
  255. fans_t15_roi,
  256. fans_t30_roi,
  257. order_cost,
  258. reco_fans_cost,
  259. conversion_live_event_goods_view_cost,
  260. report_date,
  261. `hour`
  262. from kuaishou_live.kuaishou_live_account_report_hourly report
  263. left join
  264. (select acc.account_id,
  265. account_name,
  266. project_id,
  267. pro.project_name,
  268. leader,
  269. ks_id,
  270. ks_name,
  271. account_type,
  272. user.user_id as user_id,
  273. user.nick_name as user_name,
  274. dep.leader as leader_name,
  275. pro.operator_id as pro_user_id,
  276. pro.operator_name as pro_user_name,
  277. pro.sale_id as sale_id,
  278. pro.sale_name as sale_name
  279. from ruixuan.kuaishou_account acc
  280. left join
  281. ruixuan.kuaishou_project pro
  282. on acc.project_id = pro.id
  283. left join ruixuan.sys_user user
  284. on acc.operator_id=user.user_id
  285. left join ruixuan.sys_dept dep
  286. on user.dept_id=dep.dept_id
  287. ) acc_info
  288. on report.account_id = acc_info.account_id
  289. where report.report_date >= {start_date} and report.report_date<={end_date}
  290. and report.account_id = {account_id};
  291. """.format(start_date=start_date, end_date=end_date, account_id=account_id)
  292. print(sql)
  293. try:
  294. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  295. self.conn.commit()
  296. TidbConn.conn_close(conn=self.conn)
  297. return 0
  298. except Exception as e:
  299. logging.error(e)
  300. return -1
  301. def handler(self, date_type, account_id, start_date, end_date):
  302. if date_type == "daily":
  303. self.daily_account(account_id, start_date, end_date)
  304. else:
  305. self.account_hourly(account_id, start_date, end_date)
  306. if __name__ == '__main__':
  307. LiveAccountReport().handler('hourly', 14670026, 20230321, 20230321)