头条编导排名.sql 20 KB

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