123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103 |
- # 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)
|