| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101 | 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 chargefrom `application`.kuaishou_material_video_report_daily_dw wherestat_date >=date_format(date_sub(${stat_date}, INTERVAL 60 Day), '%Y%m%d')group by account_id, signature, photo_id)ainner join(select account_id from `jeecg-boot`.ctop_ai_kuaishou_advertiser_strategy)bon a.account_id=b.account_id)aleft join(select account_id,photo_id,photo_name,channel_type,material_type from `jeecg-boot`.ctop_kuaishou_video_get )con 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 cfrom (         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     ) tgroup by account_id, photo_id, signature, charge,photo_nameorder by  c desc )twhere t.c<20 ;
 |