| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168 | 
							- # Author renyupeng
 
- # coding=utf-8
 
- # @Time    : 2022/2/17 4:13 下午
 
- # @Site    : 
 
- # @File    : KuaishouUnitAudienceProvinceReportDaily.py
 
- # @Software: PyCharm
 
- # @contact: renyupeng@c-top.com.cn
 
- # @Tel 1501435553
 
- import logging
 
- from math import ceil
 
- from Apietl.conn.TidbConn import TidbConn
 
- from Apietl.utlis.Utils import Utils
 
- class KuaishouUnitAudienceProvinceReportDaily:
 
-     def __init__(self):
 
-         self.conn = TidbConn.get_connection()
 
-         self.parm = Utils.get_args()
 
-         self.start_time = self.parm[0]
 
-     def provinceReport(self):
 
-         try:
 
-             sql = """select count(1) from media_api.kuaishou_unit_audience_province_report_daily where 
 
-             stat_date = {start_time}""".format(
 
-                 start_time=self.start_time)
 
-             totalNum = TidbConn.quary(self.conn, sql)[0][0]
 
-             pageCount = ceil(totalNum / 40000)
 
-             for i in range(pageCount):
 
-                 sql = """
 
-                     replace into application.kuaishou_unit_audience_province_report_daily_d
 
- select province.account_id,
 
-        campaign_id,
 
-        province.unit_id,
 
-        event_register_cost,
 
-        province,
 
-        photo_click,
 
-        key_action_cost,
 
-        event_next_day_stay,
 
-        photo_click_ratio,
 
-        photo_click_cost,
 
-        charge,
 
-        event_pay,
 
-        action_cost,
 
-        conversion_cost,
 
-        bclick,
 
-        key_action_ratio,
 
-        play_3s_ratio,
 
-        play_5s_ratio,
 
-        activation,
 
-        event_app_invoked_ratio,
 
-        `show`,
 
-        event_new_user_pay_cost,
 
-        aclick,
 
-        deep_conversion_num,
 
-        action_ratio,
 
-        conversion_ratio,
 
-        event_pay_purchase_amount_first_day,
 
-        event_pay_first_day,
 
-        conversion_num_cost,
 
-        event_new_user_pay_ratio,
 
-        event_app_invoked,
 
-        event_next_day_stay_ratio,
 
-        event_register,
 
-        event_new_user_pay,
 
-        event_next_day_stay_cost,
 
-        deep_conversion_ratio,
 
-        deep_conversion_cost,
 
-        `like`,
 
-        event_app_invoked_cost,
 
-        key_action,
 
-        ad_photo_played_10s,
 
-        form_cost,
 
-        event_pay_purchase_amount,
 
-        click_1k_cost,
 
-        impression_1k_cost,
 
-        conversion_num,
 
-        stat_date,
 
-        ad_photo_played_2s_ratio,
 
-        follow,
 
-        signature,
 
-        project_id,
 
-        project_name
 
- from (select account_id,
 
-              campaign_id,
 
-              unit_id,
 
-              event_register_cost,
 
-              province,
 
-              photo_click,
 
-              key_action_cost,
 
-              event_next_day_stay,
 
-              photo_click_ratio,
 
-              photo_click_cost,
 
-              charge,
 
-              event_pay,
 
-              action_cost,
 
-              conversion_cost,
 
-              bclick,
 
-              key_action_ratio,
 
-              play_3s_ratio,
 
-              play_5s_ratio,
 
-              activation,
 
-              event_app_invoked_ratio,
 
-              `show`,
 
-              event_new_user_pay_cost,
 
-              aclick,
 
-              deep_conversion_num,
 
-              action_ratio,
 
-              conversion_ratio,
 
-              event_pay_purchase_amount_first_day,
 
-              event_pay_first_day,
 
-              conversion_num_cost,
 
-              event_new_user_pay_ratio,
 
-              event_app_invoked,
 
-              event_next_day_stay_ratio,
 
-              event_register,
 
-              event_new_user_pay,
 
-              event_next_day_stay_cost,
 
-              deep_conversion_ratio,
 
-              deep_conversion_cost,
 
-              `like`,
 
-              event_app_invoked_cost,
 
-              key_action,
 
-              ad_photo_played_10s,
 
-              form_cost,
 
-              event_pay_purchase_amount,
 
-              click_1k_cost,
 
-              impression_1k_cost,
 
-              conversion_num,
 
-              stat_date,
 
-              ad_photo_played_2s_ratio,
 
-              follow
 
-       from media_api.kuaishou_unit_audience_province_report_daily
 
-       where stat_date = {stat_date}) province
 
-          inner join
 
-      (select account_id, unit_id, photo_id
 
-       from `jeecg-boot`.ctop_ai_kuaishou_creative_level_operation_record
 
-       group by account_id, unit_id, photo_id) operation
 
-      on province.account_id = operation.account_id and province.unit_id = operation.unit_id
 
-          inner join
 
-      (select account_id, signature, photo_id
 
-       from media_api.kuaishou_video_list
 
-       group by account_id, signature, photo_id) video
 
-      on operation.account_id = video.account_id and operation.photo_id = video.photo_id
 
-          left join (
 
-     select account_id, project_id
 
-     from `jeecg-boot`.ctop_user_allocation
 
- ) acc_info
 
-                    on province.account_id = acc_info.account_id
 
-          left join (
 
-     select id, project_name
 
-     from `jeecg-boot`.ctop_project
 
- ) pro
 
-                    on acc_info.project_id = pro.id
 
-  order by unit_id limit {num},20000        
 
-                     """.format(stat_date=self.start_time, num=i * 40000)
 
-                 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 -1
 
- if __name__ == '__main__':
 
-     KuaishouUnitAudienceProvinceReportDaily().provinceReport()
 
 
  |