# Author renyupeng # coding=utf-8 # @Time : 2024/7/8 15:54 # @Site : # @File : PromoterItermOrderAmountThread.py # @Software: PyCharm # @contact: renyupeng@c-top.com.cn # @Tel 1501435553 # 达人商品订单 import datetime import threading from time import sleep from utils.mysql_utils_pro import MysqlProUtils class PromoterItermOrderAmountThread: 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 item_id from {database}.kuaishou_supply_chain where stat_date='{date}' group by item_id """.format(date=self.date, database=database) results = MysqlProUtils().QueryAll(sql=sql) items_per_thread = len(results) threads = [] for i in range(10): start = i * items_per_thread end = start + items_per_thread thread = threading.Thread(target=self.Theard_hander, args=(results[start:end], database)) threads.append(thread) thread.start() for thread in threads: thread.join() def Theard_hander(self, data, database): for result in data: item_id = result[0] handler_sql = """ replace into {database}.promoter_iterm_oerder_amount select a.promoter_id,a.item_id, valid_order_num, valid_amount,b.30day_order_num,b.30day_gmv from (select promoter_id,item_id, count(distinct oid) as valid_order_num, sum(order_amount) as valid_amount from {database}.kuaishou_supply_chain where order_status != 80 and item_id={item_id} group by promoter_id,item_id)a left join ( select promoter_id,item_id, count(distinct oid) 30day_order_num, sum(order_amount) as 30day_gmv from {database}.kuaishou_supply_chain where order_status != 80 and stat_date>date_format(date_sub(${stat_date},interval 30 day),'%Y%m%d') and item_id={item_id} group by promoter_id,item_id )b on a.item_id=b.item_id and a.promoter_id=b.promoter_id """.format(stat_date=self.stat_date, item_id=item_id, database=database) try: print(handler_sql) MysqlProUtils().Operate(sql=handler_sql) except Exception as e: print(e) continue if __name__ == '__main__': databases = {'rocket', 'miaogousi', 'yufu', 'ruixuan','yuxiu'} for database in databases: PromoterItermOrderAmountThread().KuaishouRuleOrderDataHandler(database=database)