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