1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253 |
- # Author renyupeng
- # coding=utf-8
- # @Time : 2022/8/4 2:35 下午
- # @Site :
- # @File : AppBytedanceMaterialProjectRalation.py
- # @Software: PyCharm
- # @contact: renyupeng@c-top.com.cn
- # @Tel 1501435553
- import logging
- from Apietl.conn.TidbConn import TidbConn
- class AppBytedanceMaterialProjectRalation:
- def __init__(self):
- self.conn = TidbConn.get_connection()
- def handler(self):
- try:
- sql = """select advertiser_id from media_api.bytedance_file_video_get
- where create_time>=date_format(date_sub(now(),interval 3 day),'%Y-%m-%d')
- group by advertiser_id"""
- account_list = TidbConn.quary(self.conn, sql)
- for account_id in account_list:
- try:
- sql = """
- replace into application.bytedance_project_material_relation
- select project_id, signature, date_format(create_time,'%Y%m%d') from(
- select project_id, signature,create_time,row_number() over (partition by signature order by create_time desc) as rn
- from (select advertiser_id, signature,create_time
- from media_api.bytedance_file_video_get where advertiser_id ={account_id}
- and create_time>=date_format(date_sub(now(),interval 3 day),'%Y-%m-%d')
- and signature is not null
- group by advertiser_id, signature) a
- left join
- (select account_id, project_id from `jeecg-boot`.ctop_user_allocation where media_id != 2) b
- on a.advertiser_id = b.account_id
- group by project_id, signature)t
- where rn <=1
- """.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
- except Exception as e:
- logging.error(e)
- return -1
- if __name__ == '__main__':
- AppBytedanceMaterialProjectRalation().handler()
|