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 clip_cost desc ),0) as rn,
       clip_name,
       clip_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
from (
         select clip_name,
                sum(clip_cost) as clip_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.clip_id,
                         case
                             when info.clip_name like '%汇创%' then replace(clip_name, '（汇创）', '')
                             else clip_name end as clip_name,
                         info.clip_cost,
                         info.sumcost,
                         hot_num,
                         hot_cost,
                         material_num,
                         man_num,
                         hotmancount,
                         plan_num,
                         hotplancount
                  from (select clip_id, clip_name, clip_cost, sumcost
                        from (select clip_id, clip_name, sum(cost) as clip_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 clip_id is not null
                              group by clip_id, clip_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 clip_id is not null) b) info


                           left join (
                      select clip_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 clip_id in (select user_id
                                          from `jeecg-boot`.user_company
                                          where company_id in
                                                ('d57fecdcf7a94d009736d9c850731582',
                                                 '5e46b5dea3d740eeb8ff1a2895015a4b'))
                      group by clip_id) b
                                     on info.clip_id = b.clip_id
                           left join(
                      select clip_id, sum(hot_num) as hot_num, sum(hot_cost) as hot_cost
                      from (
                               select b.clip_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, clip_id
                                     from `jeecg-boot`.ctop_material_ascription
                                     where clip_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 clip_id, a.signature) t
                      group by clip_id) c
                                    on info.clip_id = c.clip_id
                           left join (
                      select clip_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 clip_id, parent_id) d
                                     on info.clip_id = d.clip_id
                           left join
                       (select clip_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 clip_id, parent_id) e
                       on info.clip_id = e.clip_id

                           left join
                       (select clip_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 clip_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 clip_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 clip_id) f
                       on info.clip_id = f.clip_id
                           left join
                       (select clip_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 clip_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 clip_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 clip_id) i
                       on info.clip_id = i.clip_id) t
         group by clip_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,
      '汇总' clip_name,
       sum(clip_cost) as clip_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
from (
         select clip_name,
                sum(clip_cost) as clip_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.clip_id,
                         case
                             when info.clip_name like '%汇创%' then replace(clip_name, '（汇创）', '')
                             else clip_name end as clip_name,
                         info.clip_cost,
                         info.sumcost,
                         hot_num,
                         hot_cost,
                         material_num,
                         man_num,
                         hotmancount,
                         plan_num,
                         hotplancount
                  from (select clip_id, clip_name, clip_cost, sumcost
                        from (select clip_id, clip_name, sum(cost) as clip_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 clip_id is not null
                              group by clip_id, clip_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 clip_id is not null) b) info


                           left join (
                      select clip_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 clip_id in (select user_id
                                          from `jeecg-boot`.user_company
                                          where company_id in
                                                ('d57fecdcf7a94d009736d9c850731582',
                                                 '5e46b5dea3d740eeb8ff1a2895015a4b'))
                      group by clip_id) b
                                     on info.clip_id = b.clip_id
                           left join(
                      select clip_id, sum(hot_num) as hot_num, sum(hot_cost) as hot_cost
                      from (
                               select b.clip_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, clip_id
                                     from `jeecg-boot`.ctop_material_ascription
                                     where clip_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 clip_id, a.signature) t
                      group by clip_id) c
                                    on info.clip_id = c.clip_id
                           left join (
                      select clip_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 clip_id, parent_id) d
                                     on info.clip_id = d.clip_id
                           left join
                       (select clip_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 clip_id, parent_id) e
                       on info.clip_id = e.clip_id

                           left join
                       (select clip_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 clip_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 clip_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 clip_id) f
                       on info.clip_id = f.clip_id
                           left join
                       (select clip_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 clip_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 clip_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 clip_id) i
                       on info.clip_id = i.clip_id) t
         group by clip_name, sumcost) t