| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341 | 
							- 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
 
 
  |