#1.快手-秒构思近一周素材消耗明细(杭州妙构思数字科技有限公司) --线上 select '杭州妙构思数字科技有限公司' as '公司', ifnull(report.project_name,'-') AS '项目', ifnull(report.signature,'-') AS '素材标识', ifnull(date(video.frast_cost_time),'-') AS '上线时间', ifnull(report.channel_name,'-') AS '渠道', sum(report.charge) AS '消耗', ifnull(report.clip_name,'-') AS '剪辑', ifnull(report.shot_name,'-') AS '拍摄', ifnull(report.plan_name,'-') AS '编导', ifnull(report.leader_name,'-') AS '负责人', ifnull(report.video_name,'-') AS '素材名称', ifnull(report.photo_url,'-') AS '素材' from (select t1.account_id, t1.photo_id, t1.project_name , t1.signature, t1.channel_name, t1.charge, t1.clip_name, t1.shot_name, t1.plan_name, t1.leader_name, t1.video_name, t1.photo_url from application.kuaishou_material_video_report_daily_dw t1 WHERE t1.stat_date between date_format(date_sub(${stat_date} , INTERVAL 6 DAY),'%Y%m%d') AND ${stat_date} and t1.leader_id in (SELECT user_id FROM `jeecg-boot`.user_company WHERE company_id = '1648c7ceb4d449a9a13fbed7a8d9976c')) report left join `jeecg-boot`.material_farst_cost_time video on report.signature=video.signature group by report.signature; #2.头条-秒构思近一周素材消耗明细(杭州妙构思数字科技有限公司) --线上 SELECT etlInfo.company_name AS '公司', allocation.project_name AS '所属项目', allocation.project_id AS '项目id', CONCAT( allocation.account_id, '_' ) AS '所属账户Id', video.signature AS '唯一标识', SUM( report.cost ) AS '总消耗', etlInfo.state_date AS '上线日期', allocation.user_name AS '所属运营', etlInfo.clip_name AS '剪辑', etlInfo.plan_name AS '策划', etlInfo.shot_name AS '拍摄', '' AS '平面', video.video_url AS '素材链接', report.material_id AS '素材id', u.leader_name AS '负责人', '视频' AS '素材类型', allocation.project_media_id AS '是否内广', etlInfo.video_type AS '视频类型' FROM `jeecg-boot`.ctop_bytedance_report_material_daily report 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 AND report.account_id = video.account_id LEFT JOIN `jeecg-boot`.ctop_bytedance_video_etl_info etlInfo ON etlInfo.video_code = video.signature LEFT JOIN `jeecg-boot`.ctop_material_ascription u ON u.material_id = video.signature LEFT JOIN `jeecg-boot`.ctop_user_allocation allocation ON allocation.account_id = report.account_id WHERE report.stat_datetime >= date_sub(${stat_date}, INTERVAL 6 DAY) AND report.stat_datetime <= ${stat_date} and etlInfo.company_name in ('杭州妙构思数字科技有限公司') GROUP BY report.material_id, report.account_id UNION ALL SELECT uc.company_name AS '公司', allocation.project_name AS '所属项目', allocation.project_id AS '项目id', CONCAT( allocation.account_id, '_' ) AS '所属账户Id', video.signature AS '唯一标识', SUM( report.cost ) AS 'totalCost', report.stat_datetime AS '日期', allocation.user_name AS '所属运营', '' AS '剪辑', '' AS '策划', '' AS '拍摄', '' AS '平面', video.image_url AS '素材链接', report.material_id AS '素材id', '' AS '负责人', '图片' AS '素材类型', allocation.project_media_id AS '是否内广', '' AS '视频类型' FROM `jeecg-boot`.ctop_bytedance_report_image_daily report LEFT JOIN `jeecg-boot`.ctop_bytedance_image_info video ON video.material_id = report.material_id AND report.account_id = video.account_id LEFT JOIN `jeecg-boot`.ctop_user_allocation allocation ON allocation.account_id = report.account_id LEFT JOIN user_company uc ON uc.user_id = allocation.user_id WHERE report.stat_datetime >= date_sub(${stat_date}, INTERVAL 6 DAY) AND report.stat_datetime <= ${stat_date} and uc.company_id='1648c7ceb4d449a9a13fbed7a8d9976c' GROUP BY report.material_id, report.account_id;