AppBytedanceFileVideoGetAll.py 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/11/12 10:20 上午
  4. # @Site :
  5. # @File : AppBytedanceFileVideoGetAll.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 AppBytedanceFileVideoGetAll:
  12. def __init__(self):
  13. self.conn = TidbConn.get_connection()
  14. def execSql(self):
  15. try:
  16. sql = """select advertiser_id from media_api.bytedance_file_video_get
  17. where advertiser_id in(1710388576343047)
  18. group by advertiser_id"""
  19. account_list = TidbConn.quary(self.conn, sql)
  20. for account_id in account_list:
  21. try:
  22. sql = """
  23. replace into application.app_bytedance_file_video_get
  24. select advertiser_id,
  25. id,
  26. size,
  27. width,
  28. height,
  29. url,
  30. format,
  31. case when v.signature is null then '' else v.signature end as signature,
  32. poster_url,
  33. bit_rate,
  34. duration,
  35. material_id,
  36. source,
  37. create_time,
  38. filename,
  39. labels,
  40. video_url,
  41. case when tag_category_id is null then '' else tag_category_id end as tag_category_id,
  42. tag_category_name,
  43. case when parent_id is null then '' else parent_id end as parent_id,
  44. parent_name,
  45. case when tag_code is null then '' else tag_code end as tag_code,
  46. tag_name,
  47. clip_id,
  48. clip_name,
  49. shot_id,
  50. shot_name,
  51. plan_id,
  52. plan_name,
  53. leader_id,
  54. leader_name,
  55. material_innovate,
  56. hot.stat_datetime,
  57. effect.stat_datetime
  58. from (
  59. select advertiser_id,
  60. id,
  61. size,
  62. width,
  63. height,
  64. url,
  65. format,
  66. signature,
  67. poster_url,
  68. bit_rate,
  69. duration,
  70. a.material_id,
  71. source,
  72. create_time,
  73. case when t.material_name is null then a.filename else t.material_name end as filename,
  74. labels,
  75. video_url,
  76. clip_id,
  77. clip_name,
  78. shot_id,
  79. shot_name,
  80. plan_id,
  81. plan_name,
  82. leader_id,
  83. leader_name,
  84. material_innovate
  85. from (select advertiser_id,
  86. id,
  87. size,
  88. width,
  89. height,
  90. url,
  91. format,
  92. signature,
  93. poster_url,
  94. bit_rate,
  95. duration,
  96. material_id,
  97. source,
  98. create_time,
  99. filename,
  100. labels,
  101. video_url
  102. from media_api.bytedance_file_video_get where advertiser_id={account_id}
  103. ) a
  104. left join
  105. (select material_id,
  106. clip_id,
  107. clip_name,
  108. shot_id,
  109. shot_name,
  110. plan_id,
  111. plan_name,
  112. leader_id,
  113. leader_name,
  114. material_name,
  115. material_innovate
  116. from (select cma.material_id,
  117. clip_id,
  118. clip.realname as clip_name,
  119. shot_id,
  120. shot.realname as shot_name,
  121. plan_id,
  122. plan.realname as plan_name,
  123. cma.leader_id,
  124. leader.realname as leader_name,
  125. material_name,
  126. material_innovate
  127. from `jeecg-boot`.ctop_material_ascription cma
  128. left join
  129. `jeecg-boot`.ctop_material_info b
  130. on cma.material_id = b.id
  131. left join `jeecg-boot`.sys_user clip on clip.id = cma.clip_id
  132. left join `jeecg-boot`.sys_user shot on shot.id = cma.shot_id
  133. left join `jeecg-boot`.sys_user plan on plan.id = cma.plan_id
  134. left join `jeecg-boot`.sys_user leader on leader.id = cma.leader_id
  135. ) v) t
  136. on a.signature = t.material_id) v
  137. left join
  138. (select code,
  139. tag_category_id,
  140. tag_category_name,
  141. parent_id,
  142. parent_name,
  143. group_concat(tag_code order by tag_code separator '-') tag_code,
  144. group_concat(tag_name order by tag_code separator '-') tag_name
  145. from (select a.code, b.tag_category_id, b.tag_category_name, b.parent_id, b.parent_name, b.tag_code, b.tag_name
  146. from `jeecg-boot`.ctop_material_tag_info a
  147. left join
  148. (select if(tag_category_id is null,'',tag_category_id) as tag_category_id,
  149. tag_category_name,
  150. case
  151. when parent_id is not null then parent_id
  152. when tag_category_id = 2 then ''
  153. else tag_code end parent_id,
  154. case
  155. when parent_id = 60155 then '真人'
  156. when parent_id = 60156 then '制作'
  157. when parent_id = 60160 then '外拍场景'
  158. when parent_id = 60161 then '公司内景'
  159. when tag_category_id = 2 then ''
  160. else '其他' end parent_name,
  161. tag_code,
  162. tag_name
  163. from `jeecg-boot`.ctop_tag_info) b
  164. on a.tag_code = b.tag_code) c
  165. group by code, tag_category_id, tag_category_name, parent_id, parent_name) m
  166. on v.signature = m.code
  167. left join (
  168. select stat_datetime, signature, sum
  169. from (
  170. select stat_datetime, signature, sum, row_number() over (partition by signature order by sum) as rn
  171. from (
  172. select stat_datetime,
  173. signature,
  174. sum(cost) over (partition by signature order by stat_datetime) as sum
  175. from (
  176. select stat_datetime, video.signature, cost
  177. from (select material_id, signature, create_time
  178. from (select material_id, min(create_time) as create_time, signature
  179. from media_api.bytedance_file_video_get
  180. where material_id is not null and advertiser_id={account_id}
  181. group by material_id, signature) s) video
  182. inner join
  183. (
  184. select stat_datetime, material_id, sum(cost) as cost
  185. from media_api.bytedance_material_report_daily
  186. where image_mode in (
  187. 'CREATIVE_IMAGE_MODE_VIDEO',
  188. 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL',
  189. 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO'
  190. ) and advertiser_id={account_id}
  191. group by stat_datetime, material_id
  192. ) rep
  193. on video.material_id = rep.material_id
  194. where rep.stat_datetime between date_format(video.create_time, '%Y%m%d') and date_format(date_add(video.create_time, INTERVAL 6 DAY), '%Y%m%d')) t) t
  195. where sum >=5000
  196. ) t
  197. where rn = 1
  198. ) effect
  199. on v.signature = effect.signature
  200. left join (
  201. select stat_datetime, signature, sum
  202. from (
  203. select stat_datetime, signature, sum, row_number() over (partition by signature order by sum) as rn
  204. from (
  205. select stat_datetime,
  206. signature,
  207. sum(cost) over (partition by signature order by stat_datetime) as sum
  208. from (
  209. select stat_datetime, video.signature, cost
  210. from (select material_id, signature, create_time
  211. from (select material_id, min(create_time) as create_time, signature
  212. from media_api.bytedance_file_video_get
  213. where material_id is not null and advertiser_id={account_id}
  214. group by material_id, signature) s) video
  215. inner join
  216. (
  217. select stat_datetime, material_id, sum(cost) as cost
  218. from media_api.bytedance_material_report_daily
  219. where image_mode in (
  220. 'CREATIVE_IMAGE_MODE_VIDEO',
  221. 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL',
  222. 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO'
  223. ) and advertiser_id={account_id}
  224. group by stat_datetime, material_id
  225. ) rep
  226. on video.material_id = rep.material_id
  227. where rep.stat_datetime between date_format(video.create_time, '%Y%m%d') and date_format(date_add(video.create_time, INTERVAL 13 DAY), '%Y%m%d')) t) t
  228. where sum >=50000
  229. ) t
  230. where rn = 1
  231. ) hot
  232. on v.signature = hot.signature
  233. group by advertiser_id, id, v.signature, material_id, tag_category_id, parent_id, tag_code
  234. """.format(account_id=account_id[0])
  235. print(sql)
  236. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  237. self.conn.commit()
  238. except Exception as e:
  239. logging.error(e)
  240. return -1
  241. TidbConn.conn_close(conn=self.conn)
  242. return 0
  243. except Exception as e:
  244. logging.error(e)
  245. if __name__ == '__main__':
  246. status = AppBytedanceFileVideoGetAll().execSql()
  247. if status == 0:
  248. print("作业执行成功")
  249. exit(0)
  250. else:
  251. print("作业执行失败")
  252. exit(-1)