| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182 | # Author zhaohailiang# coding=utf-8# @Time    : 2022/01/19 21:10 下午# @Site    :# @File    : AppKuaishouAccountBaseInfoDsD.py# @Software: PyCharm# @contact: zhaohailiang@c-top.com.cn# @Tel 18201631162import loggingfrom Apietl.conn.TidbConn import TidbConnfrom Apietl.utlis.Utils import Utilsclass AppKuaishouAccountBaseInfoDsD:    def __init__(self):        self.conn = TidbConn.get_connection()        self.parm = Utils.get_task_args()        self.date_time = self.parm[0]    def taskHandler(self):        try:            sql = """          SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';          replace into `application`.`app_kuaishou_account_base_info_d`(stat_date,account_id,account_name,project_id,project_name,product_id,product_name,user_id,user_name,leader_id,leader_name,company_id,company_name,dept_id,dept_name,all_campaign_num,new_campaign_num,all_unit_num,new_unit_num,all_video_num,new_video_num,effect_num,new_effect_num,hot_num,new_hot_num,put_campaign_num,put_unit_num,trust_campaign_num,trust_unit_num)select * from (select{date_time} stat_date,t1.account_id,bei.account_name,bei.project_id,bei.project_name,bei.product_id,bei.product_name,bei.user_id,bei.user_name,bei.leader_id,bei.leader_name,bei.company_id,bei.company_name,bei.dept_id,bei.dept_name,t2.cam_num as all_campaign_num ,t2.cam_num new_campaign_num,t2.unit_num as all_ad_num,t2.unit_num new_ad_num,t3.day_num  as all_video_num,t3.day_num new_video_num,ifnull(t4.new_eff_material,0) as effect_num,ifnull(t4.new_eff_material,0) as new_effect_num,ifnull(t5.new_hot_material,0) as hot_num,ifnull(t5.new_hot_material,0) as new_hot_num,0 put_campaign_num,0 put_unit_num, trush_cam_num, trush_unit_numfrom (select account_id from `jeecg-boot`.ctop_user_allocation where media_id=2)t1 left join(select distinct account_id,signature from `media_api`.kuaishou_video_list where date_format(stat_date,'%Y%m%d')={date_time}) ty     on t1.account_id=ty.account_idleft join(select advertiser_id account_id,count(distinct campaign_id) cam_num ,count(unit_id) unit_num   from  `media_api`.kuaishou_ad_unit_list where date_format(create_time,'%Y%m%d')={date_time}and (unit_source!=1 or unit_source is null)group by advertiser_id) t2on t1.account_id=t2.account_idleft join(select account_id,count(distinct signature) day_num from `media_api`.kuaishou_video_listwhere date_format(stat_date,'%Y%m%d')={date_time} group by account_id) t3on t1.account_id=t3.account_idleft join(select t2.account_id,count(distinct t1.material_id) new_eff_material from(select material_id from application.app_kuaishou_material_info where effect_date={date_time}) t1inner join(select distinct account_id,signature from `media_api`.kuaishou_video_list where date_format(stat_date,'%Y%m%d')>=date_sub({date_time},INTERVAL 29 DAY)) t2on t1.material_id=t2.signature group by t2.account_id) t4on t1.account_id=t4.account_idleft join(select t2.account_id,count(distinct t1.material_id) new_hot_material from(select material_id from application.app_kuaishou_material_info where faddish_date={date_time}) t1inner join(select distinct account_id,signature from `media_api`.kuaishou_video_list where date_format(stat_date,'%Y%m%d')>=date_sub({date_time},INTERVAL 29 DAY)) t2on t1.material_id=t2.signature group by t2.account_id) t5on t1.account_id=t5.account_idleft join(   select advertiser_id account_id,count(unit_id) trush_unit_num, count(distinct campaign_id) trush_cam_num  from  `media_api`.kuaishou_ad_unit_list where date_format(create_time,'%Y%m%d')={date_time}   and unit_source=1group by advertiser_id    )trush    on t1.account_id=trush.account_idleft join(SELECTcua.account_id id,cua.account_id,cp.id project_id,product.id product_id,product.product_name,ca.id advertiser_id,uc.company_id company_id,cua.auth_name account_name,cp.project_name project_name,ca.`name` advertiser_name,uc.company_name company_name,cp.sale_id sale_id,su.id user_id,su.realname user_name,su.leader_id,lea.realname leader_name,su.org_code org_code,dep.dep_id dept_id,part.depart_name dept_nameFROM`jeecg-boot`.ctop_user_allocation cuaLEFT JOIN `jeecg-boot`.ctop_project cp ON cua.project_id = cp.idLEFT JOIN `jeecg-boot`.ctop_advertiser ca ON ca.id = cua.advertiser_idLEFT JOIN `jeecg-boot`.sys_user su ON su.id = cua.user_idLEFT JOIN `jeecg-boot`.sys_user lea ON lea.id = su.leader_idLEFT JOIN `jeecg-boot`.user_company uc ON uc.user_id = cua.user_idLEFT JOIN `jeecg-boot`.ctop_product product ON product.id = cp.product_idLEFT JOIN `jeecg-boot`.sys_user_depart dep ON cua.user_id=dep.user_idLEFT JOIN `jeecg-boot`.sys_depart part  ON dep.dep_id=part.idWHEREcua.account_id IS NOT NULL and cua.account_status=0 and cua.media_id=2) beiON t1.account_id = bei.account_id  where   bei.account_id is not null) mgroup by account_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 -1if __name__ == '__main__':    status = AppKuaishouAccountBaseInfoDsD().taskHandler()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |