mvp.py 32 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857
  1. from mysql_db import MysqlDB
  2. from excel_util import ExcelUtil
  3. import time
  4. from entity import PeopleInfo
  5. class Mvp:
  6. """
  7. ce mvp 答题数据统计
  8. 城市特例 北京市,上海市, 重庆市,天津市
  9. """
  10. age_dict = {
  11. '00-04年生': '95后',
  12. '05-09年生': '05后',
  13. '50-59年生': '50后',
  14. '60-69年生': '60后',
  15. '70-74年生': '70后',
  16. '75-79年生': '75后',
  17. '80-84年生': '80后',
  18. '85-89年生': '85后',
  19. '90-94年生': '85后',
  20. '95-99年生': '95后'
  21. }
  22. age_list = ['85后', '95后']
  23. city_list = ['上海市', '上海周边']
  24. tag_table = {
  25. '用户画像-审美偏好': ['mvp_crowd_info_aesthetic_preference', 'aesthetic_preference'],
  26. '用户画像-行为兴趣': ['mvp_crowd_info_behavior', 'behavioral_interest'],
  27. '用户画像-观念': ['mvp_crowd_info_consumer_concept', 'consumer_concept'],
  28. '用户画像-社交模式': ['mvp_crowd_info_social_mode', 'social_module'],
  29. '用户画像-行业': ['mvp_crowd_info_trade', 'trade'],
  30. '用户画像-出行方式': ['mvp_crowd_info_trip_mode', 'trip_mode'],
  31. '空间需求图谱-色相': ['mvp_innovate_space_color_prefer', 'color'],
  32. '空间需求图谱-精装关注点': ['mvp_innovate_space_hardcover_focus', 'hardcover_focus'],
  33. '空间需求图谱-色调': ['mvp_innovate_space_hue_prefer', 'hue'],
  34. '空间需求图谱-单品偏好': ['mvp_innovate_space_item_preference', 'item_preference'],
  35. '空间需求图谱-材质': ['mvp_innovate_space_material_prefer', 'material'],
  36. '空间需求图谱-空间特性偏好': ['mvp_innovate_space_space_prefer', 'space_preference'],
  37. '模块分数': ['mvp_crowd_info_module', 'module_name']
  38. }
  39. crowd_info_1 = {
  40. '1973': 'A',
  41. '1974': 'B',
  42. '1975': 'C',
  43. '1976': 'D',
  44. '1977': 'E',
  45. '1978': 'F',
  46. '1979': 'G',
  47. '1813': 'A',
  48. '1814': 'B',
  49. '1815': 'C',
  50. '1816': 'D',
  51. '1817': 'E',
  52. '1818': 'F',
  53. '1819': 'G'
  54. }
  55. base_insert_sql = 'insert into {}(crowd_info_id, {}, standard_value, status) values(%s, %s, %s, ' \
  56. '1) '
  57. def get_table_name(self, name):
  58. """
  59. 获取表名
  60. :param name:
  61. :return:
  62. """
  63. params = self.tag_table.get(name)
  64. if params:
  65. return self.tag_table.get(name)[0]
  66. def get_insert_sql(self, tag_type_name):
  67. """
  68. 根据标签分类名称获取相应表的插入sql
  69. :param tag_type_name:
  70. :return:
  71. """
  72. params = self.tag_table.get(tag_type_name)
  73. if params:
  74. return self.base_insert_sql.format(params[0], params[1])
  75. crowd = ['A', 'B', 'C', 'D', 'E', 'F']
  76. # 获取答题记录中城市列表
  77. sql_1 = 'select city from f_t_daren_score_2 group by city'
  78. # 获取父选项和父题id
  79. sql_2 = 'select a.id, a.content, b.id, b.name from bq_option a left join bq_question b on a.question_id = b.id ' \
  80. 'where a.serial_number = %s and b.serial_number = %s and a.status = b.status = 1 '
  81. # 获取答题人的年龄段集合
  82. sql_4 = 'select nld from f_t_daren_score_2 group by nld'
  83. # 根据城市,年龄段,人群分类统计答题记录数
  84. sql_5 = 'select testcase_id, COUNT(DISTINCT uuid) from f_t_daren_score_2 where uuid in %s group by testcase_id '
  85. # 根据父选项获取子选项id列表
  86. sql_6 = '''
  87. SELECT
  88. c.id,
  89. c.sub_question_id,
  90. c.content
  91. FROM
  92. bq_sub_option c
  93. WHERE
  94. c.father_id IN (
  95. SELECT
  96. a.id
  97. FROM
  98. bq_option a
  99. LEFT JOIN bq_question b ON a.question_id = b.id
  100. WHERE
  101. a.serial_number = % s
  102. AND b.serial_number = % s
  103. AND a. STATUS = 1
  104. AND b. STATUS = 1
  105. )
  106. AND c. STATUS = 1
  107. '''
  108. # 根据子题id获取包含子题id的测试
  109. sql_7 = 'select id from bq_testcase where status = 1 and FIND_IN_SET(%s, question_ids)'
  110. # 根据子选项id统计答题数
  111. sql_8 = '''
  112. SELECT
  113. count(1)
  114. FROM
  115. f_t_daren_score_2 a
  116. LEFT JOIN d_shangju_tiku_02 b ON a.sub_question_id = b.sub_question_id
  117. AND (
  118. a.score = b.score
  119. OR a.score = b.sub_option_id
  120. )
  121. AND a.testcase_id = b.testcase_id
  122. WHERE
  123. b.sub_option_id IN % s
  124. AND a.uuid IN % s
  125. '''
  126. # 获取一个uuid下答题的子选项id列表
  127. sql_10 = 'select DISTINCT uuid, GROUP_CONCAT(DISTINCT b.sub_option_id) from f_t_daren_score_2 a left join ' \
  128. 'd_shangju_tiku_02 b on a.sub_question_id = b.sub_question_id and (a.score = b.score or a.score = ' \
  129. 'b.sub_option_id) where a.status = ' \
  130. 'b.status = 1 group by uuid '
  131. # 向表mvp_crowd_info插入数据
  132. sql_11 = 'insert into mvp_crowd_info(age_area, city_name, crowd_type, status) values(%s, %s, %s, 1)'
  133. # 向表mvp_crowd_info_behavior中插入数据
  134. sql_12 = 'insert into mvp_crowd_info_behavior(crowd_info_id, behavioral_interest, standard_value, status) values(' \
  135. '%s, %s, ' \
  136. '%s, 1) '
  137. # 向表mvp_crowd_info_module中插入数据
  138. sql_13 = 'insert into mvp_crowd_info_module(crowd_info_id, module_name, standard_value, status) values (%s, %s, ' \
  139. '%s, 1) '
  140. sql_14 = 'select a.id, a.age_area, a.city_name, a.crowd_type from mvp_crowd_info a where a.status = 1'
  141. # 获取答题城市信息from city
  142. sql_15 = '''
  143. SELECT
  144. a.uuid,
  145. IFNULL(GROUP_CONCAT(DISTINCT a.city, a.province), 00) AS city,
  146. IFNULL(GROUP_CONCAT(DISTINCT a.nld), 00) AS nld,
  147. IFNULL(GROUP_CONCAT(DISTINCT a.sex), 00) AS sex,
  148. IFNULL(GROUP_CONCAT(DISTINCT b.sub_option_id), 00) as sub_option_ids,
  149. IFNULL(GROUP_CONCAT(DISTINCT a.testcase_id), 00) as testcase_ids
  150. FROM
  151. f_t_daren_score_2 a
  152. LEFT JOIN d_shangju_tiku_02 b ON a.testcase_id = b.testcase_id
  153. WHERE
  154. a.testcase_id = b.testcase_id
  155. AND a.sub_question_id = b.sub_question_id
  156. AND (
  157. a.score = b.score
  158. OR a.score = b.sub_option_id
  159. )
  160. GROUP BY
  161. a.uuid
  162. '''
  163. # 根据用户uuid获取城市信息
  164. sql_16 = '''
  165. SELECT
  166. a.uuid,
  167. b.sub_option_content
  168. FROM
  169. f_t_daren_score_2 a
  170. LEFT JOIN d_shangju_tiku_02 b ON a.testcase_id = b.testcase_id
  171. WHERE
  172. a.sub_question_id = b.sub_question_id
  173. AND (
  174. a.score = b.score
  175. OR a.score = b.sub_option_id
  176. )
  177. AND a.uuid = %s
  178. AND b.father_id = 249
  179. AND a. STATUS = b. STATUS = 1
  180. '''
  181. # 答题人人群分类信息
  182. sql_17 = '''
  183. SELECT
  184. a.uuid,
  185. b.sub_option_id
  186. FROM
  187. f_t_daren_score_2 a
  188. LEFT JOIN d_shangju_tiku_02 b ON a.testcase_id = b.testcase_id
  189. WHERE
  190. a.sub_question_id = b.sub_question_id
  191. AND (
  192. a.score = b.score
  193. OR a.score = b.sub_option_id
  194. )
  195. AND a.uuid = %s
  196. AND b.father_id = 236
  197. AND a.STATUS = b.STATUS = 1
  198. '''
  199. sql_18 = '''
  200. DELETE
  201. FROM
  202. mvp_crowd_info_behavior
  203. WHERE
  204. crowd_info_id not IN (
  205. SELECT
  206. GROUP_CONCAT(id)
  207. FROM
  208. mvp_crowd_info
  209. WHERE
  210. city_name = '上海市'
  211. AND age_area = '85后'
  212. AND STATUS = 1
  213. )
  214. '''
  215. # 根据名称获取图标
  216. sql_19 = '''
  217. SELECT
  218. id,
  219. NAME
  220. FROM
  221. mvp_icon
  222. WHERE status = 1
  223. '''
  224. # 行为更新图标
  225. sql_20 = '''
  226. UPDATE mvp_crowd_info_behavior
  227. SET icon_id = % s
  228. WHERE
  229. behavioral_interest = % s
  230. '''
  231. # 模块图标更新
  232. sql_21 = '''
  233. '''
  234. """
  235. 数据debug SQL
  236. 1:
  237. SELECT
  238. c.id,
  239. c.sub_question_id,
  240. c.content
  241. FROM
  242. bq_sub_option c
  243. WHERE
  244. c.father_id IN (
  245. SELECT
  246. a.id
  247. FROM
  248. bq_option a
  249. LEFT JOIN bq_question b ON a.question_id = b.id
  250. WHERE
  251. a.serial_number ='FA001'
  252. AND b.serial_number = 'F00245'
  253. AND a. STATUS = 1
  254. AND b. STATUS = 1
  255. )
  256. AND c.STATUS = 1
  257. 2:
  258. select id from bq_testcase where status = 1 and FIND_IN_SET(%s, question_ids)
  259. 3:
  260. SELECT
  261. count(1)
  262. FROM
  263. f_t_daren_score_2 a
  264. LEFT JOIN d_shangju_tiku_02 b ON a.sub_question_id = b.sub_question_id
  265. AND (
  266. a.score = b.score
  267. OR a.score = b.sub_option_id
  268. )
  269. AND a.testcase_id = b.testcase_id
  270. WHERE
  271. b.sub_option_id IN (1964,1965,1966,1967,1968,1969,1970,1971,1972)
  272. """
  273. def __init__(self, path=None):
  274. self.shangju_db = MysqlDB('shangju')
  275. self.marketing_db = MysqlDB('bi_report')
  276. # self.shangju_db.truncate('mvp_standard_score')
  277. self.tag_data = ExcelUtil(file_name=path).init_mvp_data()
  278. self.crowd_info = ExcelUtil(file_name=path, sheet_name='选项-人群分类对应表').init_crowd_info()
  279. self.citys = self.init_city()
  280. self.age = self.init_age()
  281. self.people_sub_option_ids = self.marketing_db.select(self.sql_10)
  282. self.crowd_contain_sub_option_ids = self.get_crowd_contain_sub_option_ids()
  283. self.module_scores = ExcelUtil(file_name='set-behavior-tag.xlsx', sheet_name='算法关系表').init_module_info()
  284. # self.scores_tag = ExcelUtil(file_name='行为与模块分值汇总.xlsx', sheet_name='行为').init_scores()
  285. # self.score_module = ExcelUtil(file_name='行为与模块分值汇总.xlsx', sheet_name='模块').init_scores()
  286. self.scores_tag = None
  287. self.score_module = None
  288. def close(self):
  289. self.shangju_db.close()
  290. self.marketing_db.close()
  291. def init_city(self):
  292. """
  293. 获取答题数据中的城市。
  294. :return:
  295. """
  296. citys = ['上海市', '上海周边']
  297. # citys_info = self.marketing_db.select(self.sql_1)
  298. # citys.extend([x[0] for x in citys_info if x[0] is not None])
  299. return citys
  300. def query_behavioral_info(self, city=None, age=None, crowd=None):
  301. """
  302. 查询行为兴趣信息
  303. :return:
  304. """
  305. # datas = []
  306. # for key in self.tag_data.keys():
  307. # values = self.tag_data[key]
  308. # for value in values:
  309. # question = value[0].split('-')[0]
  310. # option = value[0].split('-')[1]
  311. # corr = value[1]
  312. # data = self.shangju_db.select(self.sql_2, [option, question])
  313. # if len(data) > 0:
  314. # print([question, option, data[0][3], data[0][1], key, corr])
  315. # datas.append([question, option, data[0][3], data[0][1], key, corr])
  316. # self.shangju_db.truncate('mvp_question_classification')
  317. # self.shangju_db.add_some(self.sql_3, datas)
  318. scores_behavioral = self.city_age_crowd(city, age, crowd)
  319. # scores_module = self.module_score(crowd, city, age, scores_behavioral['score'])
  320. # result = {'行为兴趣分值': scores_behavioral['score'], '模块分值': scores_module}
  321. print('update finished!!!')
  322. return scores_behavioral
  323. def people_info(self):
  324. """
  325. 答题人个人信息获取
  326. :return:
  327. """
  328. people_info_city = self.marketing_db.select(self.sql_15)
  329. people_infos = []
  330. for people in people_info_city:
  331. uuid = people[0]
  332. city = people[1]
  333. nld = people[2]
  334. sex = people[3]
  335. sub_option_ids_1 = people[4]
  336. testcaseid = people[5]
  337. if str(city).find('市') != -1:
  338. city = str(city).split('市')[0] + '市'
  339. if str(nld).find(',') != -1:
  340. nld_1 = list(str(nld).split(','))
  341. if len(nld_1) > 0:
  342. nld = nld_1[0]
  343. else:
  344. pass
  345. crowd = []
  346. if testcaseid:
  347. testcastids = list(map(int, str(testcaseid).split(',')))
  348. if len(testcastids) > 0:
  349. gt_75 = [x for x in testcastids if x > 74]
  350. if len(gt_75) > 0:
  351. # 从答题结果中获取城市信息
  352. citys = self.marketing_db.select(self.sql_16, [uuid])
  353. if len(citys) > 0:
  354. city = '上海市' if citys[0][1] == '一线' else '上海周边'
  355. # 根据用户子选项id集合,获取用户的人群分类
  356. if len(gt_75) > 0:
  357. # 特定的测试人群分类从答题结果中获取
  358. sub_option_ids = self.marketing_db.select(self.sql_17, [uuid])
  359. for option in sub_option_ids:
  360. crowd_type = self.crowd_info_1.get(option[1])
  361. if crowd_type:
  362. crowd.append(crowd_type)
  363. else:
  364. crowd.append('A')
  365. else:
  366. if sub_option_ids_1 is not None:
  367. crowd.extend(self.get_people_uuid_by_sub_option_ids(sub_option_ids_1))
  368. if city is None:
  369. city = '上海市'
  370. people_info = PeopleInfo(uuid, city, nld, sex, crowd)
  371. people_infos.append(people_info)
  372. # people_infos.append([uuid, city, nld, sex, crowd])
  373. return people_infos
  374. def people_filter(self, city, nld, crowd):
  375. uuids = []
  376. for people in self.people_info_1:
  377. if people.city == city and people.age == nld and crowd in people.crowd:
  378. uuids.append(people.uuid)
  379. return uuids
  380. def get_people_uuid_by_sub_option_ids(self, sub_ids):
  381. types = []
  382. for key in self.crowd_contain_sub_option_ids.keys():
  383. type_sub_option_ids = self.crowd_contain_sub_option_ids[key]
  384. sub_option_ids = list(map(int, str(sub_ids).split(',')))
  385. # list(set(a).intersection(set(b)))
  386. if len(list(set(sub_option_ids).intersection(set(type_sub_option_ids)))) > 0 and key not in types:
  387. types.append(key)
  388. return types
  389. def update_data(self):
  390. """
  391. 定时更新分值
  392. :return:
  393. """
  394. self.insert_table = []
  395. self.linshi_db = MysqlDB('linshi', db_type=1)
  396. for city in self.city_list:
  397. for age in self.age_list:
  398. for crowd in self.crowd:
  399. result = self.city_age_crowd(city, age, crowd)
  400. self.insert_score_to_db(result)
  401. print('{}数据关系完成...'.format(time.time()))
  402. def update_icon(self):
  403. """
  404. 更新行为对应图标
  405. :return:
  406. """
  407. icons = self.linshi_db.select(self.sql_19)
  408. for ic in icons:
  409. id = ic[0]
  410. name = ic[1]
  411. self.linshi_db.update(self.sql_20, [id, name])
  412. pass
  413. def insert_score_to_db(self, scores):
  414. """
  415. 行为、模块分数写入数据库
  416. :return:
  417. """
  418. ids = self.query_data()
  419. behavior_score = scores['behavior_score']
  420. module_score = scores['module_score']
  421. module_insert_sql = self.get_insert_sql('模块分数')
  422. if module_insert_sql:
  423. module_insert_data = []
  424. for module in module_score:
  425. city_2 = module[0]
  426. age_2 = module[1]
  427. crowd_2 = module[2]
  428. module_name_2 = module[3]
  429. module_score_2 = module[4]
  430. for id in ids:
  431. city_1 = id[2]
  432. age_1 = id[1]
  433. crowd_1 = id[3]
  434. id_1 = id[0]
  435. if city_2 == city_1 and age_2 == age_1 and crowd_2 == crowd_1:
  436. module_insert_data.append([id_1, module_name_2, module_score_2])
  437. # 先清空之前的数据
  438. if len(module_insert_data) > 0:
  439. table_name = self.get_table_name('模块分数')
  440. if table_name is not None and table_name not in self.insert_table:
  441. self.linshi_db.truncate(table_name)
  442. self.linshi_db.add_some(module_insert_sql, module_insert_data)
  443. self.insert_table.append(table_name)
  444. print('模块分数更新完成...')
  445. for b_score in behavior_score:
  446. for key in b_score.keys():
  447. insert_sql = self.get_insert_sql(key)
  448. if insert_sql:
  449. insert_data = []
  450. score = b_score[key]
  451. for data in score:
  452. city = data[0]
  453. age = data[1]
  454. tag_name = data[2]
  455. crowd = data[3]
  456. tag_score = data[4]
  457. # if key == '用户画像-行为兴趣' and city == '上海市' and age == '85后':
  458. # pass
  459. # else:
  460. for id in ids:
  461. city_1 = id[2]
  462. age_1 = id[1]
  463. crowd_1 = id[3]
  464. id_1 = id[0]
  465. if city == city_1 and age == age_1 and crowd == crowd_1:
  466. insert_data.append([id_1, tag_name, tag_score])
  467. if len(insert_data) > 0:
  468. table_name = self.get_table_name(key)
  469. if table_name and table_name not in self.insert_table:
  470. # if table_name == 'mvp_crowd_info_behavior':
  471. # self.linshi_db.delete(self.sql_18)
  472. # else:
  473. self.linshi_db.truncate(table_name)
  474. self.linshi_db.add_some(insert_sql, insert_data)
  475. self.insert_table.append(table_name)
  476. else:
  477. print('未找到对应的表,数据无法插入...')
  478. print('行为分数更新完成...')
  479. def module_score(self, crowd, city, age, scores):
  480. """
  481. 模块分数计算
  482. 城市 年龄 人群分类 模块名称 分数
  483. :return:
  484. """
  485. # import json
  486. # print(json.dumps(scores, ensure_ascii=False))
  487. modules = self.module_scores[crowd]
  488. result = []
  489. for key in modules.keys():
  490. values = modules[key]
  491. module_name = key
  492. score = 0
  493. for value in values:
  494. behavioral_name = value[0]
  495. weight = float(value[2])
  496. standard_score = [x[4] for x in scores if x[2] == behavioral_name]
  497. if len(standard_score) > 0:
  498. score += standard_score[0] * weight
  499. result.append([city, age, crowd, module_name, score])
  500. return result
  501. # def insert_data(self, scores_behavioral, scores_module):
  502. def insert(self):
  503. """
  504. 计算数据写入数据库中,供接口查看
  505. :return:
  506. """
  507. infos = []
  508. for city in self.city_list:
  509. for age in self.age_list:
  510. for c_type in self.crowd:
  511. age_area = self.age_dict.get(age)
  512. if age_area:
  513. infos.append([age_area, city, c_type])
  514. self.shangju_db.add_some(self.sql_11, infos)
  515. def query_data(self):
  516. ids = self.linshi_db.select(self.sql_14)
  517. return ids
  518. def shanghai_85_module_score_insert(self):
  519. """
  520. 上海市,85后模块分数计算
  521. :return:
  522. """
  523. result = []
  524. for crowd in self.crowd:
  525. modules = self.module_scores[crowd]
  526. for key in modules.keys():
  527. values = modules[key]
  528. module_name = key
  529. score = 0
  530. for value in values:
  531. behavioral_name = value[0]
  532. weight = float(value[2])
  533. # standard_score = [x[4] for x in scores if x[2] == behavioral_name]
  534. standard_score = float(value[1])
  535. if standard_score is not None:
  536. score += standard_score * weight
  537. result.append(['上海市', '85后', crowd, module_name, score])
  538. return {'score': result, 'data': self.module_scores}
  539. def tag_module_score_insert(self):
  540. """
  541. 标签模块分数写入数据库
  542. :return:
  543. """
  544. ids = self.query_data()
  545. insert_data = []
  546. insert_data_1 = []
  547. for tag, module in zip(self.scores_tag, self.score_module):
  548. city = tag[0]
  549. age = tag[1]
  550. crowd = tag[2]
  551. tag_name = tag[3]
  552. tag_score = tag[4]
  553. city_2 = module[0]
  554. age_2 = module[1]
  555. crowd_2 = module[2]
  556. module_name_2 = module[3]
  557. module_score_2 = module[4]
  558. for id in ids:
  559. city_1 = id[2]
  560. age_1 = id[1]
  561. crowd_1 = id[3]
  562. id_1 = id[0]
  563. if city == city_1 and self.age_dict[age] == age_1 and crowd == crowd_1:
  564. insert_data.append([id_1, tag_name, tag_score])
  565. if city_2 == city_1 and self.age_dict[age_2] == age_1 and crowd_2 == crowd_1:
  566. insert_data_1.append([id_1, module_name_2, module_score_2])
  567. self.shangju_db.add_some(self.sql_12, insert_data)
  568. self.shangju_db.add_some(self.sql_13, insert_data_1)
  569. def init_age(self):
  570. """
  571. 获取答题数据中的年龄
  572. """
  573. return ['95后', '85后']
  574. # age_info = self.marketing_db.select(self.sql_4)
  575. # # print([x[0] for x in age_info])
  576. # return [x[0] for x in age_info if x[0] is not None]
  577. def city_age_crowd(self, city=None, age=None, crowd=None):
  578. data_start = []
  579. result = []
  580. module_scores = []
  581. self.people_info_1 = self.people_info()
  582. if city is not None and age is not None and crowd is not None:
  583. print('获取指定城市,年龄段,人群类型的数据...')
  584. # people_uuids = self.get_people_uuid_by_type(crowd)
  585. people_uuids = self.people_filter(city, age, crowd)
  586. behavior_data = None
  587. if len(people_uuids) > 0:
  588. print('{}-{}-{}'.format(city, age, crowd))
  589. datas = self.behavior_tag_init(city, age, people_uuids)
  590. data_start.append(datas)
  591. all_data, behavior_data_1 = self.calculation_standard_score(datas, city, age, crowd)
  592. result.append(all_data)
  593. behavior_data = behavior_data_1
  594. if behavior_data:
  595. module_scores.extend(self.module_score(crowd, city, age, behavior_data))
  596. # data_list = []
  597. # for e in data_start:
  598. # for key in e.keys():
  599. # values = e[key]
  600. # for sub_e in values:
  601. # ele = [key]
  602. # ele.extend(sub_e)
  603. # data_list.append(ele)
  604. # pass
  605. return {'behavior_score': result, 'module_score': module_scores}
  606. # return {'score': result, 'data': data_list}
  607. def scores(self):
  608. behavior_score = []
  609. module_scores = []
  610. for city in self.city_list:
  611. for age in self.age_list:
  612. for crowd in self.crowd:
  613. data = self.city_age_crowd(city, age, crowd)
  614. behavior_score.extend(data['behavior_score'])
  615. module_scores.extend(data['module_score'])
  616. return {'behavior_score': behavior_score, 'module_score': module_scores}
  617. def behavior_tag_init(self, city, age, people_uuids):
  618. result = {}
  619. self.group_type_count = self.marketing_db.select(self.sql_5, [people_uuids])
  620. # 表名
  621. for key in self.tag_data.keys():
  622. values = self.tag_data[key]
  623. result_sub = {}
  624. # 标签
  625. for key_tag_name in values.keys():
  626. questions = values[key_tag_name]
  627. elements = []
  628. for value in questions:
  629. question = value[0].split('-')[0]
  630. option = value[0].split('-')[1]
  631. corr = value[1]
  632. fz, fm = self.molecular_value(question, option, city, age, people_uuids)
  633. if fm == 0:
  634. c = 0
  635. else:
  636. c = fz / fm
  637. elements.append([question, option, corr, fz, fm, c])
  638. result_sub[key_tag_name] = elements
  639. result[key] = self.indicator_calculation_d_e(result_sub)
  640. return result
  641. def molecular_value(self, queston, option, city, age, people_uuids):
  642. # 获取当前父选项包含的子选项id和子题id列表
  643. result = self.shangju_db.select(self.sql_6, [option, queston])
  644. sub_option_ids = []
  645. group_types = []
  646. for rt in result:
  647. sub_option_id, sub_question_id, content = rt[0], rt[1], rt[2]
  648. grouptypes = self.shangju_db.select(self.sql_7, [sub_question_id])
  649. for g_t in grouptypes:
  650. if str(g_t[0]) not in group_types:
  651. group_types.append(str(g_t[0]))
  652. sub_option_ids.append(sub_option_id)
  653. # 计算子选项在答题记录中的点击数
  654. sub_options_count = 0
  655. if len(sub_option_ids) > 0:
  656. result_1 = self.marketing_db.select(self.sql_8, [sub_option_ids, people_uuids])
  657. sub_options_count = result_1[0][0]
  658. # 计算父选项包含的子选项对应的子题所在的测试gt包含的点击数。
  659. denominator_value = 0
  660. for info in self.group_type_count:
  661. if str(info[0]) in group_types:
  662. denominator_value += info[1]
  663. return sub_options_count, denominator_value
  664. def indicator_calculation_d_e(self, data):
  665. result = {}
  666. for key in data.keys():
  667. values = data[key]
  668. c_list = []
  669. for x in values:
  670. _x = x[5]
  671. if _x is not None and x != 0:
  672. c_list.append(_x)
  673. fm_list = [x[4] for x in values]
  674. sum_c = sum(fm_list)
  675. if len(c_list) == 0:
  676. min_c = 0
  677. else:
  678. min_c = min(c_list)
  679. elements = []
  680. for value in values:
  681. _value = []
  682. c = value[5]
  683. if sum_c == 0:
  684. d = 0
  685. else:
  686. d = c / sum_c
  687. e = c - min_c
  688. _value.extend(value)
  689. _value.append(d)
  690. _value.append(e)
  691. elements.append(_value)
  692. result[key] = elements
  693. return result
  694. def calculation_standard_score(self, datas, city, age, crowd_type):
  695. scores = {}
  696. for key_tag_type in datas.keys():
  697. print(key_tag_type)
  698. tag_type_data = datas[key_tag_type]
  699. scores_sub = []
  700. for key_tag in tag_type_data.keys():
  701. key_tag_data = tag_type_data[key_tag]
  702. print(key_tag)
  703. print(' 父题序号 父选项序号 相关系系数 分子值 分母值 百分比 人数权重 偏离值')
  704. values = [x[5] for x in key_tag_data]
  705. min_c = min(values)
  706. f = min_c
  707. for value in key_tag_data:
  708. print(' {}'.format(value))
  709. if value[2] is not None and value[7] is not None:
  710. f += float(value[2] * value[7])
  711. print(' 标准分:{}'.format(f))
  712. scores_sub.append([city, age, key_tag, crowd_type, f])
  713. scores[key_tag_type] = scores_sub
  714. # self.shangju_db.add_some(self.sql_9, scores)
  715. return scores, scores['用户画像-行为兴趣']
  716. def people_data(self):
  717. result = self.people_info()
  718. a = 0
  719. b = 0
  720. c = 0
  721. d = 0
  722. e = 0
  723. f = 0
  724. result_1 = []
  725. for rt in result:
  726. crowds = rt.crowd
  727. if rt.uuid in [
  728. 'ae9db26b-3606-497c-83c5-56341d487a91',
  729. '9fb33b6c-bd7a-4114-b225-3ee380943517',
  730. '84636488-1307-47fe-a238-4f9cf279a908',
  731. '4a5b6654-eb99-46ed-8dcf-777648d6baca',
  732. 'ba181da0-c91a-4430-84c6-9612a693f659',
  733. '32eae583-474c-4dca-8b36-d74314f45cee',
  734. 'b07f6ff2-ccd5-44ee-9b7c-b2e1f40d777f',
  735. '149a0e40-5639-4771-8a27-60821e14a1d5',
  736. '4795b731-3e75-4f08-90bc-8ee4a0c366c6',
  737. '4795b731-3e75-4f08-90bc-8ee4a0c366c6',
  738. '47cbd398-1c39-4dc0-8d97-98fe19457516']:
  739. result_1.append([rt.uuid, rt.city, rt.age, rt.crowd])
  740. if 'A' in crowds:
  741. a += 1
  742. if 'B' in crowds:
  743. b += 1
  744. if 'C' in crowds:
  745. c += 1
  746. if 'D' in crowds:
  747. d += 1
  748. if 'E' in crowds:
  749. e += 1
  750. if 'F' in crowds:
  751. f += 1
  752. return result_1
  753. # return {'A': a, 'B': b, 'C': b, 'D': d, 'E': e, 'F': f}
  754. def get_crowd_people(self):
  755. result = {}
  756. for type in self.crowd:
  757. uuids = self.get_people_uuid_by_type(type)
  758. result[type] = len(uuids)
  759. return result
  760. def get_people_uuid_by_type(self, type):
  761. uuids = []
  762. type_sub_option_ids = self.crowd_contain_sub_option_ids[type]
  763. for people in self.people_sub_option_ids:
  764. uuid = people[0]
  765. sub_option_ids = list(map(int, str(people[1]).split(',')))
  766. # list(set(a).intersection(set(b)))
  767. if len(list(set(sub_option_ids).intersection(set(type_sub_option_ids)))) > 0 and uuid not in uuids:
  768. uuids.append(uuid)
  769. return uuids
  770. def get_crowd_contain_sub_option_ids(self):
  771. """
  772. 获取ABCDEF人群包含的子选项id
  773. :return:
  774. """
  775. infos = {}
  776. for key in self.crowd_info.keys():
  777. values = self.crowd_info[key]
  778. sub_option_ids = []
  779. for value in values:
  780. if value is not None:
  781. vals = str(value).split('-')
  782. option, question = vals[1], vals[0]
  783. query_result = self.shangju_db.select(self.sql_6, [option, question])
  784. for qr in query_result:
  785. sub_option_id, sub_question_id, content = qr[0], qr[1], qr[2]
  786. sub_option_ids.append(int(sub_option_id))
  787. infos[key] = sub_option_ids
  788. return infos
  789. if __name__ == '__main__':
  790. # mvp = Mvp()
  791. # mvp.linshi_db = MysqlDB('linshi', db_type=1)
  792. # mvp.update_icon()
  793. pass