快手项目报表.sql 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287
  1. --明细
  2. select concat(:date,'-',:enddate) as `日期`,
  3. channle.project_name as `项目名称`,
  4. round(ifnull(sum(channle.in_charge), 0), 2) as `内部素材消耗`,
  5. ifnull(sum(inner_num), 0) as `内部消耗素材数`,
  6. round(ifnull(sum(channle.in_charge) / sum(inner_num), 0), 2) as `内部素材平均消耗`,
  7. ifnull(sum(inner_first_cost_num), 0) as `内部昨日素材上新数`,
  8. round(ifnull(sum(inner_first_cost_charge) / sum(inner_first_cost_num), 0), 2) as `内部昨日素材平均消耗`,
  9. round(ifnull(sum(out_charge), 0), 2) as `外部素材消耗`,
  10. ifnull(sum(outer_num), 0) as `外部消耗素材数`,
  11. round(ifnull(sum(out_charge) / sum(outer_num), 0), 2) as `外部素材平均消耗`,
  12. sum(first_cost_num) as `外部昨日素材上新数`,
  13. round(ifnull(sum(first_cost_charge) / sum(first_cost_num), 0), 2) as `外部昨日素材平均消耗`,
  14. round(ifnull(sum(sum.sum_charge), 0), 2) as `总消耗`,
  15. concat(round(ifnull(sum(channle.in_charge) / sum(sum.sum_charge) * 100, 0), 2), '%') as `内部渗透率`,
  16. concat(round(ifnull((sum(channle.in_charge) / sum(sum.sum_charge) -
  17. sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) /
  18. (sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) * 100, 0), 2), '%') as `环比`
  19. from (select project_id,
  20. project_name,
  21. count(distinct a.signature) as inner_num,
  22. sum(charge) as in_charge,
  23. count(distinct c.signature) as inner_first_cost_num,
  24. sum(if(c.signature is not null, charge, 0)) as inner_first_cost_charge
  25. from application.kuaishou_material_video_report_daily_dw a
  26. inner join (
  27. select account_id
  28. from `jeecg-boot`.ctop_user_allocation
  29. where user_id in (
  30. select user_id
  31. from `jeecg-boot`.user_company
  32. where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
  33. )
  34. ) b
  35. on a.account_id = b.account_id
  36. left join (select signature
  37. from application.material_farst_cost_time
  38. where frast_cost_time = date_format(:date, '%Y%m%d')) c
  39. on a.signature = c.signature
  40. where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
  41. and clip_company_id in
  42. ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b',
  43. '1648c7ceb4d449a9a13fbed7a8d9976c')
  44. and channel_name = '内部'
  45. and a.charge > 0
  46. group by channel_name, project_id, project_name) channle
  47. left join(
  48. select project_id,
  49. project_name,
  50. sum(charge) as sum_charge
  51. from application.kuaishou_material_video_report_daily_dw a
  52. inner join (
  53. select account_id
  54. from `jeecg-boot`.ctop_user_allocation
  55. where user_id in (
  56. select user_id
  57. from `jeecg-boot`.user_company
  58. where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
  59. )
  60. ) b
  61. on a.account_id = b.account_id
  62. where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
  63. and a.charge > 0
  64. group by project_id, project_name
  65. ) sum
  66. on channle.project_id = sum.project_id
  67. left join(
  68. select project_id,
  69. project_name,
  70. count(distinct a.signature) as outer_num,
  71. sum(charge) as out_charge,
  72. count(distinct c.signature) as first_cost_num,
  73. sum(if(c.signature is not null, charge, 0)) as first_cost_charge
  74. from application.kuaishou_material_video_report_daily_dw a
  75. inner join (
  76. select account_id
  77. from `jeecg-boot`.ctop_user_allocation
  78. where user_id in (
  79. select user_id
  80. from `jeecg-boot`.user_company
  81. where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
  82. )
  83. ) b
  84. on a.account_id = b.account_id
  85. left join(select signature
  86. from application.material_farst_cost_time
  87. where frast_cost_time between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')) c
  88. on a.signature = c.signature
  89. where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
  90. and a.charge > 0
  91. and (channel_type = 1 or channel_name = '素造')
  92. group by channel_name, project_id, project_name
  93. ) outc
  94. on channle.project_id = outc.project_id
  95. left join (select project_id,
  96. project_name,
  97. sum(charge) as in_charge
  98. from application.kuaishou_material_video_report_daily_dw a
  99. inner join (
  100. select account_id
  101. from `jeecg-boot`.ctop_user_allocation
  102. where user_id in (
  103. select user_id
  104. from `jeecg-boot`.user_company
  105. where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
  106. )
  107. ) b
  108. on a.account_id = b.account_id
  109. where a.stat_date between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d')
  110. and clip_company_id in
  111. ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b',
  112. '1648c7ceb4d449a9a13fbed7a8d9976c')
  113. and channel_name = '内部'
  114. group by channel_name, project_id, project_name) channle_yes
  115. on channle.project_id = channle_yes.project_id
  116. left join(
  117. select project_id,
  118. project_name,
  119. sum(charge) as sum_charge
  120. from application.kuaishou_material_video_report_daily_dw a
  121. inner join (
  122. select account_id
  123. from `jeecg-boot`.ctop_user_allocation
  124. where user_id in (
  125. select user_id
  126. from `jeecg-boot`.user_company
  127. where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
  128. )
  129. ) b
  130. on a.account_id = b.account_id
  131. where a.stat_date between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d')
  132. group by project_id, project_name
  133. ) yes_sum
  134. on channle.project_id = yes_sum.project_id
  135. group by channle.project_name
  136. order by `总消耗` desc
  137. --汇总
  138. select concat(:date,'-',:enddate) as `日期`,
  139. '汇总' as `项目名称`,
  140. round(ifnull(sum(channle.in_charge), 0), 2) as `内部素材消耗`,
  141. ifnull(sum(inner_num), 0) as `内部消耗素材数`,
  142. round(ifnull(sum(channle.in_charge) / sum(inner_num), 0), 2) as `内部素材平均消耗`,
  143. ifnull(sum(inner_first_cost_num), 0) as `内部昨日素材上新数`,
  144. round(ifnull(sum(inner_first_cost_charge) / sum(inner_first_cost_num), 0), 2) as `内部昨日素材平均消耗`,
  145. round(ifnull(sum(out_charge), 0), 2) as `外部素材消耗`,
  146. ifnull(sum(outer_num), 0) as `外部消耗素材数`,
  147. round(ifnull(sum(out_charge) / sum(outer_num), 0), 2) as `外部素材平均消耗`,
  148. sum(first_cost_num) as `外部昨日素材上新数`,
  149. round(ifnull(sum(first_cost_charge) / sum(first_cost_num), 0), 2) as `外部昨日素材平均消耗`,
  150. round(ifnull(sum(sum.sum_charge), 0), 2) as 总消耗,
  151. concat(round(ifnull(sum(channle.in_charge) / sum(sum.sum_charge) * 100, 0), 2), '%') as `内部渗透率`,
  152. concat(round(ifnull((sum(channle.in_charge) / sum(sum.sum_charge) -
  153. sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) /
  154. (sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) * 100, 0), 2), '%') as `环比`
  155. from (select project_id,
  156. project_name,
  157. count(distinct a.signature) as inner_num,
  158. sum(charge) as in_charge,
  159. count(distinct c.signature) as inner_first_cost_num,
  160. sum(if(c.signature is not null, charge, 0)) as inner_first_cost_charge
  161. from application.kuaishou_material_video_report_daily_dw a
  162. inner join (
  163. select account_id
  164. from `jeecg-boot`.ctop_user_allocation
  165. where user_id in (
  166. select user_id
  167. from `jeecg-boot`.user_company
  168. where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
  169. )
  170. ) b
  171. on a.account_id = b.account_id
  172. left join (select signature
  173. from application.material_farst_cost_time
  174. where frast_cost_time between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')) c
  175. on a.signature = c.signature
  176. where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
  177. and clip_company_id in
  178. ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b',
  179. '1648c7ceb4d449a9a13fbed7a8d9976c')
  180. and channel_name = '内部'
  181. and a.charge > 0
  182. group by channel_name, project_id, project_name) channle
  183. left join(
  184. select project_id,
  185. project_name,
  186. sum(charge) as sum_charge
  187. from application.kuaishou_material_video_report_daily_dw a
  188. inner join (
  189. select account_id
  190. from `jeecg-boot`.ctop_user_allocation
  191. where user_id in (
  192. select user_id
  193. from `jeecg-boot`.user_company
  194. where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
  195. )
  196. ) b
  197. on a.account_id = b.account_id
  198. where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
  199. and a.charge > 0
  200. group by project_id, project_name
  201. ) sum
  202. on channle.project_id = sum.project_id
  203. left join(
  204. select project_id,
  205. project_name,
  206. count(distinct a.signature) as outer_num,
  207. sum(charge) as out_charge,
  208. count(distinct c.signature) as first_cost_num,
  209. sum(if(c.signature is not null, charge, 0)) as first_cost_charge
  210. from application.kuaishou_material_video_report_daily_dw a
  211. inner join (
  212. select account_id
  213. from `jeecg-boot`.ctop_user_allocation
  214. where user_id in (
  215. select user_id
  216. from `jeecg-boot`.user_company
  217. where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
  218. )
  219. ) b
  220. on a.account_id = b.account_id
  221. left join(select signature
  222. from application.material_farst_cost_time
  223. where frast_cost_time between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')) c
  224. on a.signature = c.signature
  225. where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
  226. and a.charge > 0
  227. and (channel_type = 1 or channel_name = '素造')
  228. group by channel_name, project_id, project_name
  229. ) outc
  230. on channle.project_id = outc.project_id
  231. left join (select project_id,
  232. project_name,
  233. sum(charge) as in_charge
  234. from application.kuaishou_material_video_report_daily_dw a
  235. inner join (
  236. select account_id
  237. from `jeecg-boot`.ctop_user_allocation
  238. where user_id in (
  239. select user_id
  240. from `jeecg-boot`.user_company
  241. where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
  242. )
  243. ) b
  244. on a.account_id = b.account_id
  245. where a.stat_date between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d')
  246. and clip_company_id in
  247. ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b',
  248. '1648c7ceb4d449a9a13fbed7a8d9976c')
  249. and channel_name = '内部'
  250. group by channel_name, project_id, project_name) channle_yes
  251. on channle.project_id = channle_yes.project_id
  252. left join(
  253. select project_id,
  254. project_name,
  255. sum(charge) as sum_charge
  256. from application.kuaishou_material_video_report_daily_dw a
  257. inner join (
  258. select account_id
  259. from `jeecg-boot`.ctop_user_allocation
  260. where user_id in (
  261. select user_id
  262. from `jeecg-boot`.user_company
  263. where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
  264. )
  265. ) b
  266. on a.account_id = b.account_id
  267. where a.stat_date between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d')
  268. group by project_id, project_name
  269. ) yes_sum
  270. on channle.project_id = yes_sum.project_id