NewHuaBeiMaterialW.sql 9.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212
  1. #1.快手-华北近一周素材消耗明细(北京汇创思拓数字科技有限公司) --线上
  2. SELECT
  3. ifnull(t2.company_name,'0') AS '公司',
  4. ifnull(t1.project_name,'-') AS '项目',
  5. ifnull(t2.video_code,'-') AS '素材标识',
  6. ifnull(t2.state_date,'-') AS '上线时间',
  7. ifnull(t2.channel_name,'-') AS '渠道',
  8. round(sum(t1.charge),3) AS '消耗',
  9. ifnull(t2.clip_name,'-') AS '剪辑',
  10. ifnull(t2.shot_name,'-') AS '拍摄',
  11. ifnull(t2.plan_name,'-') AS '编导',
  12. ifnull(t2.design_team_leader_name,'-') AS '负责人',
  13. ifnull(t2.video_name,'-') AS '素材名称',
  14. ifnull(t2.url,'-') AS '链接'
  15. FROM
  16. ctop_etl_kuaishou_account_material_report_daily t1
  17. LEFT JOIN ctop_etl_kuaishou_video_info t2 ON t1.signature = t2.video_code
  18. where t1.stat_date between date_sub(${stat_date}, INTERVAL 6 DAY) and ${stat_date} and t2.company_name in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司')
  19. GROUP BY
  20. t1.signature;
  21. #2.头条-华北近一周素材消耗明细(北京汇创思拓数字科技有限公司) --线上
  22. SELECT
  23. ifnull(etlInfo.company_name,'0') AS '公司',
  24. ifnull(allocation.project_name,'-') AS '所属项目',
  25. ifnull(allocation.project_id,'-') AS '项目id',
  26. ifnull(video.signature,'-') AS '唯一标识',
  27. ifnull(round(SUM(report.cost),3),'-') AS '总消耗',
  28. ifnull(etlInfo.state_date,'-') AS '上线日期',
  29. ifnull(allocation.user_name,'-') AS '所属运营',
  30. ifnull(etlInfo.clip_name,'-') AS '剪辑',
  31. ifnull(etlInfo.plan_name,'-') AS '策划',
  32. ifnull(etlInfo.shot_name,'-') AS '拍摄',
  33. '-' AS '平面',
  34. ifnull(video.video_url,'-') AS '素材链接',
  35. ifnull(report.material_id,'-') AS '素材id',
  36. ifnull(mm.realname,'-') AS '负责人',
  37. '视频' AS '素材类型',
  38. ifnull(allocation.project_media_id,'-') AS '是否内广',
  39. ifnull(etlInfo.video_type,'-') AS '视频类型'
  40. FROM
  41. `jeecg-boot`.ctop_bytedance_report_material_daily report
  42. LEFT JOIN ( SELECT material_id, account_id, video_url, signature FROM `jeecg-boot`.ctop_bytedance_video_info GROUP BY account_id, material_id ) video ON video.material_id = report.material_id
  43. AND report.account_id = video.account_id
  44. LEFT JOIN `jeecg-boot`.ctop_bytedance_video_etl_info etlInfo ON etlInfo.video_code = video.signature
  45. LEFT JOIN `jeecg-boot`.ctop_material_ascription u ON u.material_id = video.signature
  46. LEFT JOIN `jeecg-boot`.ctop_user_allocation allocation ON allocation.account_id = report.account_id
  47. left join `jeecg-boot`.sys_user mm on u.leader_id=mm.id
  48. WHERE
  49. report.stat_datetime between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date} and etlInfo.company_name in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司')
  50. GROUP BY
  51. report.material_id
  52. UNION ALL
  53. SELECT
  54. ifnull(uc.company_name,'0') AS '公司',
  55. allocation.project_name AS '所属项目',
  56. ifnull(allocation.project_id,'-') AS '项目id',
  57. video.signature AS '唯一标识',
  58. SUM( report.cost ) AS 'Cost',
  59. report.stat_datetime AS '日期',
  60. allocation.user_name AS '所属运营',
  61. '-' AS '剪辑',
  62. '-' AS '策划',
  63. '-' AS '拍摄',
  64. '-' AS '平面',
  65. video.image_url AS '素材链接',
  66. report.material_id AS '素材id',
  67. '-' AS '负责人',
  68. '图片' AS '素材类型',
  69. allocation.project_media_id AS '是否内广',
  70. '-' AS '视频类型'
  71. FROM
  72. `jeecg-boot`.ctop_bytedance_report_image_daily report
  73. LEFT JOIN `jeecg-boot`.ctop_bytedance_image_info video ON video.material_id = report.material_id
  74. AND report.account_id = video.account_id
  75. LEFT JOIN `jeecg-boot`.ctop_user_allocation allocation ON allocation.account_id = report.account_id
  76. LEFT JOIN `jeecg-boot`.user_company uc ON uc.user_id = allocation.user_id
  77. WHERE
  78. report.stat_datetime between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date} and uc.company_id='d57fecdcf7a94d009736d9c850731582'
  79. group by video.signature;
  80. #3.快手-华北近14天上线素材消耗明细(北京汇创思拓数字科技有限公司) --线上
  81. SELECT
  82. ( CASE WHEN t2.company_name IS NOT NULL THEN t2.company_name ELSE '-' END ) '公司',
  83. t1.project_name AS '项目',
  84. t2.video_code AS '素材标识',
  85. ( CASE WHEN t3.material_innovate = 1 THEN '非创新素材' WHEN t3.material_innovate = 2 THEN '创新素材' ELSE '-' END ) AS '是否创新素材',
  86. t2.state_date AS '上线时间',
  87. t2.channel_name AS '渠道',
  88. sum( t1.charge ) AS '消耗',
  89. t2.clip_name AS '剪辑',
  90. t2.shot_name AS '拍摄',
  91. t2.plan_name AS '编导',
  92. t2.design_team_leader_name AS '负责人',
  93. t2.video_name AS '素材名称',
  94. t2.url AS '素材'
  95. FROM
  96. `jeecg-boot`.ctop_etl_kuaishou_account_material_report_daily t1
  97. LEFT JOIN `jeecg-boot`.ctop_etl_kuaishou_video_info t2 ON t1.signature = t2.video_code
  98. LEFT JOIN `jeecg-boot`.ctop_material_info t3 ON t1.signature = t3.`code`
  99. WHERE
  100. t2.state_date >= date_sub(${stat_date}, INTERVAL 13 DAY)
  101. AND t2.state_date <= ${stat_date} and t2.company_name in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司')
  102. GROUP BY
  103. t1.signature;
  104. #4.头条-华北近14天上线素材消耗明细(北京汇创思拓数字科技有限公司) --线上
  105. select 公司,项目,素材标识,是否创新素材,上线日期,消耗,id,剪辑,拍摄,编导,负责人,素材 from (
  106. SELECT
  107. ( SELECT company_name FROM `jeecg-boot`.user_company WHERE user_id = ( SELECT id FROM `jeecg-boot`.sys_user WHERE realname = t1.clip_name ) ) AS '公司',
  108. t1.project_name AS '项目',
  109. t1.md5 AS '素材标识',
  110. ( CASE WHEN t2.material_innovate = 1 THEN '非创新素材' WHEN t2.material_innovate = 2 THEN '创新素材' ELSE '-' END ) AS '是否创新素材',
  111. t1.material_create_time AS '上线日期',
  112. round(sum(t1.cost),3) AS '消耗',
  113. t1.clip_id AS 'id',
  114. t1.clip_name AS '剪辑',
  115. t1.shot_name AS '拍摄',
  116. t1.plan_name AS '编导',
  117. ( SELECT leader_name FROM `jeecg-boot`.sys_user WHERE realname = t1.clip_name ) AS '负责人',
  118. t1.url AS '素材'
  119. FROM
  120. `jeecg-boot`.etl_report_bytedance_video t1
  121. LEFT JOIN `jeecg-boot`.ctop_material_info t2 ON t1.md5 = t2.`code`
  122. WHERE
  123. date(t1.material_create_time) >= date_sub(${stat_date}, INTERVAL 13 DAY)
  124. AND date(t1.material_create_time)<= ${stat_date}
  125. GROUP BY
  126. t1.md5) info where 公司 in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司');
  127. #5.快手-华北近一周有效素材明细(北京汇创思拓数字科技有限公司) --线上
  128. -- 快手 有效
  129. SELECT
  130. t1.company_name AS '公司',
  131. round(t1.single_week_cost,3) AS '上线一周消耗',
  132. -- total_cost as '总消耗',
  133. ifnull(t1.clip_name,'-') AS '剪辑',
  134. ifnull(t1.shot_name,'-') AS '拍摄',
  135. ifnull(t1.plan_name,'-') AS '编导',
  136. ifnull(t2.leader_name,'-') as '负责人',
  137. ifnull(t1.url,'-') AS '素材链接'
  138. FROM
  139. `jeecg-boot`.ctop_kuaishou_video_etl_info t1
  140. LEFT JOIN `jeecg-boot`.ctop_material_ascription t2
  141. on t1.video_code = t2.material_id
  142. WHERE
  143. t1.effect_date between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date}
  144. AND t1.effect_date IS NOT NULL
  145. AND t1.effect_day_num <= 7 and t1.company_name in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司');
  146. #6.快手-华北近一周爆款素材明细(北京汇创思拓数字科技有限公司) --线上
  147. -- 快手 爆款
  148. SELECT
  149. t1.company_name AS '公司',
  150. round(t1.two_week_cost,3) AS '双周消耗',
  151. -- total_cost as '总消耗',
  152. t1.state_date as '上线日期',
  153. ifnull(t1.clip_name,'-') AS '剪辑',
  154. ifnull(t1.shot_name,'-') AS '拍摄',
  155. ifnull(t1.plan_name,'-') AS '编导',
  156. ifnull(t3.realname,'-') as '负责人',
  157. ifnull(t1.url,'-') AS '素材链接'
  158. FROM
  159. `jeecg-boot`.ctop_kuaishou_video_etl_info t1
  160. LEFT JOIN `jeecg-boot`.ctop_material_ascription t2
  161. on t1.video_code = t2.material_id
  162. left join `jeecg-boot`.sys_user t3
  163. on t2.leader_id=t3.id
  164. WHERE
  165. t1.faddish_date between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date}
  166. AND t1.faddish_date IS NOT NULL
  167. AND t1.faddish_day_num <=14 and t1.company_name in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司');
  168. #7.头条-华北近一周有效素材明细(北京汇创思拓数字科技有限公司) --线上
  169. -- 头条有效(线上)
  170. SELECT
  171. t1.company_name AS '公司',
  172. round(t1.single_week_cost,3) AS '上线一周消耗',
  173. -- total_cost as '总消耗',
  174. ifnull(t1.clip_name,'-') AS '剪辑',
  175. ifnull(t1.shot_name,'-') AS '拍摄',
  176. ifnull(t1.plan_name,'-') AS '编导',
  177. ifnull(t2.leader_name,'-') as '负责人',
  178. ifnull(t1.url,'-') AS '素材链接'
  179. FROM
  180. `jeecg-boot`.ctop_bytedance_video_etl_info t1
  181. LEFT JOIN `jeecg-boot`.ctop_material_ascription t2
  182. on t1.video_code = t2.material_id
  183. WHERE
  184. t1.effect_date between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date}
  185. AND t1.effect_date IS NOT NULL
  186. AND t1.effect_day_num <= 7 and t1.company_name in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司');
  187. #8.头条-华北近一周爆款素材明细(北京汇创思拓数字科技有限公司)
  188. -- 头条爆款(线上)
  189. SELECT
  190. t1.company_name AS '公司',
  191. round(t1.two_week_cost,3) AS '上线两周消耗',
  192. -- total_cost as '总消耗',
  193. ifnull(t1.clip_name,'-') AS '剪辑',
  194. ifnull(t1.shot_name,'-') AS '拍摄',
  195. ifnull(t1.plan_name,'-') AS '编导',
  196. ifnull(t2.leader_name,'-') as '负责人',
  197. ifnull(t1.url,'-') AS '素材链接'
  198. FROM
  199. `jeecg-boot`.ctop_bytedance_video_etl_info t1
  200. LEFT JOIN `jeecg-boot`.ctop_material_ascription t2
  201. on t1.video_code = t2.material_id
  202. WHERE
  203. t1.faddish_date between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date}
  204. AND t1.faddish_date IS NOT NULL
  205. AND t1.faddish_day_num <= 14 and t1.company_name in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司');