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