AppKuaishouVideoList.py 14 KB

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