123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176 |
- -- 运营
- select :date as date,
- realname as user_name,
- count as account_num,
- cost,
- concat(round((cost-yes_week_cost)/yes_week_cost*100,2),'%') as cost_rate,
- effect_num,
- new_campaign_num,
- new_ad_num,
- quarter_cost
- from (
- select user_id,count(1) as count,sum(cost) as cost from (
- select user_id, account_id, sum(cost) as cost
- from application.bytedance_advertiser_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 cost > 0
- and company_id='d57fecdcf7a94d009736d9c850731582'
- and user_id in(
- select id from `jeecg-boot`.sys_user where leader_id='c52551687c524a96a3904658a35f0f26'
- union all
- select id from `jeecg-boot`.sys_user where id='c52551687c524a96a3904658a35f0f26'
- )
- group by user_id,account_id)t
- group by user_id) a
- left join (
- select user_id,
- sum(cost) as yes_week_cost
- from application.bytedance_advertiser_report_daily_dw
- where stat_datetime between date_format(date_sub(:date, interval 13 day), '%Y%m%d') and date_format(date_sub(:date, interval 7 day), '%Y%m%d')
- and cost > 0
- group by user_id
- ) b
- on a.user_id = b.user_id
- left join (
- select user_id,
- sum(new_effect_num) as effect_num,
- sum(new_campaign_num) as new_campaign_num,
- sum(new_ad_num) as new_ad_num
- from application.app_bytedance_account_base_info_d
- where stat_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
- group by user_id
- ) c
- on a.user_id = c.user_id
- left join (
- select user_id,
- sum(cost) as quarter_cost
- from application.app_bytedance_advertiser_report_busi_m
- where stat_mon between case
- when quarter(date_sub(now(), interval 1 day)) = 1 then 202201
- when quarter(date_sub(now(), interval 1 day)) = 2 then 202204
- when quarter(date_sub(now(), interval 1 day)) = 3 then 202207
- when quarter(date_sub(now(), interval 1 day)) = 4 then 202210
- end
- and
- case
- when quarter(date_sub(now(), interval 1 day)) = 1 then 202203
- when quarter(date_sub(now(), interval 1 day)) = 2 then 202206
- when quarter(date_sub(now(), interval 1 day)) = 3 then 202209
- when quarter(date_sub(now(), interval 1 day)) = 4 then 202212
- end
- group by user_id
- ) d
- on a.user_id = d.user_id
- left join `jeecg-boot`.sys_user user
- on a.user_id=user.id
- -- 项目
- select :date as date,
- project_name,
- account_count,
- user_count,
- a.cost,
- concat(round(ifnull((a.cost - c.cost) / (c.cost) * 100, 2), 0), '%') as cost_rate,
- d.tb_rate,
- e.hot_num
- from (select project_id, project_name, count(1) as account_count, sum(cost) as cost
- from (
- select project_id, project_name, account_id, sum(cost) as cost
- from application.bytedance_advertiser_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 cost > 0
- and company_id = 'd57fecdcf7a94d009736d9c850731582'
- and user_id in(
- select id from `jeecg-boot`.sys_user where leader_id='c52551687c524a96a3904658a35f0f26'
- union all
- select id from `jeecg-boot`.sys_user where id='c52551687c524a96a3904658a35f0f26'
- )
- group by project_id, account_id) t
- group by project_id) a
- left join
- (select project_id, count(1) as user_count
- from (
- select project_id, user_id
- from `jeecg-boot`.ctop_user_allocation
- group by project_id, user_id) t
- group by project_id) b
- on a.project_id = b.project_id
- left join(
- select project_id, sum(cost) as cost
- from application.bytedance_advertiser_report_daily_dw
- where stat_datetime between date_format(date_sub(:date, interval 13 day), '%Y%m%d') and date_format(date_sub(:date, interval 7 day), '%Y%m%d')
- and cost > 0
- and company_id = 'd57fecdcf7a94d009736d9c850731582'
- group by project_id
- ) c
- on a.project_id = c.project_id
- left join (
- select a.project_id, concat(round((sum(a.cost) - sum(b.cost)) / sum(b.cost) * 100, 2), '%') as tb_rate
- from application.bytedance_advertiser_report_daily_dw a
- left join application.bytedance_advertiser_report_daily_dw b
- on a.project_id = b.project_id
- where a.stat_datetime = date_format(:date, '%Y%m%d')
- and b.stat_datetime = date_format(date_sub(:date, interval 6 day), '%Y%m%d')
- group by a.project_id,b.project_id
- ) d
- on a.project_id = d.project_id
- left join (
- select project_id, sum(new_hot_num) as hot_num
- from application.app_bytedance_account_base_info_d
- where stat_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
- group by project_id
- ) e
- on a.project_id = e.project_id
- -- 账户
- select :date as date,
- account_name,
- a.account_id,
- realname,
- cost,
- c.cost_rate,
- b.tb_rate
- from (select account_id, account_name, user_id, cost
- from application.bytedance_advertiser_report_daily_dw
- where stat_datetime = date_format(:date, '%Y%m%d')
- and cost > 0
- and company_id = 'd57fecdcf7a94d009736d9c850731582'
- and user_id in(
- select id from `jeecg-boot`.sys_user where leader_id='c52551687c524a96a3904658a35f0f26'
- union all
- select id from `jeecg-boot`.sys_user where id='c52551687c524a96a3904658a35f0f26'
- )) a
- left join (
- select a.account_id, concat(round((a.cost - b.cost) / b.cost * 100, 2), '%') as tb_rate
- from application.bytedance_advertiser_report_daily_dw a
- left join application.bytedance_advertiser_report_daily_dw b
- on a.account_id = b.account_id
- where a.stat_datetime = date_format(:date, '%Y%m%d')
- and b.stat_datetime = date_format(date_sub(:date, interval 6 day), '%Y%m%d')
- ) b
- on a.account_id = b.account_id
- left join(
- select t.account_id, concat(round((t.cost - t1.yes_week_cost) / t1.yes_week_cost * 100, 2), '%') as cost_rate
- from (select account_id, sum(cost) as cost
- from application.bytedance_advertiser_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 cost > 0
- group by account_id) t
- left join
- (select account_id, sum(cost) as yes_week_cost
- from application.bytedance_advertiser_report_daily_dw
- where stat_datetime between date_format(date_sub(:date, interval 13 day), '%Y%m%d') and date_format(date_sub(:date, interval 7 day), '%Y%m%d')
- and cost > 0
- group by account_id) t1
- on t.account_id = t1.account_id
- ) c
- on a.account_id = c.account_id
- left join `jeecg-boot`.sys_user user
- on a.user_id = user.id
|