| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115 | 
							- # Author renyupeng
 
- # coding=utf-8
 
- # @Time    : 2024/7/8 15:54
 
- # @Site    : 
 
- # @File    : KuaishouRuleOrderData.py
 
- # @Software: PyCharm
 
- # @contact: renyupeng@c-top.com.cn
 
- # @Tel 1501435553
 
- import datetime
 
- import threading
 
- from time import sleep
 
- import numpy as np
 
- from utils.mysql_utils_pro import MysqlProUtils
 
- class KuaishouRuleOrderData:
 
-     def __init__(self):
 
-         self.stat_date = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y%m%d")
 
-         self.date = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y%m%d")
 
-     def KuaishouRuleOrderDataHandler(self, database):
 
-         sql = """
 
-         select promoter_id from {database}.kuaishou_supply_chain where stat_date='{date}'
 
- group by promoter_id
 
-         """.format(date=self.date, database=database)
 
-         results = MysqlProUtils().QueryAll(sql=sql)
 
-         numlist = self.divide_data(results, 100)
 
-         threads = []
 
-         for list in numlist:
 
-             t = threading.Thread(target=self.Theard_hander, args=(list, database))
 
-             threads.append(t)
 
-             t.start()
 
-         for t in threads:
 
-             t.join()
 
-     def divide_data(self, data, num):
 
-         length = len(data) // num
 
-         divide_data = [data[i:i + length] for i in range(0, len(data), length)]
 
-         return divide_data
 
-     def Theard_hander(self, lists, database):
 
-         print(lists)
 
-         for promoter_id in lists:
 
-             promoter_id = promoter_id[0].decode("utf-8")
 
-             handler_sql = """
 
-                             replace into {database}.kuaishou_rule_order_data(relate_id, item_id, item_title, promoter_id, promoter_name,
 
-                                                               item_create_id, item_create_name, promoter_create_id,
 
-                                                               promoter_create_name, order_num, valid_order_num, yesterday_order_num,
 
-                                                               yesterday_valid_order_num, stat_date)
 
-                 select relate_id,
 
-                        a.item_id,
 
-                        ifnull(b.item_title, d.item_title),
 
-                        a.promoter_id,
 
-                        promoter_nick_name,
 
-                        ifnull(item_create_id, user_id)     as item_create_id,
 
-                        ifnull(item_create_name, user_name) as item_create_name,
 
-                        collect_sample_id                   as promoter_create_id,
 
-                        collect_sample_name                 as promoter_create_name,
 
-                        order_count                         as order_num,
 
-                        a.valid_order_num,
 
-                        e.order_num                         as yesterday_order_num,
 
-                        e.valid_order_num                   as yesterday_order_num,
 
-                        {stat_date}                      as stat_date
 
-                 from (select promoter_id,
 
-                              item_id,
 
-                              count(distinct oid) as order_count,
 
-                            count(distinct case when order_status!=80 then oid  end) as valid_order_num
 
-                       from {database}.kuaishou_supply_chain
 
-                       where stat_date <= {stat_date}
 
-                         and promoter_id = '{promoter_id}'
 
-                       group by promoter_id, item_id) a
 
-                          left join
 
-                      (select item_id,
 
-                              item_title,
 
-                              promoter_id,
 
-                              promoter_nick_name,
 
-                              item_create_id,
 
-                              item_create_name,
 
-                              collect_sample_id,
 
-                              collect_sample_name,
 
-                              create_time
 
-                       from {database}.kuaishou_item_collect_samples
 
-                       where promoter_id = '{promoter_id}') b
 
-                      on a.promoter_id = b.promoter_id and a.item_id = b.item_id
 
-                          left join (select item_id, relate_id, user_id, user_name, item_title
 
-                                     from {database}.kuaishou_item_list
 
-                                     ) d
 
-                                    on a.item_id = d.item_id
 
-                          left join (select promoter_id,
 
-                              item_id,
 
-                              count(distinct oid) as order_num,
 
-                            count(distinct case when order_status!=80 then oid  end) as valid_order_num
 
-                       from {database}.kuaishou_supply_chain
 
-                       where stat_date <= date_format(date_sub('{stat_date}', interval 1 day), '%Y%m%d')
 
-                         and promoter_id = '{promoter_id}'
 
-                       group by promoter_id, item_id) e
 
-                                    on a.promoter_id = e.promoter_id and a.item_id = e.item_id
 
-                             """.format(stat_date=self.stat_date, promoter_id=promoter_id, database=database)
 
-             try:
 
-                 print(handler_sql)
 
-                 MysqlProUtils().Operate(sql=handler_sql)
 
-                 sleep(1)
 
-             except Exception as e:
 
-                 print(e)
 
-                 continue
 
- if __name__ == '__main__':
 
-     databases = {'ruixuan', 'yufu', 'miaogousi', 'rocket','yuxiu'}
 
-     for database in databases:
 
-         KuaishouRuleOrderData().KuaishouRuleOrderDataHandler(database=database)
 
 
  |