MaterialFristCostDateUpdate.py 2.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/11/17 3:40 下午
  4. # @Site :
  5. # @File : MaterialFristCostDateUpdate.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import datetime
  10. import logging
  11. from Apietl.conn.TidbConn import TidbConn
  12. from Apietl.conn.TidbProConn import TidbProConn
  13. from Apietl.utlis.Utils import Utils
  14. class MaterialFristCostDateUpdate:
  15. def __init__(self):
  16. self.conn = TidbProConn.get_connection()
  17. self.Innerconn = TidbConn.get_connection()
  18. self.parm = Utils.get_args()
  19. self.start_time = self.parm[0]
  20. self.end_time = self.parm[1]
  21. def TaskHandler(self):
  22. try:
  23. sql = """
  24. replace into application.material_farst_cost_time
  25. select a.signature,
  26. case when b.frast_cost_time is null then a.stat_date
  27. when a.stat_date<b.frast_cost_time then a.stat_date
  28. else b.frast_cost_time end as frast_cost_time
  29. from
  30. (select signature, stat_date
  31. from (
  32. select signature, stat_date, row_number() over (partition by signature order by stat_date) as rn
  33. from application.kuaishou_material_video_report_daily_dw
  34. where signature is not null
  35. and stat_date >= {start_time} and stat_date<={end_time}
  36. ) t
  37. where t.rn = 1)a
  38. left join
  39. (select signature, frast_cost_time from application.material_farst_cost_time)b
  40. on a.signature=b.signature
  41. """.format(start_time=self.start_time, end_time=self.end_time)
  42. print(sql)
  43. TidbProConn.upsert(conn=self.conn, sql_buff=sql)
  44. TidbConn.upsert(conn=self.Innerconn, sql_buff=sql)
  45. self.conn.commit()
  46. self.Innerconn.commit()
  47. TidbProConn.conn_close(self.conn)
  48. TidbConn.conn_close(self.Innerconn)
  49. return 0
  50. except Exception as e:
  51. logging.error(e)
  52. return -1
  53. if __name__ == '__main__':
  54. status = MaterialFristCostDateUpdate().TaskHandler()
  55. if status == 0:
  56. print("作业执行成功")
  57. exit(0)
  58. else:
  59. print("作业执行失败")
  60. exit(-1)