头条设计组长排名.sql 19 KB

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