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 ;