| 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 1501435553import datetimeimport loggingfrom math import ceilfrom Apietl.conn.TidbConn import TidbConnclass 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 media_api.kuaishou_video_list where  date_format(stat_date, '%Y-%m-%d') >= date_sub('{date_time}', INTERVAL 13 DAY)              group by account_id """.format(date_time=self.date_time)            account_list = TidbConn.quary(self.conn, sql)            for account_id in account_list:                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)                    return -1            TidbConn.conn_close(conn=self.conn)            return 0        except Exception as e:            logging.error(e)            return -1if __name__ == '__main__':    status = AppKuaishouVideoList().execSql()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |