# Author zhaohailiang # coding=utf-8 # @Time : 2022/01/19 21:10 下午 # @Site : # @File : HotMaterialTenThousand.py # @Software: PyCharm # @contact: zhaohailiang@c-top.com.cn # @Tel 18201631162 import datetime import logging from Apietl.conn.TidbConn import TidbConn from Apietl.utlis.Utils import Utils class HotMaterialTenThousand: def __init__(self): self.conn = TidbConn.get_connection() self.date_time = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y%m%d") def taskHandler(self): try: sql = """ #快手 REPLACE INTO application.hot_material_ten_thousand select new.media_id, new.signature, new.hot_date from (select 2 media_id, signature, min(stat_date) hot_date from ( select stat_date, signature, sum(charge) over (partition by signature order by stat_date) as sum from ( select m.stat_date, n.signature, m.charge as charge from (select stat_date, photo_id, charge from `media_api`.kuaishou_material_video_report_daily where stat_date >= date_format(date_sub({stat_date}, INTERVAL 13 DAY), '%Y%m%d') and stat_date<={stat_date} group by stat_date, photo_id) m INNER JOIN (select b.photo_id, a.signature from (select distinct signature from application.material_farst_cost_time where frast_cost_time >= date_format(date_sub({stat_date}, INTERVAL 13 DAY), '%Y%m%d')) a inner join (select photo_id, signature from `application`.kuaishou_video_list where stat_date >= date_format(date_sub({stat_date}, INTERVAL 20 DAY), '%Y-%m-%d') group by photo_id, signature) b on a.signature = b.signature) n on m.photo_id = n.photo_id group by m.stat_date, n.signature) m) info where sum >= 10000 group by signature) new left join (select * from application.hot_material_ten_thousand where media_id = 2) eff on new.signature = eff.signature where eff.signature is null union all select new.media_id, new.signature, new.hot_date from (select 1 media_id, signature, min(stat_date) hot_date from ( select stat_date, signature, sum(cost) over (partition by signature order by stat_date) as sum from ( select m.stat_date, n.signature,m.cost as cost from (select stat_datetime stat_date, material_id, sum(cost) cost from application.bytedance_material_video_report_daily_dw WHERE stat_datetime >= date_format(date_sub({stat_date}, INTERVAL 13 DAY), '%Y%m%d') and stat_datetime<= {stat_date} group by stat_datetime, material_id) m INNER JOIN (select b.material_id, a.signature from (select distinct signature from application.bytedance_material_farst_cost_time where frast_cost_time >= date_format(date_sub({stat_date}, INTERVAL 13 DAY), '%Y%m%d')) a inner join (select signature, material_id, filename, video_url from `application`.bytedance_video_get where create_time >= date_format(date_sub({stat_date}, INTERVAL 20 DAY), '%Y-%m-%d') group by signature, material_id) b on a.signature = b.signature) n on m.material_id = n.material_id group by m.stat_date, n.signature) m) info where sum >= 10000 group by signature) new left join (select * from application.hot_material_ten_thousand where media_id = 1) eff on new.signature = eff.signature where eff.signature is null; """.format(stat_date=self.date_time) print(sql) TidbConn.upsert(conn=self.conn, sql_buff=sql) self.conn.commit() TidbConn.conn_close(conn=self.conn) return 0 except Exception as e: logging.error(e) return -1 if __name__ == '__main__': status = HotMaterialTenThousand().taskHandler() if status == 0: print("作业执行成功") exit(0) else: print("作业执行失败") exit(-1)