create table `jeecg-boot`.ctop_material_top_cost
(
    account_id    bigint(20),
    md5           varchar(100),
    photo_id      bigint(20),
    material_name varchar(255),
    7day_cost     decimal(19, 3),
    primary key (account_id, md5, photo_id)
) partition by hash ( account_id )
    partitions 8;

create table `jeecg-boot`.ctop_history_material_catch
(
    account_id    bigint(20),
    md5           varchar(100),
    photo_id      bigint(20),
    material_name varchar(255),
    sum_cost      decimal(19, 3),
    creative_num int(10),
    primary key (account_id, md5, photo_id)
) partition by hash ( account_id )
    partitions 8;


truncate table `jeecg-boot`.ctop_material_top_cost;
insert into `jeecg-boot`.ctop_material_top_cost(account_id, md5, photo_id, material_name,7day_cost )
select a.account_id,signature,a.photo_id,c.photo_name,a.charge from
(select a.* from
(select account_id,signature,photo_id,sum(charge) as charge
from `application`.kuaishou_material_video_report_daily_dw where
stat_date >=date_format(date_sub(${stat_date}, INTERVAL 60 Day), '%Y%m%d')
group by account_id, signature, photo_id)a
inner join(
select account_id from `jeecg-boot`.ctop_ai_kuaishou_advertiser_strategy
)b
on a.account_id=b.account_id)a
left join
(select account_id,photo_id,photo_name,channel_type,material_type from `jeecg-boot`.ctop_kuaishou_video_get )c
on a.account_id=c.account_id and a.photo_id=c.photo_id;


truncate table `jeecg-boot`.ctop_history_material_catch;
insert into `jeecg-boot`.ctop_history_material_catch(account_id, md5, photo_id, material_name, sum_cost,creative_num)
select account_id,signature,photo_id,photo_name,charge,c from (
select account_id, photo_id, signature,photo_name, charge, count(1) as c
from (
         select q.account_id, q.photo_id, q.signature, q.charge, creative_id,q.photo_name
         from (select m.account_id,
                      m.photo_id,
                      m.signature,
                      m.charge,
                      m.photo_name
               from (select t.*, s.charge
                     from (
                              select account_id, signature, photo_name, photo_id
                              from `jeecg-boot`.ctop_kuaishou_video_get
                              where stat_date >= date_sub(date_format(${stat_date},'%Y-%m-%d'), INTERVAL 60 Day)
                          ) t
                              inner join
                          (
                              select account_id, photo_id, signature, sum(charge) as charge
                              from `application`.kuaishou_material_video_report_daily_dw
                              where stat_date >=
                                    date_format(date_sub(${stat_date}, INTERVAL 60 Day), '%Y%m%d')
                              group by account_id, photo_id, signature) s
                          on t.account_id = s.account_id and t.photo_id = s.photo_id) m
               where
                 m.charge < 500
               limit 100) q
                  left join
              (
                  select account_id, creative_id, photo_id
                  from `jeecg-boot`.ctop_kuaishou_creative
                  where
                     creative_create_time >= date_sub(date_format(${stat_date},'%Y-%m-%d'), INTERVAL 60 Day)
                    and photo_id != 0
              ) w
              on q.account_id = w.account_id and q.photo_id = w.photo_id
     ) t
group by account_id, photo_id, signature, charge,photo_name
order by  c desc )t
where t.c<20 ;



















