MaterialFristCostTime.py 1.8 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556
  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.TidbProConn import TidbProConn
  12. class MaterialFristCostTime:
  13. def __init__(self):
  14. self.conn = TidbProConn.get_connection()
  15. def TaskHandler(self):
  16. try:
  17. sql = """
  18. select count(1) from (
  19. select signature,stat_date
  20. from (
  21. select signature, stat_date, row_number() over (partition by signature order by stat_date) as rn
  22. from application.kuaishou_material_video_report_daily_dw
  23. )t
  24. where t.rn=1)t
  25. """
  26. totalNum = TidbProConn.quary(self.conn, sql)[0][0]
  27. pageCount = ceil(totalNum / 20000)
  28. for i in range(pageCount):
  29. try:
  30. sql = """
  31. replace into application.material_farst_cost_time
  32. select signature,stat_date
  33. from (
  34. select signature, stat_date, row_number() over (partition by signature order by stat_date) as rn
  35. from application.kuaishou_material_video_report_daily_dw where signature is not null
  36. )t
  37. where t.rn=1
  38. order by signature,stat_date limit {num},20000
  39. """.format(num=i * 20000)
  40. print(sql)
  41. TidbProConn.upsert(conn=self.conn, sql_buff=sql)
  42. self.conn.commit()
  43. except Exception as e:
  44. logging.error(e)
  45. return -1
  46. TidbProConn.conn_close(conn=self.conn)
  47. return 0
  48. except Exception as e:
  49. logging.error(e)
  50. if __name__ == '__main__':
  51. MaterialFristCostTime().TaskHandler()