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