-- 运营 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