# 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)