| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344 | 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_ratefrom (         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_ratefrom (         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
 |