select concat(date_format(date_sub(:date, interval 6 day), '%Y%m%d') ,'-',date_format(:date, '%Y%m%d')) as stat_datetime, round(row_number() over (order by plan_cost desc ),0) as rn, plan_name, plan_cost, ifnull(hot_num,0) as hot_num, ifnull(hot_cost,0) as hot_cost, concat(round(ifnull(hot_num/material_num*100,0),2),'%') as hot_rate, ifnull(material_num,0) as material_num, ifnull(plan_num,0) as plan_num, ifnull(hotplancount,0) as hotplancount, ifnull(man_num,0) as man_num, ifnull(hotmancount,0) as hotmancount, concat(round(ifnull(plan_cost/sumcost*100,0),2),'%') as cost_rate from ( select plan_name, sum(plan_cost) as plan_cost, sumcost, sum(hot_num) as hot_num, sum(material_num) as material_num, sum(hot_cost) as hot_cost, sum(man_num) as man_num, sum(hotmancount) as hotmancount, sum(plan_num) as plan_num, sum(hotplancount) as hotplancount from ( select info.plan_id, case when info.plan_name like '%汇创%' then replace(plan_name, '(汇创)', '') else plan_name end as plan_name, info.plan_cost, info.sumcost, hot_num, hot_cost, material_num, man_num, hotmancount, plan_num, hotplancount from (select plan_id, plan_name, plan_cost, sumcost from (select plan_id, plan_name, sum(cost) as plan_cost from application.bytedance_material_video_report_daily_dw where stat_datetime between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d') and clip_company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') and plan_id is not null group by plan_id, plan_name) a join (select sum(cost) as sumcost from application.bytedance_material_video_report_daily_dw where stat_datetime between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d') and clip_company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') and plan_id is not null) b) info left join ( select plan_id, count(1) as material_num from `jeecg-boot`.ctop_material_ascription where create_time between date_format(date_sub(:date, interval 6 day), '%Y-%m-%d') and date_format(date_sub(:date, interval -1 day), '%Y-%m-%d') and plan_id in (select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')) group by plan_id) b on info.plan_id = b.plan_id left join( select plan_id, sum(hot_num) as hot_num, sum(hot_cost) as hot_cost from ( select b.plan_id, count(1) as hot_num, sum(hot_cost) as hot_cost from (select signature from application.hot_material where media_id = 1 and hot_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')) a inner join (select material_id, plan_id from `jeecg-boot`.ctop_material_ascription where plan_id in (select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') )) b on a.signature = b.material_id left join( select * from application.hot_material_two_week_cost where media_id = 1 ) c on a.signature = c.signature left join (select material_id, parent_id, parent_name from application.app_material_tag_busi_info where tag_category_id = 1 group by material_id, parent_id) d on a.signature = d.material_id group by plan_id, a.signature) t group by plan_id) c on info.plan_id = c.plan_id left join ( select plan_id, parent_id, count(distinct tag.material_id) as man_num from application.app_material_tag_busi_info tag left join `jeecg-boot`.ctop_material_ascription info on tag.material_id=info.material_id where create_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(date_sub(:date, interval -1 day), '%Y%m%d') and tag_category_id = 1 and parent_id = 60155 group by plan_id, parent_id) d on info.plan_id = d.plan_id left join (select plan_id, parent_id, count(distinct tag.material_id) as plan_num from application.app_material_tag_busi_info tag left join `jeecg-boot`.ctop_material_ascription info on tag.material_id=info.material_id where create_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(date_sub(:date, interval -1 day), '%Y%m%d') and tag_category_id = 1 and parent_id = 60156 group by plan_id, parent_id) e on info.plan_id = e.plan_id left join (select plan_id, count(distinct signature) as hotmancount from (select signature from application.hot_material where media_id = 1 and hot_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')) a inner join (select plan_id, tag.material_id from application.app_material_tag_busi_info tag left join `jeecg-boot`.ctop_material_ascription info on tag.material_id=info.material_id where tag_category_id = 1 and parent_id = 60155 and plan_id in (select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b'))) b on a.signature = b.material_id group by plan_id) f on info.plan_id = f.plan_id left join (select plan_id, count(distinct signature) as hotplancount from (select signature from application.hot_material where media_id = 1 and hot_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')) a inner join (select plan_id, tag.material_id from application.app_material_tag_busi_info tag left join `jeecg-boot`.ctop_material_ascription info on tag.material_id=info.material_id where tag_category_id = 1 and parent_id = 60156 and plan_id in (select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b'))) b on a.signature = b.material_id group by plan_id) i on info.plan_id = i.plan_id) t group by plan_name, sumcost) t; select concat(date_format(date_sub(:date, interval 6 day), '%Y%m%d') ,'-',date_format(:date, '%Y%m%d')) as stat_datetime, '' rn, '汇总' plan_name, sum(plan_cost) as plan_cost, ifnull(sum(hot_num),0) as hot_num, ifnull(sum(hot_cost),0) as hot_cost, concat(round(ifnull(sum(hot_num)/sum(material_num)*100,0),2),'%') as hot_rate, ifnull(sum(material_num),0) as material_num, ifnull(sum(plan_num),0) as plan_num, ifnull(sum(hotplancount),0) as hotplancount, ifnull(sum(man_num),0) as man_num, ifnull(sum(hotmancount),0) as hotmancount, '' as cost_rate from ( select plan_name, sum(plan_cost) as plan_cost, sumcost, sum(hot_num) as hot_num, sum(material_num) as material_num, sum(hot_cost) as hot_cost, sum(man_num) as man_num, sum(hotmancount) as hotmancount, sum(plan_num) as plan_num, sum(hotplancount) as hotplancount from ( select info.plan_id, case when info.plan_name like '%汇创%' then replace(plan_name, '(汇创)', '') else plan_name end as plan_name, info.plan_cost, info.sumcost, hot_num, hot_cost, material_num, man_num, hotmancount, plan_num, hotplancount from (select plan_id, plan_name, plan_cost, sumcost from (select plan_id, plan_name, sum(cost) as plan_cost from application.bytedance_material_video_report_daily_dw where stat_datetime between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d') and clip_company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') and plan_id is not null group by plan_id, plan_name) a join (select sum(cost) as sumcost from application.bytedance_material_video_report_daily_dw where stat_datetime between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d') and clip_company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') and plan_id is not null) b) info left join ( select plan_id, count(1) as material_num from `jeecg-boot`.ctop_material_ascription where create_time between date_format(date_sub(:date, interval 6 day), '%Y-%m-%d') and date_format(date_sub(:date, interval -1 day), '%Y-%m-%d') and plan_id in (select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')) group by plan_id) b on info.plan_id = b.plan_id left join( select plan_id, sum(hot_num) as hot_num, sum(hot_cost) as hot_cost from ( select b.plan_id, count(1) as hot_num, sum(hot_cost) as hot_cost from (select signature from application.hot_material where media_id = 1 and hot_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')) a inner join (select material_id, plan_id from `jeecg-boot`.ctop_material_ascription where plan_id in (select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') )) b on a.signature = b.material_id left join( select * from application.hot_material_two_week_cost where media_id = 1 ) c on a.signature = c.signature left join (select material_id, parent_id, parent_name from application.app_material_tag_busi_info where tag_category_id = 1 group by material_id, parent_id) d on a.signature = d.material_id group by plan_id, a.signature) t group by plan_id) c on info.plan_id = c.plan_id left join ( select plan_id, parent_id, count(distinct tag.material_id) as man_num from application.app_material_tag_busi_info tag left join `jeecg-boot`.ctop_material_ascription info on tag.material_id=info.material_id where create_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(date_sub(:date, interval -1 day), '%Y%m%d') and tag_category_id = 1 and parent_id = 60155 group by plan_id, parent_id) d on info.plan_id = d.plan_id left join (select plan_id, parent_id, count(distinct tag.material_id) as plan_num from application.app_material_tag_busi_info tag left join `jeecg-boot`.ctop_material_ascription info on tag.material_id=info.material_id where create_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(date_sub(:date, interval -1 day), '%Y%m%d') and tag_category_id = 1 and parent_id = 60156 group by plan_id, parent_id) e on info.plan_id = e.plan_id left join (select plan_id, count(distinct signature) as hotmancount from (select signature from application.hot_material where media_id = 1 and hot_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')) a inner join (select plan_id, tag.material_id from application.app_material_tag_busi_info tag left join `jeecg-boot`.ctop_material_ascription info on tag.material_id=info.material_id where tag_category_id = 1 and parent_id = 60155 and plan_id in (select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b'))) b on a.signature = b.material_id group by plan_id) f on info.plan_id = f.plan_id left join (select plan_id, count(distinct signature) as hotplancount from (select signature from application.hot_material where media_id = 1 and hot_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')) a inner join (select plan_id, tag.material_id from application.app_material_tag_busi_info tag left join `jeecg-boot`.ctop_material_ascription info on tag.material_id=info.material_id where tag_category_id = 1 and parent_id = 60156 and plan_id in (select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b'))) b on a.signature = b.material_id group by plan_id) i on info.plan_id = i.plan_id) t group by plan_name, sumcost) t