AppBytedanceFileVideoGet.py 12 KB

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