AppBytedanceFileVideoGetAll.py 11 KB

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