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_rate
- from (
- 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_rate
- from (
- 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
|