#1.有效素材 #创建表 CREATE TABLE `effect_material` ( `media_id` int(11) NOT NULL, `signature` varchar(50) NOT NULL, `effect_date` bigint(20) DEFAULT NULL, PRIMARY KEY (`media_id`,`signature`) /*T![clustered_index] NONCLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='有效素材' #插入数据 #快手 REPLACE INTO application.effect_material select new.media_id,new.signature,new.effect_date from (select 2 media_id,signature,min(stat_date) effect_date from ( select stat_date,signature,sum(charge) over (partition by signature order by stat_date) as sum from ( select m.stat_date,n.signature,sum(m.charge) charge from (select stat_date,advertiser_id account_id,photo_id,charge from `media_api`.kuaishou_material_video_report_daily where stat_date between date_sub(${stat_date}, INTERVAL 6 DAY) and ${stat_date} group by stat_date,advertiser_id,photo_id)m INNER JOIN (select b.account_id,b.photo_id,a.signature from (select distinct signature from application.material_farst_cost_time where frast_cost_time>=date_sub(${stat_date}, INTERVAL 6 DAY)) a inner join (select account_id,photo_id,signature from `media_api`.kuaishou_video_list where date_format(stat_date,'%Y%m%d')>= date_format(date_sub(${stat_date}, INTERVAL 13 DAY),'%Y%m%d') group by account_id,photo_id,signature)b on a.signature=b.signature)n on m.account_id=n.account_id and m.photo_id=n.photo_id group by m.stat_date,n.signature)m) info where sum>=5000 group by signature) new left join application.effect_material eff on new.signature=eff.signature where eff.signature is null union all ##头条 select new.media_id,new.signature,new.effect_date from (select 1 media_id,signature,min(stat_date) effect_date from ( select stat_date,signature,sum(cost) over (partition by signature order by stat_date) as sum from ( select m.stat_date,n.signature,sum(m.cost) cost from (select stat_datetime stat_date ,advertiser_id account_id,material_id,sum(cost) cost from media_api.bytedance_material_report_daily WHERE image_mode IN ( 'CREATIVE_IMAGE_MODE_VIDEO', 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL', 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO') and stat_datetime between date_sub(${stat_date}, INTERVAL 6 DAY) and ${stat_date} group by stat_datetime,advertiser_id,material_id)m INNER JOIN (select b.account_id,b.material_id,a.signature from (select distinct signature from application.bytedance_material_farst_cost_time where frast_cost_time>=date_sub(${stat_date}, INTERVAL 6 DAY)) a inner join (select advertiser_id account_id,signature,material_id,filename,video_url from `media_api`.bytedance_file_video_get where date_format(create_time,'%Y%m%d')>= date_format(date_sub(${stat_date}, INTERVAL 13 DAY),'%Y%m%d') group by advertiser_id,signature,material_id,filename,video_url)b on a.signature=b.signature)n on m.account_id=n.account_id and m.material_id=n.material_id group by m.stat_date,n.signature)m) info where sum>=5000 group by signature) new left join application.effect_material eff on new.signature=eff.signature where eff.signature is null; #2.爆款素材 #创建表 CREATE TABLE `hot_material` ( `media_id` int(11) NOT NULL, `signature` varchar(50) NOT NULL, `hot_date` bigint(20) DEFAULT NULL, PRIMARY KEY (`media_id`,`signature`) /*T![clustered_index] NONCLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='爆款素材'; #插入数据 #快手 REPLACE INTO application.hot_material select new.media_id,new.signature,new.hot_date from (select 2 media_id,signature,min(stat_date) hot_date from ( select stat_date,signature,sum(charge) over (partition by signature order by stat_date) as sum from ( select m.stat_date,n.signature,sum(m.charge) charge from (select stat_date,advertiser_id account_id,photo_id,charge from `media_api`.kuaishou_material_video_report_daily where stat_date between date_sub(${stat_date}, INTERVAL 13 DAY) and ${stat_date} group by stat_date,advertiser_id,photo_id)m INNER JOIN (select b.account_id,b.photo_id,a.signature from (select distinct signature from application.material_farst_cost_time where frast_cost_time>=date_sub(${stat_date}, INTERVAL 13 DAY)) a inner join (select account_id,photo_id,signature from `media_api`.kuaishou_video_list where date_format(stat_date,'%Y%m%d')>= date_format(date_sub(${stat_date}, INTERVAL 20 DAY),'%Y%m%d') group by account_id,photo_id,signature)b on a.signature=b.signature)n on m.account_id=n.account_id and m.photo_id=n.photo_id group by m.stat_date,n.signature)m) info where sum>=50000 group by signature) new left join application.effect_material eff on new.signature=eff.signature where eff.signature is null union all ##头条 select new.media_id,new.signature,new.hot_date from (select 1 media_id,signature,min(stat_date) hot_date from ( select stat_date,signature,sum(cost) over (partition by signature order by stat_date) as sum from ( select m.stat_date,n.signature,sum(m.cost) cost from (select stat_datetime stat_date ,advertiser_id account_id,material_id,sum(cost) cost from media_api.bytedance_material_report_daily WHERE image_mode IN ( 'CREATIVE_IMAGE_MODE_VIDEO', 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL', 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO') and stat_datetime between date_sub(${stat_date}, INTERVAL 13 DAY) and ${stat_date} group by stat_datetime,advertiser_id,material_id)m INNER JOIN (select b.account_id,b.material_id,a.signature from (select distinct signature from application.bytedance_material_farst_cost_time where frast_cost_time>=date_sub(${stat_date}, INTERVAL 13 DAY)) a inner join (select advertiser_id account_id,signature,material_id,filename,video_url from `media_api`.bytedance_file_video_get where date_format(create_time,'%Y%m%d')>= date_format(date_sub(${stat_date}, INTERVAL 20 DAY),'%Y%m%d') group by advertiser_id,signature,material_id,filename,video_url)b on a.signature=b.signature)n on m.account_id=n.account_id and m.material_id=n.material_id group by m.stat_date,n.signature)m) info where sum>=50000 group by signature) new left join application.effect_material eff on new.signature=eff.signature where eff.signature is null;