BayesCombine.py 8.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226
  1. import yaml
  2. import pandas as pd
  3. from sqlalchemy import create_engine
  4. from urllib import parse
  5. from commonFunc import update_dict
  6. from functools import reduce
  7. from itertools import product
  8. from datetime import datetime
  9. class BayesFeatures(object):
  10. """
  11. 计算素材单维度的特征值
  12. """
  13. def __init__(self, signature, target_type, dim, dim_config):
  14. self.signature = signature
  15. self.target_type = target_type
  16. self.dim = dim
  17. self.dim_config = dim_config
  18. self.table = self.dim_config['table']
  19. self.file_name = self.dim_config['fieldName']
  20. self.dim_lst = self.dim_config['Lst']
  21. self.window_size = self.dim_config['windowSize']
  22. self.window_combine = []
  23. self.bayes_feature = {}
  24. def get_window_combine(self):
  25. """
  26. 根据size得到指定维度的滑窗组合。
  27. 如 age: ['18-23岁', '24-30岁','31-40岁',....] 和 size = 2
  28. 得到 ['18-23岁', '24-30岁'],['24-30岁','31-40岁'],......
  29. """
  30. start = 0
  31. while (start < len(self.dim_lst)) and (start + self.window_size <= len(self.dim_lst)):
  32. self.window_combine.append(self.dim_lst[start: start + self.window_size])
  33. start += 1
  34. # 添加一个全部的组合,等同于'不限'
  35. self.window_combine.append(['不限'])
  36. def get_bayes_feature(self):
  37. # 1、获取指定素材在指定维度下的人群数据(近一个月内的数据表现)
  38. sql = '''
  39. select signature,
  40. %s,
  41. sum(aclick) aclick,
  42. sum(bclick) bclick,
  43. sum(activation) activation
  44. from %s
  45. where signature = '%s'
  46. -- and datediff(now(),stat_date)<=30
  47. group by signature, %s
  48. ''' % (self.file_name, self.table, self.signature, self.file_name)
  49. df = pd.read_sql(sql, test_engine)
  50. if self.dim != 'city':
  51. if self.target_type == 'action_ratio':
  52. # 计算行为率(bclick/aclick)的组合特征值
  53. df['unbclick'] = df['aclick'] - df['bclick']
  54. sig_pos_pct = df['bclick'].sum() / df['aclick'].sum() # 素材曝光-点击的概率
  55. sig_neg_pct = 1 - sig_pos_pct # 素材曝光-未点击的概率
  56. self.bayes_feature['sig_pos_pct'] = sig_pos_pct
  57. self.bayes_feature['sig_neg_pct'] = sig_neg_pct
  58. for sub_combine in self.window_combine:
  59. if sub_combine != ['不限']:
  60. key = self.dim + '_' + '|'.join(sub_combine)
  61. pos_pct = df[df[self.file_name].isin(sub_combine)].bclick.sum() / df.bclick.sum()
  62. neg_pct = df[df[self.file_name].isin(sub_combine)].unbclick.sum() / df.unbclick.sum()
  63. self.bayes_feature[key] = {'pos_pct': pos_pct, 'neg_pct': neg_pct}
  64. else:
  65. self.bayes_feature[self.dim + '_' + '不限'] = {'pos_pct': 1, 'neg_pct': 1}
  66. if self.target_type == 'convert_ratio':
  67. # 计算转化率(activation/bclick)的组合特征值
  68. df['unactivation'] = df['bclick'] - df['activation']
  69. sig_pos_pct = df['activation'].sum() / df['bclick'].sum() # 素材点击-转化的概率
  70. sig_neg_pct = 1 - sig_pos_pct # 素材点击-未转化的概率
  71. self.bayes_feature['sig_pos_pct'] = sig_pos_pct
  72. self.bayes_feature['sig_neg_pct'] = sig_neg_pct
  73. for sub_combine in self.window_combine:
  74. if sub_combine != '不限':
  75. key = self.dim + '_' + '|'.join(sub_combine)
  76. pos_pct = df[df[self.file_name].isin(sub_combine)].activation.sum() / df.activation.sum()
  77. neg_pct = df[df[self.file_name].isin(sub_combine)].unactivation.sum() / df.unactivation.sum()
  78. self.bayes_feature[key] = {'pos_pct': pos_pct, 'neg_pct': neg_pct}
  79. else:
  80. self.bayes_feature[self.dim + '_' + '不限'] = {'pos_pct': 1, 'neg_pct': 1}
  81. elif self.dim == 'city':
  82. pass
  83. else:
  84. pass
  85. class BayesCombine(object):
  86. """
  87. 依据特征值,计算多维度的组合预估值
  88. """
  89. def __init__(self, signature, project_id, target_type, dim_features):
  90. self.signature = signature
  91. self.project_id = project_id
  92. self.target_type = target_type
  93. self.dim_features = dim_features
  94. self.bayes_combine_df = pd.DataFrame()
  95. self.actual_prob = self.dim_features[0]['sig_pos_pct']
  96. def get_bayes_estimate(self):
  97. dim_combine_lst = [[key for key in fea.keys() if key not in ['sig_pos_pct', 'sig_neg_pct']] for fea in self.dim_features]
  98. self.dim_features = reduce(update_dict, self.dim_features)
  99. for ele in product(*dim_combine_lst):
  100. prob_pos = reduce(lambda x, y: self.dim_features[x]['pos_pct'] * self.dim_features[y]['pos_pct'], ele)
  101. prob_neg = reduce(lambda x, y: self.dim_features[x]['neg_pct'] * self.dim_features[y]['neg_pct'], ele)
  102. prob = (prob_pos * self.dim_features['sig_pos_pct']) / (prob_neg * self.dim_features['sig_neg_pct'])
  103. out_dict = dict(zip([e.split('_')[0] for e in ele], [e.split('_')[-1] for e in ele]))
  104. # TODO 调用人群预估覆盖接口,得到该组合的人群覆盖数
  105. out_dict['population_cnt'] = 1000
  106. out_dict['combine_estimate_prob'] = prob
  107. out_dict['actual_prob'] = self.actual_prob
  108. out_dict['signature'] = self.signature
  109. out_dict['project_id'] = self.project_id
  110. out_dict['target_type'] = self.target_type
  111. out_dict['stat_date'] = str(datetime.now().date())
  112. combine = pd.DataFrame([out_dict])
  113. self.bayes_combine_df = self.bayes_combine_df.append(combine, ignore_index=True)
  114. def write_to_db(self):
  115. self.bayes_combine_df.to_sql(name="ctop_ai_kuaishou_signature_recommended_target_combine",
  116. con=test_engine,
  117. if_exists='append',
  118. index=False)
  119. if __name__ == '__main__':
  120. # 1、读取配置文件
  121. with open('config/config.yaml', mode='r', encoding='utf-8') as f:
  122. config = yaml.load(f.read(), Loader=yaml.FullLoader)
  123. project_ids = config['projectId']
  124. target_types = config['targetType']
  125. online_db = config['onlineDB']
  126. test_db = config['testDB']
  127. material_rule = config['materialRule']
  128. bayes_dim = config['bayesDim']
  129. # 2、数据库连接引擎
  130. db_con_str = 'mysql+pymysql://%s:%s@%s:%d/%s' % \
  131. (online_db['username'], parse.quote_plus(online_db['password']), online_db['host'], online_db['port'],
  132. online_db['database'])
  133. engine = create_engine(db_con_str, connect_args={'charset': 'utf8'})
  134. db_con_str = 'mysql+pymysql://%s:%s@%s:%d/%s' % \
  135. (test_db['username'], parse.quote_plus(test_db['password']), test_db['host'], test_db['port'],
  136. test_db['database'])
  137. test_engine = create_engine(db_con_str, connect_args={'charset': 'utf8'})
  138. # 3、 参与定向组合的维度
  139. target_dim = [key for key in bayes_dim.keys() if bayes_dim[key]['isOn']]
  140. # 4、计算贝叶斯组合入库
  141. for project_id in project_ids:
  142. # 4.1 获取指定项目下当前活跃的素材信息, 如近3天内累计激活个数达到50个
  143. sql = '''
  144. select signature from ctop_kuaishou_report_daily_material
  145. where account_id in (select account_id from ctop_user_allocation where project_id = %s)
  146. and datediff(now(),stat_date) <= %s
  147. group by signature
  148. having sum(activation) >= %s
  149. ''' % (project_id, material_rule['days'], material_rule['activation'])
  150. df = pd.read_sql(sql, engine)
  151. signature_lst = df['signature'].values
  152. signature_lst = ['0070efb7557b2a04cf3d4a6f243c3cd8', '03b93728f0d82ea7865c3c7cf632bc1b']
  153. # 4.2 计算指定素材的贝叶斯特征
  154. for sig in signature_lst:
  155. for t_type in target_types:
  156. bayes_feature_lst = []
  157. for dimension in target_dim:
  158. cls = BayesFeatures(sig, t_type, dimension, bayes_dim[dimension])
  159. # 计算滑窗组合
  160. cls.get_window_combine()
  161. cls.get_bayes_feature()
  162. bayes_feature_lst.append(cls.bayes_feature)
  163. # 依据特征值,计算多维度的组合预估值
  164. cls = BayesCombine(sig, project_id, t_type, bayes_feature_lst)
  165. cls.get_bayes_estimate()
  166. cls.write_to_db()