123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566 |
- # Author renyupeng
- # coding=utf-8
- # @Time : 2021/11/17 3:40 下午
- # @Site :
- # @File : MaterialFristCostDateUpdate.py
- # @Software: PyCharm
- # @contact: renyupeng@c-top.com.cn
- # @Tel 1501435553
- import datetime
- import logging
- from Apietl.conn.TidbConn import TidbConn
- from Apietl.conn.TidbProConn import TidbProConn
- from Apietl.utlis.Utils import Utils
- class MaterialFristCostDateUpdate:
- def __init__(self):
- self.conn = TidbProConn.get_connection()
- self.Innerconn = TidbConn.get_connection()
- self.parm = Utils.get_args()
- self.start_time = self.parm[0]
- self.end_time = self.parm[1]
- def TaskHandler(self):
- try:
- sql = """
- replace into application.material_farst_cost_time
- select a.signature,
- case when b.frast_cost_time is null then a.stat_date
- when a.stat_date<b.frast_cost_time then a.stat_date
- else b.frast_cost_time end as frast_cost_time
- from
- (select signature, stat_date
- from (
- select signature, stat_date, row_number() over (partition by signature order by stat_date) as rn
- from application.kuaishou_material_video_report_daily_dw
- where signature is not null
- and stat_date >= {start_time} and stat_date<={end_time}
- ) t
- where t.rn = 1)a
- left join
- (select signature, frast_cost_time from application.material_farst_cost_time)b
- on a.signature=b.signature
- """.format(start_time=self.start_time, end_time=self.end_time)
- print(sql)
- TidbProConn.upsert(conn=self.conn, sql_buff=sql)
- TidbConn.upsert(conn=self.Innerconn, sql_buff=sql)
- self.conn.commit()
- self.Innerconn.commit()
- TidbProConn.conn_close(self.conn)
- TidbConn.conn_close(self.Innerconn)
- return 0
- except Exception as e:
- logging.error(e)
- return -1
- if __name__ == '__main__':
- status = MaterialFristCostDateUpdate().TaskHandler()
- if status == 0:
- print("作业执行成功")
- exit(0)
- else:
- print("作业执行失败")
- exit(-1)
|