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