AppBytedanceFileVideoGetAll.py 11 KB

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