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