# 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 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(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 -1 if __name__ == '__main__': status = AppKuaishouVideoList().execSql() if status == 0: print("作业执行成功") exit(0) else: print("作业执行失败") exit(-1)