1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071 |
- # Author renyupeng
- # coding=utf-8
- # @Time : 2024/7/8 15:54
- # @Site :
- # @File : PromoterOrderAmountThread.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 PromoterOrderAmountThread:
- 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)
- 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:
- promoter_id = result[0].decode('utf-8')
- handler_sql = """
- replace into {database}.promoter_oerder_amount
- select
- a.promoter_id, valid_order_num, valid_amount,b.30day_order_num,b.30day_gmv
- from
- (select promoter_id, count(distinct oid) as valid_order_num, sum(order_amount) as valid_amount
- from {database}.kuaishou_supply_chain
- where order_status != 80 and promoter_id={promoter_id}
- group by promoter_id)a
- left join (
- select promoter_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 promoter_id={promoter_id}
- group by promoter_id
- )b
- on a.promoter_id=b.promoter_id;
- """.format(promoter_id=promoter_id, stat_date=self.stat_date, 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'}
- for database in databases:
- PromoterOrderAmountThread().KuaishouRuleOrderDataHandler(database=database)
|