| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281 | 
							- # Author renyupeng
 
- # coding=utf-8
 
- # @Time    : 2021/11/12 10:20 上午
 
- # @Site    : 
 
- # @File    : AppBytedanceFileVideoGetAll.py
 
- # @Software: PyCharm
 
- # @contact: renyupeng@c-top.com.cn
 
- # @Tel 1501435553
 
- import logging
 
- from Apietl.conn.TidbConn import TidbConn
 
- class 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( 
 
- 1707223153526792
 
- ,1664657972166663
 
- ,1707223213061127
 
- ,1698359675409486
 
- ,1698089060301832
 
- ,1707315154915335
 
- ,1714136868023373
 
- ,1707315154450440
 
- ,1707315153522695
 
- ,1692751131201549
 
- ,1715489403810824
 
- ,1715489404251150
 
- ,1707315152598024
 
- ,1696450109948936
 
- ,1707315152139335
 
- ,1672178052130888
 
- ,1698359674963080
 
- ,1700996856859720
 
- ,1697174845285384
 
- ,1678959320699917
 
- )
 
-                 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_get
 
- select 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_datetime
 
- from (
 
-          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.signature
 
- group 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)
 
 
  |