| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103 | # Author zhaohailiang# coding=utf-8# @Time    : 2022/01/19 21:10 下午# @Site    :# @File    : HotMaterialMonthFiftyThousand.py# @Software: PyCharm# @contact: zhaohailiang@c-top.com.cn# @Tel 18201631162import datetimeimport loggingfrom Apietl.conn.TidbConn import TidbConnfrom Apietl.utlis.Utils import Utilsclass HotMaterialMonthFiftyThousand:    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_month_fifty_thousandselect new.media_id, new.signature, new.hot_datefrom (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 29 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 29 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 40 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 >= 50000      group by signature) new         left join (select * from application.hot_material_month_fifty_thousand where media_id = 2) eff                   on new.signature = eff.signaturewhere eff.signature is nullunion allselect new.media_id, new.signature, new.hot_datefrom (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 29 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 29 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 40 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 >= 50000      group by signature) new         left join (select * from application.hot_material_month_fifty_thousand where media_id = 1) eff                   on new.signature = eff.signaturewhere 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 -1if __name__ == '__main__':    status = HotMaterialMonthFiftyThousand().taskHandler()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |