| 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_costfrom (    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_idleft join `jeecg-boot`.sys_user useron 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_numfrom (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_ratefrom (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
 |