MaterialFristCostTime.py 2.0 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/11/17 3:40 下午
  4. # @Site :
  5. # @File : MaterialFristCostTime.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import logging
  10. from math import ceil
  11. from Apietl.conn.TidbConn import TidbConn
  12. from Apietl.conn.TidbProConn import TidbProConn
  13. class MaterialFristCostTime:
  14. def __init__(self):
  15. self.conn = TidbProConn.get_connection()
  16. self.Innerconn = TidbConn.get_connection()
  17. def TaskHandler(self):
  18. try:
  19. sql = """
  20. select count(1) from (
  21. select signature,stat_date
  22. from (
  23. select signature, stat_date, row_number() over (partition by signature order by stat_date) as rn
  24. from application.kuaishou_material_video_report_daily_dw
  25. )t
  26. where t.rn=1)t
  27. """
  28. totalNum = TidbProConn.quary(self.conn, sql)[0][0]
  29. pageCount = ceil(totalNum / 10000)
  30. for i in range(pageCount):
  31. try:
  32. sql = """
  33. replace into application.material_farst_cost_time
  34. select signature,stat_date
  35. from (
  36. select signature, stat_date, row_number() over (partition by signature order by stat_date) as rn
  37. from application.kuaishou_material_video_report_daily_dw where signature is not null
  38. )t
  39. where t.rn=1
  40. order by signature,stat_date limit {num},10000
  41. """.format(num=i * 10000)
  42. print(sql)
  43. TidbConn.upsert(conn=self.Innerconn, sql_buff=sql)
  44. TidbProConn.upsert(conn=self.conn, sql_buff=sql)
  45. self.conn.commit()
  46. self.Innerconn.commit()
  47. except Exception as e:
  48. logging.error(e)
  49. return -1
  50. TidbConn.conn_close(conn=self.Innerconn)
  51. TidbProConn.conn_close(conn=self.conn)
  52. return 0
  53. except Exception as e:
  54. logging.error(e)
  55. if __name__ == '__main__':
  56. MaterialFristCostTime().TaskHandler()