NewMgsMaterialW 3.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101
  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.leader_id in (SELECT user_id FROM `jeecg-boot`.user_company WHERE company_id = '1648c7ceb4d449a9a13fbed7a8d9976c')) report
  32. left join
  33. `jeecg-boot`.material_farst_cost_time video
  34. on report.signature=video.signature
  35. group by
  36. report.signature;
  37. #2.头条-秒构思近一周素材消耗明细(杭州妙构思数字科技有限公司) --线上
  38. SELECT
  39. etlInfo.company_name AS '公司',
  40. allocation.project_name AS '所属项目',
  41. allocation.project_id AS '项目id',
  42. CONCAT( allocation.account_id, '_' ) AS '所属账户Id',
  43. video.signature AS '唯一标识',
  44. SUM( report.cost ) AS '总消耗',
  45. etlInfo.state_date AS '上线日期',
  46. allocation.user_name AS '所属运营',
  47. etlInfo.clip_name AS '剪辑',
  48. etlInfo.plan_name AS '策划',
  49. etlInfo.shot_name AS '拍摄',
  50. '' AS '平面',
  51. video.video_url AS '素材链接',
  52. report.material_id AS '素材id',
  53. u.leader_name AS '负责人',
  54. '视频' AS '素材类型',
  55. allocation.project_media_id AS '是否内广',
  56. etlInfo.video_type AS '视频类型'
  57. FROM
  58. `jeecg-boot`.ctop_bytedance_report_material_daily report
  59. 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
  60. AND report.account_id = video.account_id
  61. LEFT JOIN `jeecg-boot`.ctop_bytedance_video_etl_info etlInfo ON etlInfo.video_code = video.signature
  62. LEFT JOIN `jeecg-boot`.ctop_material_ascription u ON u.material_id = video.signature
  63. LEFT JOIN `jeecg-boot`.ctop_user_allocation allocation ON allocation.account_id = report.account_id
  64. WHERE
  65. report.stat_datetime >= date_sub(${stat_date}, INTERVAL 6 DAY)
  66. AND report.stat_datetime <= ${stat_date} and etlInfo.company_name in ('杭州妙构思数字科技有限公司')
  67. GROUP BY
  68. report.material_id,
  69. report.account_id UNION ALL
  70. SELECT
  71. uc.company_name AS '公司',
  72. allocation.project_name AS '所属项目',
  73. allocation.project_id AS '项目id',
  74. CONCAT( allocation.account_id, '_' ) AS '所属账户Id',
  75. video.signature AS '唯一标识',
  76. SUM( report.cost ) AS 'totalCost',
  77. report.stat_datetime AS '日期',
  78. allocation.user_name AS '所属运营',
  79. '' AS '剪辑',
  80. '' AS '策划',
  81. '' AS '拍摄',
  82. '' AS '平面',
  83. video.image_url AS '素材链接',
  84. report.material_id AS '素材id',
  85. '' AS '负责人',
  86. '图片' AS '素材类型',
  87. allocation.project_media_id AS '是否内广',
  88. '' AS '视频类型'
  89. FROM
  90. `jeecg-boot`.ctop_bytedance_report_image_daily report
  91. LEFT JOIN `jeecg-boot`.ctop_bytedance_image_info video ON video.material_id = report.material_id
  92. AND report.account_id = video.account_id
  93. LEFT JOIN `jeecg-boot`.ctop_user_allocation allocation ON allocation.account_id = report.account_id
  94. LEFT JOIN user_company uc ON uc.user_id = allocation.user_id
  95. WHERE
  96. report.stat_datetime >= date_sub(${stat_date}, INTERVAL 6 DAY)
  97. AND report.stat_datetime <= ${stat_date} and uc.company_id='1648c7ceb4d449a9a13fbed7a8d9976c'
  98. GROUP BY
  99. report.material_id,
  100. report.account_id;