| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233 | # Author zhaohailiang# coding=utf-8# @Time    : 2022/01/19 21:10 下午# @Site    :# @File    : AppBytedanceAccountBaseInfoDsD.py# @Software: PyCharm# @contact: zhaohailiang@c-top.com.cn# @Tel 18201631162import loggingfrom Apietl.conn.TidbConn import TidbConnfrom Apietl.utlis.Utils import Utilsclass AppBytedanceAccountBaseInfoDsD:    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_bytedance_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_ad_num,            new_ad_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,            new_cpd_project_num            )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,       sum_cam.cam_num                as all_campaign_num,       cam.campain_num                   new_campaign_num,       sum_ad.ad_num                  as all_ad_num,       t2.ad_num                         new_ad_num,       video_num                      as all_video_num,       t3.day_num                        new_video_num,       sum_eff.new_eff_material          effect_num,       ifnull(t4.new_eff_material, 0) as new_effect_num,       sum_hot.new_hot_material       as hot_num,       ifnull(t5.new_hot_material, 0) as new_hot_num,       0                              as put_campaign_num,       0                              as put_unit_num,       0                              as trust_campaign_num,       0                              as trust_unit_num,       project_num as new_cpd_project_numfrom (select * from `jeecg-boot`.ctop_user_allocation where media_id = 1) t1         left join     (select account_id, count(id) ad_num      from `media_api`.bytedance_ad_get      where date_format(ad_create_time, '%Y%m%d') = {date_time}      group by account_id) t2     on t1.account_id = t2.account_id         left join(    select account_id, count(id) ad_num    from `media_api`.bytedance_ad_get    where date_format(ad_create_time, '%Y%m%d') <= {date_time}    group by account_id) sum_ad on t1.account_id = sum_ad.account_id         left join (select advertiser_id, count(campaign_id) campain_num                    from media_api.bytedance_campaign_get                    where date_format(campaign_create_time, '%Y%m%d') = {date_time}                    group by advertiser_id) cam                   on t1.account_id = cam.advertiser_id         left join(    select advertiser_id, count(campaign_id) cam_num    from `media_api`.bytedance_campaign_get    where date_format(campaign_create_time, '%Y%m%d') <= {date_time}    group by advertiser_id) sum_cam on t1.account_id = sum_cam.advertiser_id         left join     (select advertiser_id account_id, count(distinct signature) day_num      from `media_api`.bytedance_file_video_get      where date_format(create_time, '%Y%m%d') = {date_time}      group by advertiser_id) t3     on t1.account_id = t3.account_id         left join     (select advertiser_id account_id, count(distinct signature) video_num      from `media_api`.bytedance_file_video_get      where date_format(create_time, '%Y%m%d') <= {date_time}      group by advertiser_id) all_video     on t1.account_id = all_video.account_id         left join     (select t2.account_id, count(distinct t1.signature) new_eff_material      from (select signature from application.effect_material where effect_date = {date_time}) t1               inner join           (select distinct advertiser_id account_id, signature            from `media_api`.bytedance_file_video_get            where date_format(create_time, '%Y%m%d') >=                  date_sub({date_time}, INTERVAL 29 DAY)) t2           on t1.signature = t2.signature      group by t2.account_id) t4     on t1.account_id = t4.account_id         left join     (select t2.account_id, count(distinct t1.signature) new_eff_material      from (select signature from application.effect_material where effect_date <= {date_time}) t1               inner join           (select distinct advertiser_id account_id, signature            from `media_api`.bytedance_file_video_get            where date_format(create_time, '%Y%m%d') <= {date_time}) t2           on t1.signature = t2.signature      group by t2.account_id) sum_eff     on t1.account_id = sum_eff.account_id    left join(        select advertiser_id,count(distinct project_id) as project_num from media_api.bytedance_cdp_project          where date_format(project_create_time, '%Y%m%d') = {date_time}      group by advertiser_id    )pro    on t1.account_id=pro.advertiser_id         left join     (select t2.account_id, count(distinct t1.signature) new_hot_material      from (select signature from application.hot_material where hot_date = {date_time} and media_id=1) t1               inner join           (select distinct advertiser_id account_id, signature            from `media_api`.bytedance_file_video_get            where date_format(create_time, '%Y%m%d') >=                  date_sub({date_time}, INTERVAL 29 DAY)) t2           on t1.signature = t2.signature      group by t2.account_id) t5     on t1.account_id = t5.account_id         left join     (select t2.account_id, count(distinct t1.signature) new_hot_material      from (select signature from application.hot_material where  hot_date<= {date_time}) t1               inner join           (select distinct advertiser_id account_id, signature            from `media_api`.bytedance_file_video_get            where date_format(create_time, '%Y%m%d') <= {date_time}) t2           on t1.signature = t2.signature      group by t2.account_id) sum_hot     on t1.account_id = sum_hot.account_id         left join     (         SELECT cua.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_name         FROM `jeecg-boot`.ctop_user_allocation cua                  LEFT JOIN `jeecg-boot`.ctop_project cp ON cua.project_id = cp.id                  LEFT JOIN `jeecg-boot`.ctop_advertiser ca ON ca.id = cua.advertiser_id                  LEFT JOIN `jeecg-boot`.sys_user su ON su.id = cua.user_id                  LEFT JOIN `jeecg-boot`.sys_user lea ON lea.id = su.leader_id                  LEFT JOIN `jeecg-boot`.user_company uc ON uc.user_id = cua.user_id                  LEFT JOIN `jeecg-boot`.ctop_product product ON product.id = cp.product_id                  LEFT JOIN `jeecg-boot`.sys_user_depart dep ON cua.user_id = dep.user_id                  LEFT JOIN `jeecg-boot`.sys_depart part ON dep.dep_id = part.id         WHERE cua.account_id IS NOT NULL           and cua.account_status = 0           and cua.media_id = 1     ) bei     ON t1.account_id = bei.account_idwhere bei.account_id is not null            """.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 = AppBytedanceAccountBaseInfoDsD().taskHandler()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |