| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260 | # Author renyupeng# coding=utf-8# @Time    : 2021/11/12 10:20 上午# @Site    : # @File    : AppBytedanceFileVideoGetAll.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cn# @Tel 1501435553import loggingfrom Apietl.conn.TidbConn import TidbConnclass AppBytedanceFileVideoGetAll:    def __init__(self):        self.conn = TidbConn.get_connection()    def execSql(self):        try:            sql = """select advertiser_id from media_api.bytedance_file_video_get              where advertiser_id  in(1710388576343047)                group by advertiser_id"""            account_list = TidbConn.quary(self.conn, sql)            for account_id in account_list:                try:                    sql = """replace into application.app_bytedance_file_video_getselect advertiser_id,       id,       size,       width,       height,       url,       format,       case when v.signature is null then '' else v.signature end as signature,       poster_url,       bit_rate,       duration,       material_id,       source,       create_time,       filename,       labels,       video_url,       case when tag_category_id is null then '' else tag_category_id end as tag_category_id,       tag_category_name,       case when parent_id is null then '' else parent_id end as parent_id,       parent_name,      case when tag_code is null then '' else tag_code end as tag_code,       tag_name,       clip_id,       clip_name,       shot_id,       shot_name,       plan_id,       plan_name,       leader_id,       leader_name,       material_innovate,       hot.stat_datetime,       effect.stat_datetimefrom (         select advertiser_id,                id,                size,                width,                height,                url,                format,                signature,                poster_url,                bit_rate,                duration,                a.material_id,                source,                create_time,                case when t.material_name is null then a.filename else t.material_name end as filename,                labels,                video_url,                clip_id,                clip_name,                shot_id,                shot_name,                plan_id,                plan_name,                leader_id,                leader_name,                material_innovate         from (select advertiser_id,                      id,                      size,                      width,                      height,                      url,                      format,                      signature,                      poster_url,                      bit_rate,                      duration,                      material_id,                      source,                      create_time,                      filename,                      labels,                      video_url               from media_api.bytedance_file_video_get where advertiser_id={account_id}              ) a                  left join              (select material_id,                      clip_id,                      clip_name,                      shot_id,                      shot_name,                      plan_id,                      plan_name,                      leader_id,                      leader_name,                      material_name,                      material_innovate               from (select cma.material_id,                            clip_id,                            clip.realname   as clip_name,                            shot_id,                            shot.realname   as shot_name,                            plan_id,                            plan.realname   as plan_name,                            cma.leader_id,                            leader.realname as leader_name,                            material_name,                            material_innovate                     from `jeecg-boot`.ctop_material_ascription cma                              left join                          `jeecg-boot`.ctop_material_info b                          on cma.material_id = b.id                              left join `jeecg-boot`.sys_user clip on clip.id = cma.clip_id                              left join `jeecg-boot`.sys_user shot on shot.id = cma.shot_id                              left join `jeecg-boot`.sys_user plan on plan.id = cma.plan_id                              left join `jeecg-boot`.sys_user leader on leader.id = cma.leader_id                    ) v) t              on a.signature = t.material_id) v         left join     (select code,             tag_category_id,             tag_category_name,             parent_id,             parent_name,             group_concat(tag_code order by tag_code separator '-') tag_code,             group_concat(tag_name order by tag_code separator '-') tag_name      from (select a.code, b.tag_category_id, b.tag_category_name, b.parent_id, b.parent_name, b.tag_code, b.tag_name            from `jeecg-boot`.ctop_material_tag_info a                     left join                 (select if(tag_category_id is null,'',tag_category_id) as tag_category_id,                         tag_category_name,                         case                             when parent_id is not null then parent_id                             when tag_category_id = 2 then ''                             else tag_code end parent_id,                         case                             when parent_id = 60155 then '真人'                             when parent_id = 60156 then '制作'                             when parent_id = 60160 then '外拍场景'                             when parent_id = 60161 then '公司内景'                             when tag_category_id = 2 then ''                             else '其他' end     parent_name,                         tag_code,                         tag_name                  from `jeecg-boot`.ctop_tag_info) b                 on a.tag_code = b.tag_code) c      group by code, tag_category_id, tag_category_name, parent_id, parent_name) m     on v.signature = m.code         left join (    select stat_datetime, signature, sum    from (             select stat_datetime, signature, sum, row_number() over (partition by signature order by sum) as rn             from (                      select stat_datetime,                             signature,                             sum(cost) over (partition by signature order by stat_datetime) as sum                      from (                               select stat_datetime, video.signature, cost                               from (select material_id, signature, create_time                                     from (select material_id, min(create_time) as create_time, signature                                           from media_api.bytedance_file_video_get                                           where material_id is not null and advertiser_id={account_id}                                           group by material_id, signature) s) video                                        inner join                                    (                                        select stat_datetime, material_id, sum(cost) as cost                                        from media_api.bytedance_material_report_daily                                        where image_mode in (                                                             'CREATIVE_IMAGE_MODE_VIDEO',                                                             'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL',                                                             'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO'                                            ) and advertiser_id={account_id}                                        group by stat_datetime, material_id                                    ) rep                                    on video.material_id = rep.material_id                               where rep.stat_datetime between date_format(video.create_time, '%Y%m%d') and date_format(date_add(video.create_time, INTERVAL 6 DAY), '%Y%m%d')) t) t             where sum >=5000         ) t    where rn = 1) effect                   on v.signature = effect.signature         left join (    select stat_datetime, signature, sum    from (             select stat_datetime, signature, sum, row_number() over (partition by signature order by sum) as rn             from (                      select stat_datetime,                             signature,                             sum(cost) over (partition by signature order by stat_datetime) as sum                      from (                               select stat_datetime, video.signature, cost                               from (select material_id, signature, create_time                                     from (select material_id, min(create_time) as create_time, signature                                           from media_api.bytedance_file_video_get                                           where material_id is not null and advertiser_id={account_id}                                           group by material_id, signature) s) video                                        inner join                                    (                                        select stat_datetime, material_id, sum(cost) as cost                                        from media_api.bytedance_material_report_daily                                        where image_mode in (                                                             'CREATIVE_IMAGE_MODE_VIDEO',                                                             'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL',                                                             'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO'                                            ) and advertiser_id={account_id}                                        group by stat_datetime, material_id                                    ) rep                                    on video.material_id = rep.material_id                               where rep.stat_datetime between date_format(video.create_time, '%Y%m%d') and date_format(date_add(video.create_time, INTERVAL 13 DAY), '%Y%m%d')) t) t             where sum >=50000         ) t    where rn = 1) hot                   on v.signature = hot.signaturegroup by advertiser_id, id,  v.signature, material_id, tag_category_id, parent_id, tag_code                       """.format(account_id=account_id[0])                    print(sql)                    TidbConn.upsert(conn=self.conn, sql_buff=sql)                    self.conn.commit()                except Exception as e:                    logging.error(e)                    return -1            TidbConn.conn_close(conn=self.conn)            return 0        except Exception as e:            logging.error(e)if __name__ == '__main__':    status = AppBytedanceFileVideoGetAll().execSql()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |