--明细 select concat(:date,'-',:enddate) as `日期`, channle.project_name as `项目名称`, round(ifnull(sum(channle.in_charge), 0), 2) as `内部素材消耗`, ifnull(sum(inner_num), 0) as `内部消耗素材数`, round(ifnull(sum(channle.in_charge) / sum(inner_num), 0), 2) as `内部素材平均消耗`, ifnull(sum(inner_first_cost_num), 0) as `内部昨日素材上新数`, round(ifnull(sum(inner_first_cost_charge) / sum(inner_first_cost_num), 0), 2) as `内部昨日素材平均消耗`, round(ifnull(sum(out_charge), 0), 2) as `外部素材消耗`, ifnull(sum(outer_num), 0) as `外部消耗素材数`, round(ifnull(sum(out_charge) / sum(outer_num), 0), 2) as `外部素材平均消耗`, sum(first_cost_num) as `外部昨日素材上新数`, round(ifnull(sum(first_cost_charge) / sum(first_cost_num), 0), 2) as `外部昨日素材平均消耗`, round(ifnull(sum(sum.sum_charge), 0), 2) as `总消耗`, concat(round(ifnull(sum(channle.in_charge) / sum(sum.sum_charge) * 100, 0), 2), '%') as `内部渗透率`, concat(round(ifnull((sum(channle.in_charge) / sum(sum.sum_charge) - sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) / (sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) * 100, 0), 2), '%') as `环比` from (select project_id, project_name, count(distinct a.signature) as inner_num, sum(charge) as in_charge, count(distinct c.signature) as inner_first_cost_num, sum(if(c.signature is not null, charge, 0)) as inner_first_cost_charge from application.kuaishou_material_video_report_daily_dw a inner join ( select account_id from `jeecg-boot`.ctop_user_allocation where user_id in ( select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') ) ) b on a.account_id = b.account_id left join (select signature from application.material_farst_cost_time where frast_cost_time = date_format(:date, '%Y%m%d')) c on a.signature = c.signature where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d') and clip_company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b', '1648c7ceb4d449a9a13fbed7a8d9976c') and channel_name = '内部' and a.charge > 0 group by channel_name, project_id, project_name) channle left join( select project_id, project_name, sum(charge) as sum_charge from application.kuaishou_material_video_report_daily_dw a inner join ( select account_id from `jeecg-boot`.ctop_user_allocation where user_id in ( select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') ) ) b on a.account_id = b.account_id where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d') and a.charge > 0 group by project_id, project_name ) sum on channle.project_id = sum.project_id left join( select project_id, project_name, count(distinct a.signature) as outer_num, sum(charge) as out_charge, count(distinct c.signature) as first_cost_num, sum(if(c.signature is not null, charge, 0)) as first_cost_charge from application.kuaishou_material_video_report_daily_dw a inner join ( select account_id from `jeecg-boot`.ctop_user_allocation where user_id in ( select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') ) ) b on a.account_id = b.account_id left join(select signature from application.material_farst_cost_time where frast_cost_time between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')) c on a.signature = c.signature where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d') and a.charge > 0 and (channel_type = 1 or channel_name = '素造') group by channel_name, project_id, project_name ) outc on channle.project_id = outc.project_id left join (select project_id, project_name, sum(charge) as in_charge from application.kuaishou_material_video_report_daily_dw a inner join ( select account_id from `jeecg-boot`.ctop_user_allocation where user_id in ( select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') ) ) b on a.account_id = b.account_id where a.stat_date between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and clip_company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b', '1648c7ceb4d449a9a13fbed7a8d9976c') and channel_name = '内部' group by channel_name, project_id, project_name) channle_yes on channle.project_id = channle_yes.project_id left join( select project_id, project_name, sum(charge) as sum_charge from application.kuaishou_material_video_report_daily_dw a inner join ( select account_id from `jeecg-boot`.ctop_user_allocation where user_id in ( select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') ) ) b on a.account_id = b.account_id where a.stat_date between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') group by project_id, project_name ) yes_sum on channle.project_id = yes_sum.project_id group by channle.project_name order by `总消耗` desc --汇总 select concat(:date,'-',:enddate) as `日期`, '汇总' as `项目名称`, round(ifnull(sum(channle.in_charge), 0), 2) as `内部素材消耗`, ifnull(sum(inner_num), 0) as `内部消耗素材数`, round(ifnull(sum(channle.in_charge) / sum(inner_num), 0), 2) as `内部素材平均消耗`, ifnull(sum(inner_first_cost_num), 0) as `内部昨日素材上新数`, round(ifnull(sum(inner_first_cost_charge) / sum(inner_first_cost_num), 0), 2) as `内部昨日素材平均消耗`, round(ifnull(sum(out_charge), 0), 2) as `外部素材消耗`, ifnull(sum(outer_num), 0) as `外部消耗素材数`, round(ifnull(sum(out_charge) / sum(outer_num), 0), 2) as `外部素材平均消耗`, sum(first_cost_num) as `外部昨日素材上新数`, round(ifnull(sum(first_cost_charge) / sum(first_cost_num), 0), 2) as `外部昨日素材平均消耗`, round(ifnull(sum(sum.sum_charge), 0), 2) as 总消耗, concat(round(ifnull(sum(channle.in_charge) / sum(sum.sum_charge) * 100, 0), 2), '%') as `内部渗透率`, concat(round(ifnull((sum(channle.in_charge) / sum(sum.sum_charge) - sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) / (sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) * 100, 0), 2), '%') as `环比` from (select project_id, project_name, count(distinct a.signature) as inner_num, sum(charge) as in_charge, count(distinct c.signature) as inner_first_cost_num, sum(if(c.signature is not null, charge, 0)) as inner_first_cost_charge from application.kuaishou_material_video_report_daily_dw a inner join ( select account_id from `jeecg-boot`.ctop_user_allocation where user_id in ( select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') ) ) b on a.account_id = b.account_id left join (select signature from application.material_farst_cost_time where frast_cost_time between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')) c on a.signature = c.signature where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d') and clip_company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b', '1648c7ceb4d449a9a13fbed7a8d9976c') and channel_name = '内部' and a.charge > 0 group by channel_name, project_id, project_name) channle left join( select project_id, project_name, sum(charge) as sum_charge from application.kuaishou_material_video_report_daily_dw a inner join ( select account_id from `jeecg-boot`.ctop_user_allocation where user_id in ( select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') ) ) b on a.account_id = b.account_id where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d') and a.charge > 0 group by project_id, project_name ) sum on channle.project_id = sum.project_id left join( select project_id, project_name, count(distinct a.signature) as outer_num, sum(charge) as out_charge, count(distinct c.signature) as first_cost_num, sum(if(c.signature is not null, charge, 0)) as first_cost_charge from application.kuaishou_material_video_report_daily_dw a inner join ( select account_id from `jeecg-boot`.ctop_user_allocation where user_id in ( select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') ) ) b on a.account_id = b.account_id left join(select signature from application.material_farst_cost_time where frast_cost_time between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')) c on a.signature = c.signature where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d') and a.charge > 0 and (channel_type = 1 or channel_name = '素造') group by channel_name, project_id, project_name ) outc on channle.project_id = outc.project_id left join (select project_id, project_name, sum(charge) as in_charge from application.kuaishou_material_video_report_daily_dw a inner join ( select account_id from `jeecg-boot`.ctop_user_allocation where user_id in ( select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') ) ) b on a.account_id = b.account_id where a.stat_date between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and clip_company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b', '1648c7ceb4d449a9a13fbed7a8d9976c') and channel_name = '内部' group by channel_name, project_id, project_name) channle_yes on channle.project_id = channle_yes.project_id left join( select project_id, project_name, sum(charge) as sum_charge from application.kuaishou_material_video_report_daily_dw a inner join ( select account_id from `jeecg-boot`.ctop_user_allocation where user_id in ( select user_id from `jeecg-boot`.user_company where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b') ) ) b on a.account_id = b.account_id where a.stat_date between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') group by project_id, project_name ) yes_sum on channle.project_id = yes_sum.project_id