NewAllMaterialW.sql 8.1 KB

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