NewTyMaterialW.sql 1.3 KB

123456789101112131415161718192021222324252627282930313233343536
  1. #1.快手-骄阳近一周素材消耗明细(北京骄阳数字科技有限公司) ----线上
  2. select
  3. '北京骄阳数字科技有限公司' as '公司',
  4. ifnull(report.project_name,'-') AS '项目',
  5. ifnull(report.signature,'-') AS '素材标识',
  6. ifnull(date(video.frast_cost_time),'-') AS '上线时间',
  7. ifnull(report.channel_name,'-') AS '渠道',
  8. sum(report.charge) AS '消耗',
  9. ifnull(report.clip_name,'-') AS '剪辑',
  10. ifnull(report.shot_name,'-') AS '拍摄',
  11. ifnull(report.plan_name,'-') AS '编导',
  12. ifnull(report.leader_name,'-') AS '负责人',
  13. ifnull(report.video_name,'-') AS '素材名称',
  14. ifnull(report.photo_url,'-') AS '素材'
  15. from
  16. (select
  17. t1.account_id,
  18. t1.photo_id,
  19. t1.project_name ,
  20. t1.signature,
  21. t1.channel_name,
  22. t1.charge,
  23. t1.clip_name,
  24. t1.shot_name,
  25. t1.plan_name,
  26. t1.leader_name,
  27. t1.video_name,
  28. t1.photo_url
  29. from application.kuaishou_material_video_report_daily_dw t1
  30. WHERE t1.stat_date between date_format(date_sub(${stat_date}, INTERVAL 6 DAY),'%Y%m%d') AND ${stat_date}
  31. and t1.account_id in (SELECT account_id FROM `jeecg-boot`.ctop_user_allocation WHERE user_id IN (SELECT user_id FROM `jeecg-boot`.user_company WHERE company_id = '5e46b5dea3d740eeb8ff1a2895015a4b'))) report
  32. left join
  33. material_farst_cost_time video
  34. on report.signature=video.signature
  35. group by
  36. report.signature;