PromoterOrderAmountThread.py 2.5 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2024/7/8 15:54
  4. # @Site :
  5. # @File : PromoterOrderAmountThread.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. # 达人订单
  10. import datetime
  11. import threading
  12. from time import sleep
  13. from utils.mysql_utils_pro import MysqlProUtils
  14. class PromoterOrderAmountThread:
  15. def __init__(self):
  16. self.stat_date = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y%m%d")
  17. self.date = (datetime.date.today() + datetime.timedelta(-1)).strftime("%Y%m%d")
  18. def KuaishouRuleOrderDataHandler(self, database):
  19. sql = """
  20. select promoter_id from {database}.kuaishou_supply_chain where stat_date='{date}'
  21. group by promoter_id
  22. """.format(date=self.date, database=database)
  23. results = MysqlProUtils().QueryAll(sql=sql)
  24. items_per_thread = len(results)
  25. threads = []
  26. for i in range(10):
  27. start = i * items_per_thread
  28. end = start + items_per_thread
  29. thread = threading.Thread(target=self.Theard_hander, args=(results[start:end], database))
  30. threads.append(thread)
  31. thread.start()
  32. for thread in threads:
  33. thread.join()
  34. def Theard_hander(self, data, database):
  35. for result in data:
  36. promoter_id = result[0].decode('utf-8')
  37. handler_sql = """
  38. replace into {database}.promoter_oerder_amount
  39. select
  40. a.promoter_id, valid_order_num, valid_amount,b.30day_order_num,b.30day_gmv
  41. from
  42. (select promoter_id, count(distinct oid) as valid_order_num, sum(order_amount) as valid_amount
  43. from {database}.kuaishou_supply_chain
  44. where order_status != 80 and promoter_id='{promoter_id}'
  45. group by promoter_id)a
  46. left join (
  47. select promoter_id, count(distinct oid) 30day_order_num, sum(order_amount) as 30day_gmv
  48. from {database}.kuaishou_supply_chain
  49. where order_status != 80 and stat_date>date_format(date_sub('{stat_date}',interval 30 day),'%Y%m%d')
  50. and promoter_id='{promoter_id}'
  51. group by promoter_id
  52. )b
  53. on a.promoter_id=b.promoter_id;
  54. """.format(promoter_id=promoter_id, stat_date=self.stat_date, database=database)
  55. try:
  56. print(handler_sql)
  57. MysqlProUtils().Operate(sql=handler_sql)
  58. except Exception as e:
  59. print(e)
  60. continue
  61. if __name__ == '__main__':
  62. databases = {'rocket', 'miaogousi', 'yufu', 'ruixuan','yuxiu'}
  63. for database in databases:
  64. PromoterOrderAmountThread().KuaishouRuleOrderDataHandler(database=database)