#1.快手-华北近一周素材消耗明细(北京汇创思拓数字科技有限公司) --线上 SELECT ifnull(t2.company_name,'0') AS '公司', ifnull(t1.project_name,'-') AS '项目', ifnull(t2.video_code,'-') AS '素材标识', ifnull(t2.state_date,'-') AS '上线时间', ifnull(t2.channel_name,'-') AS '渠道', round(sum(t1.charge),3) AS '消耗', ifnull(t2.clip_name,'-') AS '剪辑', ifnull(t2.shot_name,'-') AS '拍摄', ifnull(t2.plan_name,'-') AS '编导', ifnull(t2.design_team_leader_name,'-') AS '负责人', ifnull(t2.video_name,'-') AS '素材名称', ifnull(t2.url,'-') AS '链接' FROM ctop_etl_kuaishou_account_material_report_daily t1 LEFT JOIN ctop_etl_kuaishou_video_info t2 ON t1.signature = t2.video_code where t1.stat_date between date_sub(${stat_date}, INTERVAL 6 DAY) and ${stat_date} and t2.company_name in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司') GROUP BY t1.signature; #2.头条-华北近一周素材消耗明细(北京汇创思拓数字科技有限公司) --线上 SELECT ifnull(etlInfo.company_name,'0') AS '公司', ifnull(allocation.project_name,'-') AS '所属项目', ifnull(allocation.project_id,'-') AS '项目id', ifnull(video.signature,'-') AS '唯一标识', ifnull(round(SUM(report.cost),3),'-') AS '总消耗', ifnull(etlInfo.state_date,'-') AS '上线日期', ifnull(allocation.user_name,'-') AS '所属运营', ifnull(etlInfo.clip_name,'-') AS '剪辑', ifnull(etlInfo.plan_name,'-') AS '策划', ifnull(etlInfo.shot_name,'-') AS '拍摄', '-' AS '平面', ifnull(video.video_url,'-') AS '素材链接', ifnull(report.material_id,'-') AS '素材id', ifnull(mm.realname,'-') AS '负责人', '视频' AS '素材类型', ifnull(allocation.project_media_id,'-') AS '是否内广', ifnull(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 left join `jeecg-boot`.sys_user mm on u.leader_id=mm.id WHERE report.stat_datetime between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date} and etlInfo.company_name in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司') GROUP BY report.material_id UNION ALL SELECT ifnull(uc.company_name,'0') AS '公司', allocation.project_name AS '所属项目', ifnull(allocation.project_id,'-') AS '项目id', video.signature AS '唯一标识', SUM( report.cost ) AS 'Cost', 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 `jeecg-boot`.user_company uc ON uc.user_id = allocation.user_id WHERE report.stat_datetime between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date} and uc.company_id='d57fecdcf7a94d009736d9c850731582' group by video.signature; #3.快手-华北近14天上线素材消耗明细(北京汇创思拓数字科技有限公司) --线上 SELECT ( CASE WHEN t2.company_name IS NOT NULL THEN t2.company_name ELSE '-' END ) '公司', t1.project_name AS '项目', t2.video_code AS '素材标识', ( CASE WHEN t3.material_innovate = 1 THEN '非创新素材' WHEN t3.material_innovate = 2 THEN '创新素材' ELSE '-' END ) AS '是否创新素材', t2.state_date AS '上线时间', t2.channel_name AS '渠道', sum( t1.charge ) AS '消耗', t2.clip_name AS '剪辑', t2.shot_name AS '拍摄', t2.plan_name AS '编导', t2.design_team_leader_name AS '负责人', t2.video_name AS '素材名称', t2.url AS '素材' FROM `jeecg-boot`.ctop_etl_kuaishou_account_material_report_daily t1 LEFT JOIN `jeecg-boot`.ctop_etl_kuaishou_video_info t2 ON t1.signature = t2.video_code LEFT JOIN `jeecg-boot`.ctop_material_info t3 ON t1.signature = t3.`code` WHERE t2.state_date >= date_sub(${stat_date}, INTERVAL 13 DAY) AND t2.state_date <= ${stat_date} and t2.company_name in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司') GROUP BY t1.signature; #4.头条-华北近14天上线素材消耗明细(北京汇创思拓数字科技有限公司) --线上 select 公司,项目,素材标识,是否创新素材,上线日期,消耗,id,剪辑,拍摄,编导,负责人,素材 from ( SELECT ( SELECT company_name FROM `jeecg-boot`.user_company WHERE user_id = ( SELECT id FROM `jeecg-boot`.sys_user WHERE realname = t1.clip_name ) ) AS '公司', t1.project_name AS '项目', t1.md5 AS '素材标识', ( CASE WHEN t2.material_innovate = 1 THEN '非创新素材' WHEN t2.material_innovate = 2 THEN '创新素材' ELSE '-' END ) AS '是否创新素材', t1.material_create_time AS '上线日期', round(sum(t1.cost),3) AS '消耗', t1.clip_id AS 'id', t1.clip_name AS '剪辑', t1.shot_name AS '拍摄', t1.plan_name AS '编导', ( SELECT leader_name FROM `jeecg-boot`.sys_user WHERE realname = t1.clip_name ) AS '负责人', t1.url AS '素材' FROM `jeecg-boot`.etl_report_bytedance_video t1 LEFT JOIN `jeecg-boot`.ctop_material_info t2 ON t1.md5 = t2.`code` WHERE date(t1.material_create_time) >= date_sub(${stat_date}, INTERVAL 13 DAY) AND date(t1.material_create_time)<= ${stat_date} GROUP BY t1.md5) info where 公司 in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司'); #5.快手-华北近一周有效素材明细(北京汇创思拓数字科技有限公司) --线上 -- 快手 有效 SELECT t1.company_name AS '公司', round(t1.single_week_cost,3) AS '上线一周消耗', -- total_cost as '总消耗', ifnull(t1.clip_name,'-') AS '剪辑', ifnull(t1.shot_name,'-') AS '拍摄', ifnull(t1.plan_name,'-') AS '编导', ifnull(t2.leader_name,'-') as '负责人', ifnull(t1.url,'-') AS '素材链接' FROM `jeecg-boot`.ctop_kuaishou_video_etl_info t1 LEFT JOIN `jeecg-boot`.ctop_material_ascription t2 on t1.video_code = t2.material_id WHERE t1.effect_date between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date} AND t1.effect_date IS NOT NULL AND t1.effect_day_num <= 7 and t1.company_name in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司'); #6.快手-华北近一周爆款素材明细(北京汇创思拓数字科技有限公司) --线上 -- 快手 爆款 SELECT t1.company_name AS '公司', round(t1.two_week_cost,3) AS '双周消耗', -- total_cost as '总消耗', t1.state_date as '上线日期', ifnull(t1.clip_name,'-') AS '剪辑', ifnull(t1.shot_name,'-') AS '拍摄', ifnull(t1.plan_name,'-') AS '编导', ifnull(t3.realname,'-') as '负责人', ifnull(t1.url,'-') AS '素材链接' FROM `jeecg-boot`.ctop_kuaishou_video_etl_info t1 LEFT JOIN `jeecg-boot`.ctop_material_ascription t2 on t1.video_code = t2.material_id left join `jeecg-boot`.sys_user t3 on t2.leader_id=t3.id WHERE t1.faddish_date between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date} AND t1.faddish_date IS NOT NULL AND t1.faddish_day_num <=14 and t1.company_name in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司'); #7.头条-华北近一周有效素材明细(北京汇创思拓数字科技有限公司) --线上 -- 头条有效(线上) SELECT t1.company_name AS '公司', round(t1.single_week_cost,3) AS '上线一周消耗', -- total_cost as '总消耗', ifnull(t1.clip_name,'-') AS '剪辑', ifnull(t1.shot_name,'-') AS '拍摄', ifnull(t1.plan_name,'-') AS '编导', ifnull(t2.leader_name,'-') as '负责人', ifnull(t1.url,'-') AS '素材链接' FROM `jeecg-boot`.ctop_bytedance_video_etl_info t1 LEFT JOIN `jeecg-boot`.ctop_material_ascription t2 on t1.video_code = t2.material_id WHERE t1.effect_date between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date} AND t1.effect_date IS NOT NULL AND t1.effect_day_num <= 7 and t1.company_name in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司'); #8.头条-华北近一周爆款素材明细(北京汇创思拓数字科技有限公司) -- 头条爆款(线上) SELECT t1.company_name AS '公司', round(t1.two_week_cost,3) AS '上线两周消耗', -- total_cost as '总消耗', ifnull(t1.clip_name,'-') AS '剪辑', ifnull(t1.shot_name,'-') AS '拍摄', ifnull(t1.plan_name,'-') AS '编导', ifnull(t2.leader_name,'-') as '负责人', ifnull(t1.url,'-') AS '素材链接' FROM `jeecg-boot`.ctop_bytedance_video_etl_info t1 LEFT JOIN `jeecg-boot`.ctop_material_ascription t2 on t1.video_code = t2.material_id WHERE t1.faddish_date between date_sub(${stat_date}, INTERVAL 6 DAY) AND ${stat_date} AND t1.faddish_date IS NOT NULL AND t1.faddish_day_num <= 14 and t1.company_name in ('北京汇创思拓数字科技有限公司(华北)','北京汇创思拓数字科技有限公司');