1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556 |
- # Author renyupeng
- # coding=utf-8
- # @Time : 2021/11/17 3:40 下午
- # @Site :
- # @File : MaterialFristCostTime.py
- # @Software: PyCharm
- # @contact: renyupeng@c-top.com.cn
- # @Tel 1501435553
- import logging
- from math import ceil
- from Apietl.conn.TidbProConn import TidbProConn
- class MaterialFristCostTime:
- def __init__(self):
- self.conn = TidbProConn.get_connection()
- def TaskHandler(self):
- try:
- sql = """
- select count(1) from (
- select signature,stat_date
- from (
- select signature, stat_date, row_number() over (partition by signature order by stat_date) as rn
- from application.kuaishou_material_video_report_daily_dw
- )t
- where t.rn=1)t
- """
- totalNum = TidbProConn.quary(self.conn, sql)[0][0]
- pageCount = ceil(totalNum / 20000)
- for i in range(pageCount):
- try:
- sql = """
- replace into application.material_farst_cost_time
- select signature,stat_date
- from (
- select signature, stat_date, row_number() over (partition by signature order by stat_date) as rn
- from application.kuaishou_material_video_report_daily_dw where signature is not null
- )t
- where t.rn=1
- order by signature,stat_date limit {num},20000
- """.format(num=i * 20000)
- print(sql)
- TidbProConn.upsert(conn=self.conn, sql_buff=sql)
- self.conn.commit()
- except Exception as e:
- logging.error(e)
- return -1
- TidbProConn.conn_close(conn=self.conn)
- return 0
- except Exception as e:
- logging.error(e)
- if __name__ == '__main__':
- MaterialFristCostTime().TaskHandler()
|