| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667 | # Author renyupeng# coding=utf-8# @Time    : 2021/11/17 3:40 下午# @Site    :# @File    : MaterialFristCostDateUpdate.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cn# @Tel 1501435553import datetimeimport loggingfrom Apietl.conn.TidbConn import TidbConnfrom Apietl.conn.TidbProConn import TidbProConnfrom Apietl.utlis.Utils import Utilsclass 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.bytedance_material_farst_cost_timeselect a.signature,case when b.frast_cost_time is null then a.stat_datetime when a.stat_datetime<b.frast_cost_time then a.stat_datetimeelse b.frast_cost_time end as frast_cost_timefrom (select signature, stat_datetimefrom (         select signature, stat_datetime, row_number() over (partition by signature order by stat_datetime) as rn         from application.bytedance_material_video_report_daily_dw         where signature is not null           and stat_datetime  between  {start_time} and  {end_time}     ) twhere t.rn = 1)aleft join (select signature, frast_cost_time from application.bytedance_material_farst_cost_time)bon 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 -1if __name__ == '__main__':    status = MaterialFristCostDateUpdate().TaskHandler()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |