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