| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258 | 
							- # Author renyupeng
 
- # coding=utf-8
 
- # @Time    : 2021/11/12 10:20 上午
 
- # @Site    :
 
- # @File    : AppKuaishouVideoList.py
 
- # @Software: PyCharm
 
- # @contact: renyupeng@c-top.com.cn
 
- # @Tel 1501435553
 
- import datetime
 
- import logging
 
- from math import ceil
 
- from Apietl.conn.TidbConn import TidbConn
 
- class AppKuaishouVideoList:
 
-     def __init__(self):
 
-         self.conn = TidbConn.get_connection()
 
-         self.date_time = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y-%m-%d")
 
-     def execSql(self):
 
-         try:
 
-             sql = """select account_id from `jeecg-boot`.ctop_user_allocation where  account_status=0 and media_id=2""".format(date_time=self.date_time)
 
-             cur = self.conn.cursor()
 
-             cur.execute(sql)
 
-             result = cur.fetchall()
 
-             for account_id in result:
 
-                 try:
 
-                     sql = """
 
-             replace into application.app_kuaishou_video_list
 
-             select account_id,
 
-                    status,
 
-                    photo_id,
 
-                    width,
 
-                    height,
 
-                    url,
 
-                    cover_url,
 
-                    material_type,
 
-                    channel_type,
 
-                    v.stat_date,
 
-                    case when v.signature is null then '' else v.signature end as signature,
 
-                    photo_name,
 
-                    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_date,
 
-                    effect.stat_date
 
-             from (
 
-                      select `account_id`,
 
-                             `status`,
 
-                             `photo_id`,
 
-                             `width`,
 
-                             `height`,
 
-                             `url`,
 
-                             `cover_url`,
 
-                             `material_type`,
 
-                             `channel_type`,
 
-                             `stat_date`,
 
-                             `signature`,
 
-                             case when t.material_name is null then a.photo_name else t.material_name end as photo_name,
 
-                             clip_id,
 
-                             clip_name,
 
-                             shot_id,
 
-                             shot_name,
 
-                             plan_id,
 
-                             plan_name,
 
-                             leader_id,
 
-                             leader_name,
 
-                             material_innovate
 
-                      from (select `account_id`,
 
-                                   `status`,
 
-                                   `photo_id`,
 
-                                   `width`,
 
-                                   `height`,
 
-                                   `url`,
 
-                                   `cover_url`,
 
-                                   `material_type`,
 
-                                   `channel_type`,
 
-                                   `stat_date`,
 
-                                   `signature`,
 
-                                   `photo_name`
 
-                            from media_api.kuaishou_video_list 
 
-                            where date_format(stat_date, '%Y-%m-%d') >= date_sub('{date_time}', INTERVAL 13 DAY)
 
-                            and account_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 tag_category_id,
 
-                                      tag_category_name,
 
-                                      case
 
-                                          when parent_id is not null then parent_id
 
-                                          when tag_category_id = 2 then null
 
-                                          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 null
 
-                                          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_date, signature, sum
 
-                 from (
 
-                          select stat_date, signature, sum, row_number() over (partition by signature order by sum) as rn
 
-                          from (
 
-                                   select stat_date,
 
-                                          signature,
 
-                                          sum(cost) over (partition by signature order by stat_date) as sum
 
-                                   from (
 
-                                            select rep.stat_date, video.signature, cost
 
-                                            from (select photo_id, signature, stat_date
 
-                                                  from (select photo_id,
 
-                                                               stat_date,
 
-                                                               signature,
 
-                                                               row_number() over (partition by photo_id order by stat_date) rn
 
-                                                        from media_api.kuaishou_video_list
 
-                                                        where photo_id is not null
 
-                                                          and photo_id != 0 and account_id ={account_id}
 
-                                                          and date_format(stat_date, '%Y-%m-%d') >=
 
-                                                              date_sub('{date_time}', INTERVAL 6 DAY)
 
-                                                        group by photo_id, signature) s
 
-                                                  where rn = 1) video
 
-                                                     left join
 
-                                                 (
 
-                                                     select stat_date, photo_id, sum(charge) as cost
 
-                                                     from media_api.kuaishou_material_video_report_daily
 
-                                                     where photo_id is not null
 
-                                                       and photo_id != 0 and advertiser_id ={account_id}
 
-                                                     group by stat_date, photo_id
 
-                                                 ) rep
 
-                                                 on video.photo_id = rep.photo_id
 
-                                            where rep.stat_date between date_format(video.stat_date, '%Y%m%d') and date_format(date_add(video.stat_date, INTERVAL 6 DAY), '%Y%m%d')) t) t
 
-                          where sum >= 5000
 
-                      ) t
 
-                 where rn = 1
 
-             ) effect
 
-                                on effect.signature = v.signature
 
-                      left join (
 
-                 select stat_date, signature, sum
 
-                 from (
 
-                          select stat_date, signature, sum, row_number() over (partition by signature order by sum) as rn
 
-                          from (
 
-                                   select stat_date,
 
-                                          signature,
 
-                                          sum(cost) over (partition by signature order by stat_date) as sum
 
-                                   from (
 
-                                            select rep.stat_date, video.signature, cost
 
-                                            from (select photo_id, signature, stat_date
 
-                                                  from (select photo_id,
 
-                                                               stat_date,
 
-                                                               signature,
 
-                                                               row_number() over (partition by photo_id order by stat_date) rn
 
-                                                        from media_api.kuaishou_video_list
 
-                                                        where photo_id is not null 
 
-                                                          and photo_id != 0 and account_id ={account_id}
 
-                                                          and date_format(stat_date, '%Y-%m-%d') >=
 
-                                                              date_sub('{date_time}', INTERVAL 13 DAY)
 
-                                                        group by photo_id, signature) s
 
-                                                  where rn = 1) video
 
-                                                     left join
 
-                                                 (
 
-                                                     select stat_date, photo_id, sum(charge) as cost
 
-                                                     from media_api.kuaishou_material_video_report_daily
 
-                                                     where photo_id is not null and advertiser_id ={account_id}
 
-                                                       and photo_id != 0
 
-                                                     group by stat_date, photo_id
 
-                                                 ) rep
 
-                                                 on video.photo_id = rep.photo_id
 
-                                            where rep.stat_date between date_format(video.stat_date, '%Y%m%d') and date_format(date_add(video.stat_date, INTERVAL 13 DAY), '%Y%m%d')) t) t
 
-                          where sum >= 50000
 
-                      ) t
 
-                 where rn = 1
 
-             ) hot
 
-                                on hot.signature = v.signature
 
-             group by account_id, photo_id, v.stat_date, v.signature, tag_category_id, parent_id, tag_code
 
-                                 """.format(date_time=self.date_time, 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)
 
-             TidbConn.conn_close(conn=self.conn)
 
-             return 0
 
-         except Exception as e:
 
-             logging.error(e)
 
-             return -1
 
- if __name__ == '__main__':
 
-     status = AppKuaishouVideoList().execSql()
 
-     if status == 0:
 
-         print("作业执行成功")
 
-         exit(0)
 
-     else:
 
-         print("作业执行失败")
 
-         exit(-1)
 
 
  |