MaterialFristCostDateUpdate.py 1.6 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
  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.TidbProConn import TidbProConn
  12. from Apietl.utlis.Utils import Utils
  13. class MaterialFristCostDateUpdate:
  14. def __init__(self):
  15. self.conn = TidbProConn.get_connection()
  16. self.parm = Utils.get_args()
  17. self.start_time = self.parm[0]
  18. self.end_time = self.parm[1]
  19. def TaskHandler(self):
  20. try:
  21. sql = """
  22. replace into application.material_farst_cost_time
  23. select a.signature,
  24. case when b.frast_cost_time is null then a.stat_date
  25. when a.stat_date<b.frast_cost_time then a.stat_date
  26. else b.frast_cost_time end as frast_cost_time
  27. from
  28. (select signature, stat_date
  29. from (
  30. select signature, stat_date, row_number() over (partition by signature order by stat_date) as rn
  31. from application.kuaishou_material_video_report_daily_dw
  32. where signature is not null
  33. and stat_date between {start_time} and {end_time}
  34. ) t
  35. where t.rn = 1)a
  36. left join
  37. (select signature, frast_cost_time from application.material_farst_cost_time)b
  38. on a.signature=b.signature
  39. """.format(start_time=self.start_time, end_time=self.end_time)
  40. print(sql)
  41. TidbProConn.upsert(conn=self.conn, sql_buff=sql)
  42. self.conn.commit()
  43. TidbProConn.conn_close(self.conn)
  44. except Exception as e:
  45. logging.error(e)
  46. if __name__ == '__main__':
  47. MaterialFristCostDateUpdate().TaskHandler()