-- 明细
select

       round(row_number() over (order by leadercost desc ),0) as rn,
       leader_name,
       leadercost,
       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(material_num,0)  as  script_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,
       ifnull(plan_num,0) as script_plan_num,
       ifnull(hotplancount,0) as  script_hotplancount,
       ifnull(man_num,0) as script_man_num,
       ifnull(hotmancount,0) as script_hotmancount,
      concat(round(ifnull(leadercost/sumcost*100,0),2),'%') as cost_rate,
       1 as sort
from (
         select leader_name,
                sum(leadercost) as leadercost,
                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.leader_id,
                         case
                             when info.leader_name like '%汇创%' then replace(leader_name, '（汇创）', '')
                             else leader_name end as leader_name,
                         info.leadercost,
                         info.sumcost,
                         hot_num,
                         hot_cost,
                         material_num,
                         man_num,
                         hotmancount,
                         plan_num,
                         hotplancount
                  from (select leader_id, leader_name, leadercost, sumcost
                        from (select leader_id, leader_name, sum(cost) as leadercost
                              from application.bytedance_material_video_report_daily_dw
                              where stat_datetime between date_format(date_sub('2022-05-17', interval 6 day), '%Y%m%d') and date_format('2022-05-17', '%Y%m%d')
                                and clip_company_id in
                                    ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
                                and leader_id is not null
                              group by leader_id, leader_name) a
                                 join (select sum(cost) as sumcost
                                       from application.bytedance_material_video_report_daily_dw
                                       where stat_datetime between date_format(date_sub('2022-05-17', interval 6 day), '%Y%m%d') and date_format('2022-05-17', '%Y%m%d')
                                         and clip_company_id in
                                             ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
                                         and leader_id is not null) b) info


                           left join (
                      select leader_id, count(1) as material_num
                      from `jeecg-boot`.ctop_material_ascription
                      where create_time between date_format(date_sub('2022-05-17', interval 6 day), '%Y-%m-%d')
                          and date_format(date_sub('2022-05-17', interval -1 day), '%Y-%m-%d')
                        and leader_id in (select user_id
                                          from `jeecg-boot`.user_company
                                          where company_id in
                                                ('d57fecdcf7a94d009736d9c850731582',
                                                 '5e46b5dea3d740eeb8ff1a2895015a4b'))
                      group by leader_id) b
                                     on info.leader_id = b.leader_id
                           left join(
                      select leader_id, sum(hot_num) as hot_num, sum(hot_cost) as hot_cost
                      from (
                               select b.leader_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('2022-05-17', interval 6 day), '%Y%m%d') and date_format('2022-05-17', '%Y%m%d')) a
                                        inner join
                                    (select material_id, leader_id
                                     from `jeecg-boot`.ctop_material_ascription
                                     where leader_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 leader_id, a.signature) t
                      group by leader_id) c
                                    on info.leader_id = c.leader_id
                           left join (
                      select leader_id, parent_id, count(distinct material_id) as man_num
                      from application.app_material_tag_busi_info
                      where create_date between date_format(date_sub('2022-05-17', interval 6 day), '%Y%m%d')
                          and date_format(date_sub('2022-05-17', interval -1 day), '%Y%m%d')
                        and tag_category_id = 1
                        and parent_id = 60155
                      group by leader_id, parent_id) d
                                     on info.leader_id = d.leader_id
                           left join
                       (select leader_id, parent_id, count(distinct material_id)  as plan_num
                        from application.app_material_tag_busi_info
                        where create_date between date_format(date_sub('2022-05-17', interval 6 day), '%Y%m%d')
                            and date_format(date_sub('2022-05-17', interval -1 day), '%Y%m%d')
                          and tag_category_id = 1
                          and parent_id = 60156
                        group by leader_id, parent_id) e
                       on info.leader_id = e.leader_id

                           left join
                       (select leader_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('2022-05-17', interval 6 day), '%Y%m%d') and date_format('2022-05-17', '%Y%m%d')) a
                                 inner join
                             (select leader_id, material_id
                              from application.app_material_tag_busi_info
                              where tag_category_id = 1
                                and parent_id = 60155
                                and leader_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 leader_id) f
                       on info.leader_id = f.leader_id
                           left join
                       (select leader_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('2022-05-17', interval 6 day), '%Y%m%d') and date_format('2022-05-17', '%Y%m%d')) a
                                 inner join
                             (select leader_id, material_id
                              from application.app_material_tag_busi_info
                              where tag_category_id = 1
                                and parent_id = 60156
                                and leader_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 leader_id) i
                       on info.leader_id = i.leader_id) t
         group by leader_name, sumcost) t;

-- 汇总

select
       '' as rn,
       '汇总' leader_name,
       sum(leadercost) as leadercost,
       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(material_num),0)  as  script_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,
       ifnull(sum(plan_num),0) as script_plan_num,
       ifnull(sum(hotplancount),0) as  script_hotplancount,
       ifnull(sum(man_num),0) as script_man_num,
       ifnull(sum(hotmancount),0) as script_hotmancount,
       '' as cost_rate
from (
         select leader_name,
                sum(leadercost) as leadercost,
                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.leader_id,
                         case
                             when info.leader_name like '%汇创%' then replace(leader_name, '（汇创）', '')
                             else leader_name end as leader_name,
                         info.leadercost,
                         hot_num,
                         hot_cost,
                         material_num,
                         man_num,
                         hotmancount,
                         plan_num,
                         hotplancount
                  from (select leader_id, leader_name, leadercost
                        from (select leader_id, leader_name, sum(cost) as leadercost
                              from application.bytedance_material_video_report_daily_dw
                              where stat_datetime between date_format(date_sub('2022-05-17', interval 6 day), '%Y%m%d') and date_format('2022-05-17', '%Y%m%d')
                                and clip_company_id in
                                    ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
                                and leader_id is not null
                              group by leader_id, leader_name) a) info


                           left join (
                      select leader_id, count(1) as material_num
                      from `jeecg-boot`.ctop_material_ascription
                      where create_time between date_format(date_sub('2022-05-17', interval 6 day), '%Y-%m-%d')
                          and date_format(date_sub('2022-05-17', interval -1 day), '%Y-%m-%d')
                        and leader_id in (select user_id
                                          from `jeecg-boot`.user_company
                                          where company_id in
                                                ('d57fecdcf7a94d009736d9c850731582',
                                                 '5e46b5dea3d740eeb8ff1a2895015a4b'))
                      group by leader_id) b
                                     on info.leader_id = b.leader_id
                           left join(
                      select leader_id, sum(hot_num) as hot_num, sum(hot_cost) as hot_cost
                      from (
                               select b.leader_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('2022-05-17', interval 6 day), '%Y%m%d') and date_format('2022-05-17', '%Y%m%d')) a
                                        inner join
                                    (select material_id, leader_id
                                     from `jeecg-boot`.ctop_material_ascription
                                     where leader_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 leader_id, a.signature) t
                      group by leader_id) c
                                    on info.leader_id = c.leader_id
                           left join (
                      select leader_id, parent_id, count(distinct material_id) as man_num
                      from application.app_material_tag_busi_info
                      where create_date between date_format(date_sub('2022-05-17', interval 6 day), '%Y%m%d')
                          and date_format(date_sub('2022-05-17', interval -1 day), '%Y%m%d')
                        and tag_category_id = 1
                        and parent_id = 60155
                      group by leader_id, parent_id) d
                                     on info.leader_id = d.leader_id
                           left join
                       (select leader_id, parent_id, count(distinct material_id)  as plan_num
                        from application.app_material_tag_busi_info
                        where create_date between date_format(date_sub('2022-05-17', interval 6 day), '%Y%m%d')
                            and date_format(date_sub('2022-05-17', interval -1 day), '%Y%m%d')
                          and tag_category_id = 1
                          and parent_id = 60156
                        group by leader_id, parent_id) e
                       on info.leader_id = e.leader_id

                           left join
                       (select leader_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('2022-05-17', interval 6 day), '%Y%m%d') and date_format('2022-05-17', '%Y%m%d')) a
                                 inner join
                             (select leader_id, material_id
                              from application.app_material_tag_busi_info
                              where tag_category_id = 1
                                and parent_id = 60155
                                and leader_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 leader_id) f
                       on info.leader_id = f.leader_id
                           left join
                       (select leader_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('2022-05-17', interval 6 day), '%Y%m%d') and date_format('2022-05-17', '%Y%m%d')) a
                                 inner join
                             (select leader_id, material_id
                              from application.app_material_tag_busi_info
                              where tag_category_id = 1
                                and parent_id = 60156
                                and leader_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 leader_id) i
                       on info.leader_id = i.leader_id) t
         group by leader_name) t
