1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071 |
- # Author renyupeng
- # coding=utf-8
- # @Time : 2024/7/8 15:54
- # @Site :
- # @File : ItermOrderAmountThread.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 ItermOrderAmountThread:
- 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}.item_oerder_amount
- select
- a.item_id, valid_order_num, valid_amount,b.30day_order_num,b.30day_gmv,item_first_order_time
- from
- (select item_id, count(distinct oid) as valid_order_num, sum(order_amount) as valid_amount,min(stat_date) as item_first_order_time
- from {database}.kuaishou_supply_chain
- where order_status != 80 and item_id={item_id}
- group by item_id)a
- left join (
- select item_id, count(distinct oid) 30day_order_num, sum(order_amount) as 30day_gmv
- from {database}.kuaxishou_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 item_id
- )b
- on a.item_id=b.item_id
- """.format(item_id=item_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','yuxiu'}
- for database in databases:
- ItermOrderAmountThread().KuaishouRuleOrderDataHandler(database=database)
|