| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324 | # Author renyupeng# coding=utf-8# @Time    : 2021/9/27 10:50 上午# @Site    :# @File    : AdvertiserDailyInventoryReport.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cn# @Tel 1501435553import loggingfrom math import ceilfrom Apietl.utlis.Logger import Loggerfrom Apietl.utlis.Utils import Utilsfrom Apietl.conn.TidbConn import TidbConnclass AdvertiserDailyInventoryReport:    def __init__(self):        self.conn = TidbConn.get_connection()        self.parm = Utils.get_args()        self.start_time = self.parm[0]        self.end_time = self.parm[1]    def daily_report(self):        try:            sql = """select count(1) from media_api.bytedance_advertiser_inventory_report_daily where             stat_datetime between {start_time} and {end_time}""".format(                start_time=self.start_time, end_time=self.end_time)            totalNum = TidbConn.quary(self.conn, sql)[0][0]            pageCount = ceil(totalNum / 20000)            for i in range(pageCount):                sql = """            replace into application.bytedance_advertiser_inventory_report_daily_dw(account_id, stat_datetime, project_id, product_id,                                                              advertiser_id, company_id, account_name, project_name,                                                              advertiser_name, company_name, sale_id, user_id, org_code,                                                              cost, `show`, avg_show_cost, click, avg_click_cost, ctr,                                                              `convert`, convert_cost, convert_rate, deep_convert,                                                              deep_convert_cost, deep_convert_rate, attribution_convert,                                                              attribution_convert_cost, attribution_deep_convert,                                                              attribution_deep_convert_cost, download_start,                                                              download_start_cost, download_start_rate, download_finish,                                                              download_finish_cost, download_finish_rate, click_install,                                                              install_finish, install_finish_cost, install_finish_rate,                                                              active, active_cost, active_rate, register,                                                              active_register_cost, active_register_rate, next_day_open,                                                              next_day_open_cost, next_day_open_rate,                                                              attribution_next_day_open_cnt,                                                              attribution_next_day_open_cost,                                                              attribution_next_day_open_rate, game_addiction,                                                              game_addiction_cost, game_addiction_rate, pay_count,                                                              active_pay_cost, active_pay_rate, loan_completion,                                                              loan_completion_cost, loan_completion_rate,                                                              pre_loan_credit, pre_loan_credit_cost, loan_credit,                                                              loan_credit_cost, loan_credit_rate, in_app_uv,                                                              in_app_detail_uv, in_app_cart, in_app_pay, in_app_order,                                                              attribution_game_pay_7d_count,                                                              attribution_game_pay_7d_cost,                                                              attribution_active_pay_7d_per_count, game_pay_cost,                                                              game_pay_count, phone, form, map_search, button, view,                                                              download, qq, lottery, vote, message, redirect, shopping,                                                              consult, wechat, phone_confirm, phone_connect,                                                              consult_effective, coupon, coupon_single_page,                                                              redirect_to_shop, poi_collect, poi_address_click,                                                              luban_order_cnt, luban_order_stat_amount, luban_order_roi,                                                              luban_live_enter_cnt, live_watch_one_minute_count,                                                              luban_live_follow_cnt, live_fans_club_join_cnt,                                                              luban_live_comment_cnt, luban_live_share_cnt,                                                              luban_live_gift_cnt, luban_live_gift_amount,                                                              luban_live_slidecart_click_cnt,                                                              luban_live_click_product_cnt, luban_live_pay_order_count,                                                              luban_live_pay_order_stat_cost,                                                              live_component_click_count, live_component_click_cost,                                                              live_component_click_rate, wechat_login_count,                                                              attribution_wechat_login_30d_count, wechat_login_cost,                                                              attribution_wechat_login_30d_cost, wechat_first_pay_count,                                                              attribution_wechat_first_pay_30d_count,                                                              wechat_first_pay_cost,                                                              attribution_wechat_first_pay_30d_cost,                                                              wechat_first_pay_rate,                                                              attribution_wechat_first_pay_30d_rate, wechat_pay_amount,                                                              attribution_wechat_pay_30d_amount,                                                              attribution_wechat_pay_30d_roi, phone_effective,                                                              total_play, valid_play, valid_play_cost, valid_play_rate,                                                              play_25_feed_break, play_50_feed_break,                                                              play_75_feed_break, play_100_feed_break,                                                              average_play_time_per_play, play_over_rate,                                                              wifi_play_rate, wifi_play, play_duration_sum,                                                              advanced_creative_phone_click,                                                              advanced_creative_counsel_click,                                                              advanced_creative_form_click,                                                              advanced_creative_coupon_addition,                                                              advanced_creative_form_submit, card_show, share, comment,                                                              `like`, follow, home_visited, ies_challenge_click,                                                              ies_music_click, location_click, message_action,                                                              click_landing_page, click_shopwindow, click_website,                                                              click_download, click_call_dy, submit_certification_count,                                                              approval_count, first_rental_order_count,                                                              first_order_count, commute_first_pay_count,                                                              attribution_active_pay_intra_one_day_roi,attribution_active_pay_intra_one_day_amount,attribution_active_pay_intra_one_day_rate,attribution_active_pay_intra_one_day_cost,attribution_active_pay_intra_one_day_count,stat_pay_amount,inventory,inventory_name)select a.advertiser_id account_id,       a.stat_datetime,       b.project_id,       b.product_id,       b.advertiser_id,       b.company_id,       b.account_name,       b.project_name,       b.advertiser_name,       b.company_name,       b.sale_id,       b.user_id,       b.org_code,       a.cost,       a.show,       a.avg_show_cost,       a.click,       a.avg_click_cost,       a.ctr,       a.convert,       a.convert_cost,       a.convert_rate,       a.deep_convert,       a.deep_convert_cost,       a.deep_convert_rate,       a.attribution_convert,       a.attribution_convert_cost,       a.attribution_deep_convert,       a.attribution_deep_convert_cost,       a.download_start,       a.download_start_cost,       a.download_start_rate,       a.download_finish,       a.download_finish_cost,       a.download_finish_rate,       a.click_install,       a.install_finish,       a.install_finish_cost,       a.install_finish_rate,       a.active,       a.active_cost,       a.active_rate,       a.register,       a.active_register_cost,       a.active_register_rate,       a.next_day_open,       a.next_day_open_cost,       a.next_day_open_rate,       a.attribution_next_day_open_cnt,       a.attribution_next_day_open_cost,       a.attribution_next_day_open_rate,       a.game_addiction,       a.game_addiction_cost,       a.game_addiction_rate,       a.pay_count,       a.active_pay_cost,       a.active_pay_rate,       a.loan_completion,       a.loan_completion_cost,       a.loan_completion_rate,       a.pre_loan_credit,       a.pre_loan_credit_cost,       a.loan_credit,       a.loan_credit_cost,       a.loan_credit_rate,       a.in_app_uv,       a.in_app_detail_uv,       a.in_app_cart,       a.in_app_pay,       a.in_app_order,       a.attribution_game_pay_7d_count,       a.attribution_game_pay_7d_cost,       a.attribution_active_pay_7d_per_count,       a.game_pay_cost,       a.game_pay_count,       a.phone,       a.form,       a.map_search,       a.button,       a.view,       a.download,       a.qq,       a.lottery,       a.vote,       a.message,       a.redirect,       a.shopping,       a.consult,       a.wechat,       a.phone_confirm,       a.phone_connect,       a.consult_effective,       a.coupon,       a.coupon_single_page,       a.redirect_to_shop,       a.poi_collect,       a.poi_address_click,       a.luban_order_cnt,       a.luban_order_stat_amount,       a.luban_order_roi,       a.luban_live_enter_cnt,       a.live_watch_one_minute_count,       a.luban_live_follow_cnt,       a.live_fans_club_join_cnt,       a.luban_live_comment_cnt,       a.luban_live_share_cnt,       a.luban_live_gift_cnt,       a.luban_live_gift_amount,       a.luban_live_slidecart_click_cnt,       a.luban_live_click_product_cnt,       a.luban_live_pay_order_count,       a.luban_live_pay_order_stat_cost,       a.live_component_click_count,       a.live_component_click_cost,       a.live_component_click_rate,       a.wechat_login_count,       a.attribution_wechat_login_30d_count,       a.wechat_login_cost,       a.attribution_wechat_login_30d_cost,       a.wechat_first_pay_count,       a.attribution_wechat_first_pay_30d_count,       a.wechat_first_pay_cost,       a.attribution_wechat_first_pay_30d_cost,       a.wechat_first_pay_rate,       a.attribution_wechat_first_pay_30d_rate,       a.wechat_pay_amount,       a.attribution_wechat_pay_30d_amount,       a.attribution_wechat_pay_30d_roi,       a.phone_effective,       a.total_play,       a.valid_play,       a.valid_play_cost,       a.valid_play_rate,       a.play_25_feed_break,       a.play_50_feed_break,       a.play_75_feed_break,       a.play_100_feed_break,       a.average_play_time_per_play,       a.play_over_rate,       a.wifi_play_rate,       a.wifi_play,       a.play_duration_sum,       a.advanced_creative_phone_click,       a.advanced_creative_counsel_click,       a.advanced_creative_form_click,       a.advanced_creative_coupon_addition,       a.advanced_creative_form_submit,       a.card_show,       a.share,       a.comment,       a.like,       a.follow,       a.home_visited,       a.ies_challenge_click,       a.ies_music_click,       a.location_click,       a.message_action,       a.click_landing_page,       a.click_shopwindow,       a.click_website,       a.click_download,       a.click_call_dy,       a.submit_certification_count,       a.approval_count,       a.first_rental_order_count,       a.first_order_count,       a.commute_first_pay_count,              a.attribution_active_pay_intra_one_day_roi,a.attribution_active_pay_intra_one_day_amount,a.attribution_active_pay_intra_one_day_rate,a.attribution_active_pay_intra_one_day_cost,attribution_active_pay_intra_one_day_count,a.stat_pay_amount,inventory,inventory_namefrom `media_api`.bytedance_advertiser_inventory_report_daily a         left join     (select cua.account_id  id,             cua.account_id  account_id,             cp.id           project_id,             product.id      product_id,             ca.id           advertiser_id,             uc.company_id   company_id,             cua.auth_name   account_name,             cp.project_name project_name,             ca.name         advertiser_name,             uc.company_name company_name,             cp.sale_id      sale_id,             su.id           user_id,             su.org_code     org_code      from `jeecg-boot`.ctop_user_allocation cua               left join `jeecg-boot`.ctop_project cp on cua.project_id = cp.id               left join `jeecg-boot`.ctop_advertiser ca on ca.id = cua.advertiser_id               left join `jeecg-boot`.sys_user su on su.id = cua.user_id               left join `jeecg-boot`.user_company uc on uc.user_id = cua.user_id               left join `jeecg-boot`.ctop_product product on product.id = cp.product_id      where cua.account_id is not null) b     on a.advertiser_id = b.account_idwhere a.stat_datetime between {start_time} and {end_time}order by account_id limit {num},20000;        """.format(start_time=self.start_time, end_time=self.end_time, num=i * 20000)                print(sql)                TidbConn.upsert(conn=self.conn, sql_buff=sql)                self.conn.commit()            TidbConn.conn_close(conn=self.conn)            return 0        except Exception as e:            logging.error(e)            return -1if __name__ == '__main__':    status = AdvertiserDailyInventoryReport().daily_report()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |