快手剪辑排名.sql 20 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341
  1. select
  2. concat(date_format(date_sub(:date, interval 6 day), '%Y%m%d') ,'-',date_format(:date, '%Y%m%d')) as stat_date,
  3. round(row_number() over (order by clip_charge desc ),0) as rn,
  4. clip_name,
  5. clip_charge,
  6. ifnull(hot_num,0) as hot_num,
  7. ifnull(hot_cost,0) as hot_cost,
  8. concat(round(ifnull(hot_num/material_num*100,0),2),'%') as hot_rate,
  9. ifnull(material_num,0) as material_num,
  10. ifnull(plan_num,0) as plan_num,
  11. ifnull(hotplancount,0) as hotplancount,
  12. ifnull(man_num,0) as man_num,
  13. ifnull(hotmancount,0) as hotmancount
  14. from (
  15. select clip_name,
  16. sum(clip_charge) as clip_charge,
  17. sumcharge,
  18. sum(hot_num) as hot_num,
  19. sum(material_num) as material_num,
  20. sum(hot_cost) as hot_cost,
  21. sum(man_num) as man_num,
  22. sum(hotmancount) as hotmancount,
  23. sum(plan_num) as plan_num,
  24. sum(hotplancount) as hotplancount
  25. from (
  26. select info.clip_id,
  27. case
  28. when info.clip_name like '%汇创%' then replace(clip_name, '(汇创)', '')
  29. else clip_name end as clip_name,
  30. info.clip_charge,
  31. info.sumcharge,
  32. hot_num,
  33. hot_cost,
  34. material_num,
  35. man_num,
  36. hotmancount,
  37. plan_num,
  38. hotplancount
  39. from (select clip_id, clip_name, clip_charge, sumcharge
  40. from (select clip_id, clip_name, sum(charge) as clip_charge
  41. from application.kuaishou_material_video_report_daily_dw
  42. where stat_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
  43. and clip_company_id in
  44. ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
  45. and clip_id is not null
  46. group by clip_id, clip_name) a
  47. join (select sum(charge) as sumcharge
  48. from application.kuaishou_material_video_report_daily_dw
  49. where stat_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
  50. and clip_company_id in
  51. ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
  52. and clip_id is not null) b) info
  53. left join (
  54. select clip_id, count(1) as material_num
  55. from `jeecg-boot`.ctop_material_ascription
  56. where create_time between date_format(date_sub(:date, interval 6 day), '%Y-%m-%d')
  57. and date_format(date_sub(:date, interval -1 day), '%Y-%m-%d')
  58. and clip_id in (select user_id
  59. from `jeecg-boot`.user_company
  60. where company_id in
  61. ('d57fecdcf7a94d009736d9c850731582',
  62. '5e46b5dea3d740eeb8ff1a2895015a4b'))
  63. group by clip_id) b
  64. on info.clip_id = b.clip_id
  65. left join(
  66. select clip_id, sum(hot_num) as hot_num, sum(hot_cost) as hot_cost
  67. from (
  68. select b.clip_id, count(1) as hot_num, sum(hot_cost) as hot_cost
  69. from (select signature
  70. from application.hot_material
  71. where media_id = 2
  72. and hot_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')) a
  73. inner join
  74. (select material_id, clip_id
  75. from `jeecg-boot`.ctop_material_ascription
  76. where clip_id in (select user_id
  77. from `jeecg-boot`.user_company
  78. where company_id in
  79. ('d57fecdcf7a94d009736d9c850731582',
  80. '5e46b5dea3d740eeb8ff1a2895015a4b')
  81. )) b
  82. on a.signature = b.material_id
  83. left join(
  84. select *
  85. from application.hot_material_two_week_cost
  86. where media_id = 2
  87. ) c
  88. on a.signature = c.signature
  89. left join (select material_id, parent_id, parent_name
  90. from application.app_material_tag_busi_info
  91. where tag_category_id = 1
  92. group by material_id, parent_id) d
  93. on a.signature = d.material_id
  94. group by clip_id, a.signature) t
  95. group by clip_id) c
  96. on info.clip_id = c.clip_id
  97. left join (
  98. select clip_id, parent_id, count(distinct tag.material_id) as man_num
  99. from application.app_material_tag_busi_info tag
  100. left join `jeecg-boot`.ctop_material_ascription info
  101. on tag.material_id=info.material_id
  102. where create_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d')
  103. and date_format(date_sub(:date, interval -1 day), '%Y%m%d')
  104. and tag_category_id = 1
  105. and parent_id = 60155
  106. group by clip_id, parent_id) d
  107. on info.clip_id = d.clip_id
  108. left join
  109. (select clip_id, parent_id, count(distinct tag.material_id) as plan_num
  110. from application.app_material_tag_busi_info tag
  111. left join `jeecg-boot`.ctop_material_ascription info
  112. on tag.material_id=info.material_id
  113. where create_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d')
  114. and date_format(date_sub(:date, interval -1 day), '%Y%m%d')
  115. and tag_category_id = 1
  116. and parent_id = 60156
  117. group by clip_id, parent_id) e
  118. on info.clip_id = e.clip_id
  119. left join
  120. (select clip_id, count(distinct signature) as hotmancount
  121. from (select signature
  122. from application.hot_material
  123. where media_id = 2
  124. and hot_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')) a
  125. inner join
  126. (select clip_id, tag.material_id
  127. from application.app_material_tag_busi_info tag
  128. left join `jeecg-boot`.ctop_material_ascription info
  129. on tag.material_id=info.material_id
  130. where tag_category_id = 1
  131. and parent_id = 60155
  132. and clip_id in (select user_id
  133. from `jeecg-boot`.user_company
  134. where company_id in
  135. ('d57fecdcf7a94d009736d9c850731582',
  136. '5e46b5dea3d740eeb8ff1a2895015a4b'))) b
  137. on a.signature = b.material_id
  138. group by clip_id) f
  139. on info.clip_id = f.clip_id
  140. left join
  141. (select clip_id, count(distinct signature) as hotplancount
  142. from (select signature
  143. from application.hot_material
  144. where media_id = 2
  145. and hot_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')) a
  146. inner join
  147. (select clip_id, tag.material_id
  148. from application.app_material_tag_busi_info tag
  149. left join `jeecg-boot`.ctop_material_ascription info
  150. on tag.material_id=info.material_id
  151. where tag_category_id = 1
  152. and parent_id = 60156
  153. and clip_id in (select user_id
  154. from `jeecg-boot`.user_company
  155. where company_id in
  156. ('d57fecdcf7a94d009736d9c850731582',
  157. '5e46b5dea3d740eeb8ff1a2895015a4b'))) b
  158. on a.signature = b.material_id
  159. group by clip_id) i
  160. on info.clip_id = i.clip_id) t
  161. group by clip_name, sumcharge) t
  162. select
  163. concat(date_format(date_sub(:date, interval 6 day), '%Y%m%d') ,'-',date_format(:date, '%Y%m%d')) as stat_date,
  164. '' rn,
  165. '汇总' clip_name,
  166. sum(clip_charge) as clip_charge,
  167. ifnull(sum(hot_num),0) as hot_num,
  168. ifnull(sum(hot_cost),0) as hot_cost,
  169. concat(round(ifnull(sum(hot_num)/sum(material_num)*100,0),2),'%') as hot_rate,
  170. ifnull(sum(material_num),0) as material_num,
  171. ifnull(sum(plan_num),0) as plan_num,
  172. ifnull(sum(hotplancount),0) as hotplancount,
  173. ifnull(sum(man_num),0) as man_num,
  174. ifnull(sum(hotmancount),0) as hotmancount
  175. from (
  176. select clip_name,
  177. sum(clip_charge) as clip_charge,
  178. sumcharge,
  179. sum(hot_num) as hot_num,
  180. sum(material_num) as material_num,
  181. sum(hot_cost) as hot_cost,
  182. sum(man_num) as man_num,
  183. sum(hotmancount) as hotmancount,
  184. sum(plan_num) as plan_num,
  185. sum(hotplancount) as hotplancount
  186. from (
  187. select info.clip_id,
  188. case
  189. when info.clip_name like '%汇创%' then replace(clip_name, '(汇创)', '')
  190. else clip_name end as clip_name,
  191. info.clip_charge,
  192. info.sumcharge,
  193. hot_num,
  194. hot_cost,
  195. material_num,
  196. man_num,
  197. hotmancount,
  198. plan_num,
  199. hotplancount
  200. from (select clip_id, clip_name, clip_charge, sumcharge
  201. from (select clip_id, clip_name, sum(charge) as clip_charge
  202. from application.kuaishou_material_video_report_daily_dw
  203. where stat_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
  204. and clip_company_id in
  205. ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
  206. and clip_id is not null
  207. group by clip_id, clip_name) a
  208. join (select sum(charge) as sumcharge
  209. from application.kuaishou_material_video_report_daily_dw
  210. where stat_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
  211. and clip_company_id in
  212. ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
  213. and clip_id is not null) b) info
  214. left join (
  215. select clip_id, count(1) as material_num
  216. from `jeecg-boot`.ctop_material_ascription
  217. where create_time between date_format(date_sub(:date, interval 6 day), '%Y-%m-%d')
  218. and date_format(date_sub(:date, interval -1 day), '%Y-%m-%d')
  219. and clip_id in (select user_id
  220. from `jeecg-boot`.user_company
  221. where company_id in
  222. ('d57fecdcf7a94d009736d9c850731582',
  223. '5e46b5dea3d740eeb8ff1a2895015a4b'))
  224. group by clip_id) b
  225. on info.clip_id = b.clip_id
  226. left join(
  227. select clip_id, sum(hot_num) as hot_num, sum(hot_cost) as hot_cost
  228. from (
  229. select b.clip_id, count(1) as hot_num, sum(hot_cost) as hot_cost
  230. from (select signature
  231. from application.hot_material
  232. where media_id = 2
  233. and hot_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')) a
  234. inner join
  235. (select material_id, clip_id
  236. from `jeecg-boot`.ctop_material_ascription
  237. where clip_id in (select user_id
  238. from `jeecg-boot`.user_company
  239. where company_id in
  240. ('d57fecdcf7a94d009736d9c850731582',
  241. '5e46b5dea3d740eeb8ff1a2895015a4b')
  242. )) b
  243. on a.signature = b.material_id
  244. left join(
  245. select *
  246. from application.hot_material_two_week_cost
  247. where media_id = 2
  248. ) c
  249. on a.signature = c.signature
  250. left join (select material_id, parent_id, parent_name
  251. from application.app_material_tag_busi_info
  252. where tag_category_id = 1
  253. group by material_id, parent_id) d
  254. on a.signature = d.material_id
  255. group by clip_id, a.signature) t
  256. group by clip_id) c
  257. on info.clip_id = c.clip_id
  258. left join (
  259. select clip_id, parent_id, count(distinct tag.material_id) as man_num
  260. from application.app_material_tag_busi_info tag
  261. left join `jeecg-boot`.ctop_material_ascription info
  262. on tag.material_id=info.material_id
  263. where create_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d')
  264. and date_format(date_sub(:date, interval -1 day), '%Y%m%d')
  265. and tag_category_id = 1
  266. and parent_id = 60155
  267. group by clip_id, parent_id) d
  268. on info.clip_id = d.clip_id
  269. left join
  270. (select clip_id, parent_id, count(distinct tag.material_id) as plan_num
  271. from application.app_material_tag_busi_info tag
  272. left join `jeecg-boot`.ctop_material_ascription info
  273. on tag.material_id=info.material_id
  274. where create_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d')
  275. and date_format(date_sub(:date, interval -1 day), '%Y%m%d')
  276. and tag_category_id = 1
  277. and parent_id = 60156
  278. group by clip_id, parent_id) e
  279. on info.clip_id = e.clip_id
  280. left join
  281. (select clip_id, count(distinct signature) as hotmancount
  282. from (select signature
  283. from application.hot_material
  284. where media_id = 2
  285. and hot_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')) a
  286. inner join
  287. (select clip_id, tag.material_id
  288. from application.app_material_tag_busi_info tag
  289. left join `jeecg-boot`.ctop_material_ascription info
  290. on tag.material_id=info.material_id
  291. where tag_category_id = 1
  292. and parent_id = 60155
  293. and clip_id in (select user_id
  294. from `jeecg-boot`.user_company
  295. where company_id in
  296. ('d57fecdcf7a94d009736d9c850731582',
  297. '5e46b5dea3d740eeb8ff1a2895015a4b'))) b
  298. on a.signature = b.material_id
  299. group by clip_id) f
  300. on info.clip_id = f.clip_id
  301. left join
  302. (select clip_id, count(distinct signature) as hotplancount
  303. from (select signature
  304. from application.hot_material
  305. where media_id = 2
  306. and hot_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')) a
  307. inner join
  308. (select clip_id, tag.material_id
  309. from application.app_material_tag_busi_info tag
  310. left join `jeecg-boot`.ctop_material_ascription info
  311. on tag.material_id=info.material_id
  312. where tag_category_id = 1
  313. and parent_id = 60156
  314. and clip_id in (select user_id
  315. from `jeecg-boot`.user_company
  316. where company_id in
  317. ('d57fecdcf7a94d009736d9c850731582',
  318. '5e46b5dea3d740eeb8ff1a2895015a4b'))) b
  319. on a.signature = b.material_id
  320. group by clip_id) i
  321. on info.clip_id = i.clip_id) t
  322. group by clip_name, sumcharge) t