| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244 | 
							- # Author zhaohailiang
 
- # coding=utf-8
 
- # @Time    : 2022/01/19 21:10 下午
 
- # @Site    :
 
- # @File    : AppBytedanceAccountBaseInfoDsD.py
 
- # @Software: PyCharm
 
- # @contact: zhaohailiang@c-top.com.cn
 
- # @Tel 18201631162
 
- import logging
 
- from Apietl.conn.TidbConn import TidbConn
 
- from Apietl.utlis.Utils import Utils
 
- class 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,
 
-             promotion_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_num,
 
-        promotion_num as promotion_num
 
- from (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 advertiser_id,count(distinct promotion_id) as promotion_num from media_api.bytedance_promotion_list
 
-           where date_format(promotion_create_time, '%Y%m%d') = {date_time}
 
-       group by advertiser_id
 
-     )promotion
 
-     on t1.account_id=promotion.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_id
 
- where 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 -1
 
- if __name__ == '__main__':
 
-     status = AppBytedanceAccountBaseInfoDsD().taskHandler()
 
-     if status == 0:
 
-         print("作业执行成功")
 
-         exit(0)
 
-     else:
 
-         print("作业执行失败")
 
-         exit(-1)
 
 
  |