commonFunc.py 8.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222
  1. from sqlalchemy import create_engine
  2. from urllib import parse
  3. import pandas as pd
  4. from utils.code_dict import age_dict
  5. import yaml
  6. def update_dict(x, y):
  7. # 用字典y,来更新x
  8. # key 存在则更新,不存在则新增
  9. x.update(y)
  10. return x
  11. def get_db_engine(db_info):
  12. db_con_str = 'mysql+pymysql://%s:%s@%s:%d/%s' % \
  13. (db_info['username'],
  14. parse.quote_plus(db_info['password']),
  15. db_info['host'],
  16. db_info['port'],
  17. db_info['database'])
  18. engine = create_engine(db_con_str, connect_args={'charset': 'utf8'})
  19. return engine
  20. def is_contains_region(val1, val2):
  21. """
  22. level1
  23. region long[] 可选 地域 传值为[]表示不限;传递上一级ID时,childrenID可以不传;不允许同时传parentID和childrenID;
  24. 仅计划的campaign_type为5时,支持设置三级地域(例:山西-大同-左云,左云是三级地域)
  25. :param val1: 推荐的城市等级组合,如 "一线城市|新一线城市|二线城市|三线城市" , "不限"
  26. :param val2: 从账户配置信息表里读取的地区信息, 如 '[]', None, '[14,15,21,22,23,31,32]'
  27. :return:
  28. """
  29. # 如果val1为空,表示该字段没有参与定向组合运算,就直接取客户投放策略里面的值
  30. if val1 is None:
  31. return True, {'region': val2}
  32. val_split = val1.split('|')
  33. val_tuple = tuple(val_split*2) if len(val_split) == 1 else tuple(val_split)
  34. sql = """
  35. select t1.city_name, t2.level, t2.region_id, t2.parent from ctop_kuaishou_city_level t1
  36. left join ctop_kuaishou_region_list_parent t2
  37. on t1.city_name = t2.name
  38. where t1.city_level in %s
  39. """ % (val_tuple,)
  40. with open('/data/pythonProject/ai_target/config/config.yaml', mode='r', encoding='utf-8') as f:
  41. config = yaml.load(f.read(), Loader=yaml.FullLoader)
  42. city_df = pd.read_sql(sql, get_db_engine(config['jeecg_product_db']))
  43. # 如果【吉林】在city_level里面的话,在ctop_kuaishou_region_list_parent会同时把 吉林省和吉林市过滤出来
  44. # 按level进行降序排列后,按city_name进行去重,保留低等级的值
  45. city_df = city_df.sort_values(by='level', ascending=False)
  46. city_df.drop_duplicates(subset=['city_name'], keep='first', inplace=True)
  47. if not val2 or val2 == '不限' or val2 == '[]':
  48. return True, {'region': list(city_df['region_id'].values)}
  49. else:
  50. if val1 == '不限':
  51. return False, -1
  52. else:
  53. val = []
  54. # level1
  55. if set(city_df[city_df.level == 1].region_id.values).issubset(set(eval(val2))):
  56. val.extend(city_df[city_df.level == 1].region_id.values)
  57. else:
  58. return False, -1
  59. # level2
  60. # 元素的父级是否存在于 val2,如果存在则为包含于的关系
  61. # 元素本身是否存在于 val2 ,如果存在则为包含于的关系
  62. # 以上都不满足,val1 不包含于 val2
  63. for item in city_df[city_df.level == 2].region_id.values:
  64. parent = city_df[city_df.region_id == item].parent.values[0]
  65. if parent in eval(val2):
  66. val.append(item)
  67. elif item in eval(val2):
  68. val.append(item)
  69. else:
  70. return False, -1
  71. # level3
  72. for item in city_df[city_df.level == 3].region_id.values:
  73. parent = city_df[city_df.region_id == item].parent.values[0]
  74. if parent in eval(val2):
  75. val.append(item)
  76. elif item in eval(val2):
  77. val.append(item)
  78. else:
  79. return False, -1
  80. return True, {'region': val}
  81. def is_contains_gender(val1, val2):
  82. """
  83. :param val1: ctop_ai_kuaishou_signature_recommended_target_combine 中的 gender 字段: '男'、'女'、'不限'、None
  84. :param val2: ctop_ai_kuaishou_advertiser_strategy 中的 gender 字段: 1:女性, 2:男性,0表示不限
  85. :return: val1 是否为 val2的子集,以及传递给快手后台的值
  86. """
  87. if val1 is None:
  88. # 表示该字段没有参与贝叶斯的计算, 直接返回客户投放策略里的值
  89. return True, {'gender': val2}
  90. if val1 == '男' and val2 in (2, 0):
  91. return True, {'gender': 2}
  92. if val1 == '女' and val2 in (1, 0):
  93. return True, {'gender': 1}
  94. if val1 == '不限' and val2 == 0:
  95. return True, {'gender': 0}
  96. def is_contains_age(val1, val2_min, val2_max, val2_range):
  97. """
  98. :param val1: 推荐的年龄等级组合,如 "31-40岁|41-49岁|50+岁" , "不限"
  99. age struct 可选 自定义年龄段 不传值表示不限,传值具体见下方表格;与ages_range不能同时传
  100. min int 必填 年龄最小限制 年龄区间最小为18岁
  101. max int 必填 年龄最大限制 年龄区间最大为55岁,且年龄最大限制须大于等于年龄最小限制
  102. :param val2_min: age_min
  103. :param val2_max: age_max
  104. :param val2_range: ctop_ai_kuaishou_advertiser_strategy 中的 ages_range [] varchar
  105. 与age不能同时传;【18:表示18-23岁】;【24:表示24-30岁】;【31:表示31-40岁】;【41:表示41-49岁】;【50:表示50-100岁】
  106. :return:
  107. """
  108. # val2_range 为字符串类型,修改为 list 类型
  109. if val2_range == '[]':
  110. val2_range = None
  111. if val2_range:
  112. val2_range = eval(val2_range)
  113. # 如果val1为空,表示该字段没有参与定向组合运算,就直接取客户投放策略里面的值
  114. if val1 is None:
  115. return True, {'age_min': val2_min, 'age_max': val2_max, 'ages_range': val2_range}
  116. if val1 == '不限':
  117. if (not val2_min) and (not val2_max) and (not val2_range or val2_range == '不限'):
  118. return True, {'age_min': val2_min, 'age_max': val2_max, 'ages_range': val2_range}
  119. else:
  120. return False, -1
  121. else:
  122. val1 = [age_dict[ele] for ele in val1.split('|')]
  123. if (not val2_min) and (not val2_max) and (not val2_range or val2_range == '不限'):
  124. return True, {'ages_range': val1}
  125. if (val2_min is None and val2_max is None) and val2_range:
  126. if set(val1).issubset(set(val2_range)):
  127. return True, {'ages_range': val1}
  128. else:
  129. return False, -1
  130. if (val2_min and val2_max) and (not val2_range):
  131. if val1[0] >= val2_min and val1[-1] <= val2_max:
  132. return True, {'age_min': val1[0], 'age_max': val1[-1]}
  133. else:
  134. return False, -1
  135. def age_code_transform(val):
  136. if (val is None) or (val == '不限'):
  137. ages_range = None
  138. else:
  139. ages_range = [age_dict[item] for item in val.split('|')]
  140. return ages_range
  141. def gender_code_transform(val):
  142. if (val is None) or (val == '不限'):
  143. gender = 0
  144. elif val == '女':
  145. gender = 1
  146. else:
  147. gender = 2
  148. return gender
  149. def city_code_transform(val):
  150. if (val is None) or (val == '不限'):
  151. region = None
  152. else:
  153. sql = """select t1.city_name, t2.level, t2.region_id, t2.parent from ctop_kuaishou_city_level t1
  154. left join ctop_kuaishou_region_list_parent t2
  155. on t1.city_name = t2.name
  156. where t1.city_level in %s
  157. """ % (tuple(val.split('|')),)
  158. with open('/data/pythonProject/ai_target/config/config.yaml', mode='r', encoding='utf-8') as f:
  159. config = yaml.load(f.read(), Loader=yaml.FullLoader)
  160. city_df = pd.read_sql(sql, get_db_engine(config['jeecg_product_db']))
  161. # 如果【吉林】在city_level里面的话,在ctop_kuaishou_region_list_parent会同时把 吉林省和吉林市查询出来
  162. # 按level进行降序排列后,按city_name进行去重,保留高等级的值
  163. city_df = city_df.sort_values(by='level', ascending=False)
  164. city_df.drop_duplicates(subset=['city_name'], keep='first', inplace=True)
  165. region = city_df.region_id.values
  166. return region