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