123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287 |
- --明细
- select concat(:date,'-',:enddate) as `日期`,
- channle.project_name as `项目名称`,
- round(ifnull(sum(channle.in_charge), 0), 2) as `内部素材消耗`,
- ifnull(sum(inner_num), 0) as `内部消耗素材数`,
- round(ifnull(sum(channle.in_charge) / sum(inner_num), 0), 2) as `内部素材平均消耗`,
- ifnull(sum(inner_first_cost_num), 0) as `内部昨日素材上新数`,
- round(ifnull(sum(inner_first_cost_charge) / sum(inner_first_cost_num), 0), 2) as `内部昨日素材平均消耗`,
- round(ifnull(sum(out_charge), 0), 2) as `外部素材消耗`,
- ifnull(sum(outer_num), 0) as `外部消耗素材数`,
- round(ifnull(sum(out_charge) / sum(outer_num), 0), 2) as `外部素材平均消耗`,
- sum(first_cost_num) as `外部昨日素材上新数`,
- round(ifnull(sum(first_cost_charge) / sum(first_cost_num), 0), 2) as `外部昨日素材平均消耗`,
- round(ifnull(sum(sum.sum_charge), 0), 2) as `总消耗`,
- concat(round(ifnull(sum(channle.in_charge) / sum(sum.sum_charge) * 100, 0), 2), '%') as `内部渗透率`,
- concat(round(ifnull((sum(channle.in_charge) / sum(sum.sum_charge) -
- sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) /
- (sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) * 100, 0), 2), '%') as `环比`
- from (select project_id,
- project_name,
- count(distinct a.signature) as inner_num,
- sum(charge) as in_charge,
- count(distinct c.signature) as inner_first_cost_num,
- sum(if(c.signature is not null, charge, 0)) as inner_first_cost_charge
- from application.kuaishou_material_video_report_daily_dw a
- inner join (
- select account_id
- from `jeecg-boot`.ctop_user_allocation
- where user_id in (
- select user_id
- from `jeecg-boot`.user_company
- where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
- )
- ) b
- on a.account_id = b.account_id
- left join (select signature
- from application.material_farst_cost_time
- where frast_cost_time = date_format(:date, '%Y%m%d')) c
- on a.signature = c.signature
- where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
- and clip_company_id in
- ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b',
- '1648c7ceb4d449a9a13fbed7a8d9976c')
- and channel_name = '内部'
- and a.charge > 0
- group by channel_name, project_id, project_name) channle
- left join(
- select project_id,
- project_name,
- sum(charge) as sum_charge
- from application.kuaishou_material_video_report_daily_dw a
- inner join (
- select account_id
- from `jeecg-boot`.ctop_user_allocation
- where user_id in (
- select user_id
- from `jeecg-boot`.user_company
- where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
- )
- ) b
- on a.account_id = b.account_id
- where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
- and a.charge > 0
- group by project_id, project_name
- ) sum
- on channle.project_id = sum.project_id
- left join(
- select project_id,
- project_name,
- count(distinct a.signature) as outer_num,
- sum(charge) as out_charge,
- count(distinct c.signature) as first_cost_num,
- sum(if(c.signature is not null, charge, 0)) as first_cost_charge
- from application.kuaishou_material_video_report_daily_dw a
- inner join (
- select account_id
- from `jeecg-boot`.ctop_user_allocation
- where user_id in (
- select user_id
- from `jeecg-boot`.user_company
- where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
- )
- ) b
- on a.account_id = b.account_id
- left join(select signature
- from application.material_farst_cost_time
- where frast_cost_time between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')) c
- on a.signature = c.signature
- where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
- and a.charge > 0
- and (channel_type = 1 or channel_name = '素造')
- group by channel_name, project_id, project_name
- ) outc
- on channle.project_id = outc.project_id
- left join (select project_id,
- project_name,
- sum(charge) as in_charge
- from application.kuaishou_material_video_report_daily_dw a
- inner join (
- select account_id
- from `jeecg-boot`.ctop_user_allocation
- where user_id in (
- select user_id
- from `jeecg-boot`.user_company
- where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
- )
- ) b
- on a.account_id = b.account_id
- where a.stat_date between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d')
- and clip_company_id in
- ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b',
- '1648c7ceb4d449a9a13fbed7a8d9976c')
- and channel_name = '内部'
- group by channel_name, project_id, project_name) channle_yes
- on channle.project_id = channle_yes.project_id
- left join(
- select project_id,
- project_name,
- sum(charge) as sum_charge
- from application.kuaishou_material_video_report_daily_dw a
- inner join (
- select account_id
- from `jeecg-boot`.ctop_user_allocation
- where user_id in (
- select user_id
- from `jeecg-boot`.user_company
- where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
- )
- ) b
- on a.account_id = b.account_id
- where a.stat_date between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d')
- group by project_id, project_name
- ) yes_sum
- on channle.project_id = yes_sum.project_id
- group by channle.project_name
- order by `总消耗` desc
- --汇总
- select concat(:date,'-',:enddate) as `日期`,
- '汇总' as `项目名称`,
- round(ifnull(sum(channle.in_charge), 0), 2) as `内部素材消耗`,
- ifnull(sum(inner_num), 0) as `内部消耗素材数`,
- round(ifnull(sum(channle.in_charge) / sum(inner_num), 0), 2) as `内部素材平均消耗`,
- ifnull(sum(inner_first_cost_num), 0) as `内部昨日素材上新数`,
- round(ifnull(sum(inner_first_cost_charge) / sum(inner_first_cost_num), 0), 2) as `内部昨日素材平均消耗`,
- round(ifnull(sum(out_charge), 0), 2) as `外部素材消耗`,
- ifnull(sum(outer_num), 0) as `外部消耗素材数`,
- round(ifnull(sum(out_charge) / sum(outer_num), 0), 2) as `外部素材平均消耗`,
- sum(first_cost_num) as `外部昨日素材上新数`,
- round(ifnull(sum(first_cost_charge) / sum(first_cost_num), 0), 2) as `外部昨日素材平均消耗`,
- round(ifnull(sum(sum.sum_charge), 0), 2) as 总消耗,
- concat(round(ifnull(sum(channle.in_charge) / sum(sum.sum_charge) * 100, 0), 2), '%') as `内部渗透率`,
- concat(round(ifnull((sum(channle.in_charge) / sum(sum.sum_charge) -
- sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) /
- (sum(channle_yes.in_charge) / sum(yes_sum.sum_charge)) * 100, 0), 2), '%') as `环比`
- from (select project_id,
- project_name,
- count(distinct a.signature) as inner_num,
- sum(charge) as in_charge,
- count(distinct c.signature) as inner_first_cost_num,
- sum(if(c.signature is not null, charge, 0)) as inner_first_cost_charge
- from application.kuaishou_material_video_report_daily_dw a
- inner join (
- select account_id
- from `jeecg-boot`.ctop_user_allocation
- where user_id in (
- select user_id
- from `jeecg-boot`.user_company
- where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
- )
- ) b
- on a.account_id = b.account_id
- left join (select signature
- from application.material_farst_cost_time
- where frast_cost_time between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')) c
- on a.signature = c.signature
- where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
- and clip_company_id in
- ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b',
- '1648c7ceb4d449a9a13fbed7a8d9976c')
- and channel_name = '内部'
- and a.charge > 0
- group by channel_name, project_id, project_name) channle
- left join(
- select project_id,
- project_name,
- sum(charge) as sum_charge
- from application.kuaishou_material_video_report_daily_dw a
- inner join (
- select account_id
- from `jeecg-boot`.ctop_user_allocation
- where user_id in (
- select user_id
- from `jeecg-boot`.user_company
- where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
- )
- ) b
- on a.account_id = b.account_id
- where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
- and a.charge > 0
- group by project_id, project_name
- ) sum
- on channle.project_id = sum.project_id
- left join(
- select project_id,
- project_name,
- count(distinct a.signature) as outer_num,
- sum(charge) as out_charge,
- count(distinct c.signature) as first_cost_num,
- sum(if(c.signature is not null, charge, 0)) as first_cost_charge
- from application.kuaishou_material_video_report_daily_dw a
- inner join (
- select account_id
- from `jeecg-boot`.ctop_user_allocation
- where user_id in (
- select user_id
- from `jeecg-boot`.user_company
- where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
- )
- ) b
- on a.account_id = b.account_id
- left join(select signature
- from application.material_farst_cost_time
- where frast_cost_time between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')) c
- on a.signature = c.signature
- where a.stat_date between date_format(:date, '%Y%m%d') and date_format(:enddate, '%Y%m%d')
- and a.charge > 0
- and (channel_type = 1 or channel_name = '素造')
- group by channel_name, project_id, project_name
- ) outc
- on channle.project_id = outc.project_id
- left join (select project_id,
- project_name,
- sum(charge) as in_charge
- from application.kuaishou_material_video_report_daily_dw a
- inner join (
- select account_id
- from `jeecg-boot`.ctop_user_allocation
- where user_id in (
- select user_id
- from `jeecg-boot`.user_company
- where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
- )
- ) b
- on a.account_id = b.account_id
- where a.stat_date between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d')
- and clip_company_id in
- ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b',
- '1648c7ceb4d449a9a13fbed7a8d9976c')
- and channel_name = '内部'
- group by channel_name, project_id, project_name) channle_yes
- on channle.project_id = channle_yes.project_id
- left join(
- select project_id,
- project_name,
- sum(charge) as sum_charge
- from application.kuaishou_material_video_report_daily_dw a
- inner join (
- select account_id
- from `jeecg-boot`.ctop_user_allocation
- where user_id in (
- select user_id
- from `jeecg-boot`.user_company
- where company_id in ('d57fecdcf7a94d009736d9c850731582', '5e46b5dea3d740eeb8ff1a2895015a4b')
- )
- ) b
- on a.account_id = b.account_id
- where a.stat_date between date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d') and date_format(date_sub(:date, interval datediff(:enddate,:date)+1 day), '%Y%m%d')
- group by project_id, project_name
- ) yes_sum
- on channle.project_id = yes_sum.project_id
|