KuaishouRuleOrderDataTherad.py 5.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2024/7/8 15:54
  4. # @Site :
  5. # @File : KuaishouRuleOrderData.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import datetime
  10. import threading
  11. from time import sleep
  12. import numpy as np
  13. from utils.mysql_utils_pro import MysqlProUtils
  14. class KuaishouRuleOrderData:
  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. numlist = self.divide_data(results, 100)
  25. threads = []
  26. for list in numlist:
  27. t = threading.Thread(target=self.Theard_hander, args=(list, database))
  28. threads.append(t)
  29. t.start()
  30. for t in threads:
  31. t.join()
  32. def divide_data(self, data, num):
  33. length = len(data) // num
  34. divide_data = [data[i:i + length] for i in range(0, len(data), length)]
  35. return divide_data
  36. def Theard_hander(self, lists, database):
  37. print(lists)
  38. for promoter_id in lists:
  39. promoter_id = promoter_id[0].decode("utf-8")
  40. handler_sql = """
  41. replace into {database}.kuaishou_rule_order_data(relate_id, item_id, item_title, promoter_id, promoter_name,
  42. item_create_id, item_create_name, promoter_create_id,
  43. promoter_create_name, order_num, valid_order_num, yesterday_order_num,
  44. yesterday_valid_order_num, stat_date)
  45. select relate_id,
  46. a.item_id,
  47. ifnull(b.item_title, d.item_title),
  48. a.promoter_id,
  49. promoter_nick_name,
  50. ifnull(item_create_id, user_id) as item_create_id,
  51. ifnull(item_create_name, user_name) as item_create_name,
  52. collect_sample_id as promoter_create_id,
  53. collect_sample_name as promoter_create_name,
  54. order_count as order_num,
  55. a.valid_order_num,
  56. e.order_num as yesterday_order_num,
  57. e.valid_order_num as yesterday_order_num,
  58. {stat_date} as stat_date
  59. from (select promoter_id,
  60. item_id,
  61. count(distinct oid) as order_count,
  62. count(distinct case when order_status!=80 then oid end) as valid_order_num
  63. from {database}.kuaishou_supply_chain
  64. where stat_date <= {stat_date}
  65. and promoter_id = '{promoter_id}'
  66. group by promoter_id, item_id) a
  67. left join
  68. (select item_id,
  69. item_title,
  70. promoter_id,
  71. promoter_nick_name,
  72. item_create_id,
  73. item_create_name,
  74. collect_sample_id,
  75. collect_sample_name,
  76. create_time
  77. from {database}.kuaishou_item_collect_samples
  78. where promoter_id = '{promoter_id}') b
  79. on a.promoter_id = b.promoter_id and a.item_id = b.item_id
  80. left join (select item_id, relate_id, user_id, user_name, item_title
  81. from {database}.kuaishou_item_list
  82. ) d
  83. on a.item_id = d.item_id
  84. left join (select promoter_id,
  85. item_id,
  86. count(distinct oid) as order_num,
  87. count(distinct case when order_status!=80 then oid end) as valid_order_num
  88. from {database}.kuaishou_supply_chain
  89. where stat_date <= date_format(date_sub('{stat_date}', interval 1 day), '%Y%m%d')
  90. and promoter_id = '{promoter_id}'
  91. group by promoter_id, item_id) e
  92. on a.promoter_id = e.promoter_id and a.item_id = e.item_id
  93. """.format(stat_date=self.stat_date, promoter_id=promoter_id, database=database)
  94. try:
  95. print(handler_sql)
  96. MysqlProUtils().Operate(sql=handler_sql)
  97. sleep(1)
  98. except Exception as e:
  99. print(e)
  100. continue
  101. if __name__ == '__main__':
  102. databases = {'ruixuan', 'yufu', 'miaogousi', 'rocket','yuxiu'}
  103. for database in databases:
  104. KuaishouRuleOrderData().KuaishouRuleOrderDataHandler(database=database)