123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260 |
- # Author renyupeng
- # coding=utf-8
- # @Time : 2021/11/12 10:20 上午
- # @Site :
- # @File : AppBytedanceFileVideoGetAll.py
- # @Software: PyCharm
- # @contact: renyupeng@c-top.com.cn
- # @Tel 1501435553
- import logging
- from Apietl.conn.TidbConn import TidbConn
- class AppBytedanceFileVideoGetAll:
- def __init__(self):
- self.conn = TidbConn.get_connection()
- def execSql(self):
- try:
- sql = """select advertiser_id from media_api.bytedance_file_video_get
- where advertiser_id in(1710388576343047)
- group by advertiser_id"""
- account_list = TidbConn.quary(self.conn, sql)
- for account_id in account_list:
- try:
- sql = """
- replace into application.app_bytedance_file_video_get
- select advertiser_id,
- id,
- size,
- width,
- height,
- url,
- format,
- case when v.signature is null then '' else v.signature end as signature,
- poster_url,
- bit_rate,
- duration,
- material_id,
- source,
- create_time,
- filename,
- labels,
- video_url,
- case when tag_category_id is null then '' else tag_category_id end as tag_category_id,
- tag_category_name,
- case when parent_id is null then '' else parent_id end as parent_id,
- parent_name,
- case when tag_code is null then '' else tag_code end as tag_code,
- tag_name,
- clip_id,
- clip_name,
- shot_id,
- shot_name,
- plan_id,
- plan_name,
- leader_id,
- leader_name,
- material_innovate,
- hot.stat_datetime,
- effect.stat_datetime
- from (
- select advertiser_id,
- id,
- size,
- width,
- height,
- url,
- format,
- signature,
- poster_url,
- bit_rate,
- duration,
- a.material_id,
- source,
- create_time,
- case when t.material_name is null then a.filename else t.material_name end as filename,
- labels,
- video_url,
- clip_id,
- clip_name,
- shot_id,
- shot_name,
- plan_id,
- plan_name,
- leader_id,
- leader_name,
- material_innovate
- from (select advertiser_id,
- id,
- size,
- width,
- height,
- url,
- format,
- signature,
- poster_url,
- bit_rate,
- duration,
- material_id,
- source,
- create_time,
- filename,
- labels,
- video_url
- from media_api.bytedance_file_video_get where advertiser_id={account_id}
- ) a
- left join
- (select material_id,
- clip_id,
- clip_name,
- shot_id,
- shot_name,
- plan_id,
- plan_name,
- leader_id,
- leader_name,
- material_name,
- material_innovate
- from (select cma.material_id,
- clip_id,
- clip.realname as clip_name,
- shot_id,
- shot.realname as shot_name,
- plan_id,
- plan.realname as plan_name,
- cma.leader_id,
- leader.realname as leader_name,
- material_name,
- material_innovate
- from `jeecg-boot`.ctop_material_ascription cma
- left join
- `jeecg-boot`.ctop_material_info b
- on cma.material_id = b.id
- left join `jeecg-boot`.sys_user clip on clip.id = cma.clip_id
- left join `jeecg-boot`.sys_user shot on shot.id = cma.shot_id
- left join `jeecg-boot`.sys_user plan on plan.id = cma.plan_id
- left join `jeecg-boot`.sys_user leader on leader.id = cma.leader_id
- ) v) t
- on a.signature = t.material_id) v
- left join
- (select code,
- tag_category_id,
- tag_category_name,
- parent_id,
- parent_name,
- group_concat(tag_code order by tag_code separator '-') tag_code,
- group_concat(tag_name order by tag_code separator '-') tag_name
- from (select a.code, b.tag_category_id, b.tag_category_name, b.parent_id, b.parent_name, b.tag_code, b.tag_name
- from `jeecg-boot`.ctop_material_tag_info a
- left join
- (select if(tag_category_id is null,'',tag_category_id) as tag_category_id,
- tag_category_name,
- case
- when parent_id is not null then parent_id
- when tag_category_id = 2 then ''
- else tag_code end parent_id,
- case
- when parent_id = 60155 then '真人'
- when parent_id = 60156 then '制作'
- when parent_id = 60160 then '外拍场景'
- when parent_id = 60161 then '公司内景'
- when tag_category_id = 2 then ''
- else '其他' end parent_name,
- tag_code,
- tag_name
- from `jeecg-boot`.ctop_tag_info) b
- on a.tag_code = b.tag_code) c
- group by code, tag_category_id, tag_category_name, parent_id, parent_name) m
- on v.signature = m.code
- left join (
- select stat_datetime, signature, sum
- from (
- select stat_datetime, signature, sum, row_number() over (partition by signature order by sum) as rn
- from (
- select stat_datetime,
- signature,
- sum(cost) over (partition by signature order by stat_datetime) as sum
- from (
- select stat_datetime, video.signature, cost
- from (select material_id, signature, create_time
- from (select material_id, min(create_time) as create_time, signature
- from media_api.bytedance_file_video_get
- where material_id is not null and advertiser_id={account_id}
- group by material_id, signature) s) video
- inner join
- (
- select stat_datetime, material_id, sum(cost) as 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 advertiser_id={account_id}
- group by stat_datetime, material_id
- ) rep
- on video.material_id = rep.material_id
- where rep.stat_datetime between date_format(video.create_time, '%Y%m%d') and date_format(date_add(video.create_time, INTERVAL 6 DAY), '%Y%m%d')) t) t
- where sum >=5000
- ) t
- where rn = 1
- ) effect
- on v.signature = effect.signature
- left join (
- select stat_datetime, signature, sum
- from (
- select stat_datetime, signature, sum, row_number() over (partition by signature order by sum) as rn
- from (
- select stat_datetime,
- signature,
- sum(cost) over (partition by signature order by stat_datetime) as sum
- from (
- select stat_datetime, video.signature, cost
- from (select material_id, signature, create_time
- from (select material_id, min(create_time) as create_time, signature
- from media_api.bytedance_file_video_get
- where material_id is not null and advertiser_id={account_id}
- group by material_id, signature) s) video
- inner join
- (
- select stat_datetime, material_id, sum(cost) as 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 advertiser_id={account_id}
- group by stat_datetime, material_id
- ) rep
- on video.material_id = rep.material_id
- where rep.stat_datetime between date_format(video.create_time, '%Y%m%d') and date_format(date_add(video.create_time, INTERVAL 13 DAY), '%Y%m%d')) t) t
- where sum >=50000
- ) t
- where rn = 1
- ) hot
- on v.signature = hot.signature
- group by advertiser_id, id, v.signature, material_id, tag_category_id, parent_id, tag_code
- """.format(account_id=account_id[0])
- print(sql)
- TidbConn.upsert(conn=self.conn, sql_buff=sql)
- self.conn.commit()
- except Exception as e:
- logging.error(e)
- return -1
- TidbConn.conn_close(conn=self.conn)
- return 0
- except Exception as e:
- logging.error(e)
- if __name__ == '__main__':
- status = AppBytedanceFileVideoGetAll().execSql()
- if status == 0:
- print("作业执行成功")
- exit(0)
- else:
- print("作业执行失败")
- exit(-1)
|