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'}
- for database in databases:
- KuaishouRuleOrderData().KuaishouRuleOrderDataHandler(database=database)
|