# Author zhaohailiang # coding=utf-8 # @Time : 2022/01/19 21:10 下午 # @Site : # @File : AppKuaishouAccountTodayBaseInfoD.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 AppKuaishouAccountTodayBaseInfoD: def __init__(self): self.conn = TidbConn.get_connection() self.date_time = (datetime.date.today() + datetime.timedelta(0)).strftime("%Y%m%d") def taskHandler(self): try: sql = """ replace into application.app_kuaishou_account_today_base_info_d(stat_date, hour, project_id, account_id, user_id, user_name, leader_id, leader_name, new_all_unit_num, new_unit_num, new_trush_unit_num, account_name, project_name) select stat_date, hour, project_id, a.advertiser_id, info.user_id, user.realname as user_name, user.leader_id, leader.realname, all_unit_num, unit_num, trush_unit_num, auth_name as acount_name, pro.project_name from (select stat_date, advertiser_id, hour, count(distinct unit_id) as all_unit_num, sum(unit_num) as unit_num, sum(trush_unit_num) as trush_unit_num from ( select date_format(create_time, '%Y%m%d') as stat_date, advertiser_id, unit_id, create_time, hour(create_time) as hour, if(unit_source = 0, 1, 0) as unit_num, if(unit_source = 1, 1, 0) as trush_unit_num from media_api.kuaishou_ad_unit_list where date_format(create_time, '%Y%m%d') = {date_time}) t group by advertiser_id, hour) a left join `jeecg-boot`.ctop_user_allocation info on a.advertiser_id = info.account_id left join `jeecg-boot`.ctop_project pro on info.project_id = pro.id left join `jeecg-boot`.sys_user user on info.user_id = user.id left join `jeecg-boot`.sys_user leader on user.leader_id = leader.id """.format(date_time=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 = AppKuaishouAccountTodayBaseInfoD().taskHandler() if status == 0: print("作业执行成功") exit(0) else: print("作业执行失败") exit(-1)