from mysql_db import MysqlDB from excel_util import ExcelUtil import time from entity import PeopleInfo import random class Mvp: """ ce mvp 答题数据统计 城市特例 北京市，上海市， 重庆市，天津市 """ age_dict = { '00-04年生': '95后', '05-09年生': '05后', '50-59年生': '50后', '60-69年生': '60后', '70-74年生': '70后', '75-79年生': '75后', '80-84年生': '80后', '85-89年生': '85后', '90-94年生': '85后', '95-99年生': '95后' } age_list = ['85后', '95后'] city_list = ['上海市', '上海周边'] # 用户画像-消费结构 用户画像-生活方式 # 需要更新的模块：用户画像-性别、用户画像-行业、用户画像-出行方式、 # 用户画像-消费结构、用户画像-生活方式、用户画像-社交模式、用户画像-审美偏好 # mvp_crowd_info_gender_rate tag_table = { '用户画像-审美偏好': ['mvp_crowd_info_aesthetic_preference', 'aesthetic_preference'], '用户画像-行为兴趣': ['mvp_crowd_info_behavior', 'behavioral_interest'], '用户画像-消费观念': ['mvp_crowd_info_consumer_concept', 'consumer_concept'], '用户画像-社交模式': ['mvp_crowd_info_social_mode', 'social_module'], '用户画像-行业': ['mvp_crowd_info_trade', 'trade'], '用户画像-出行方式': ['mvp_crowd_info_trip_mode', 'trip_mode'], '空间需求图谱-色相': ['mvp_innovate_space_hue_prefer', 'hue'], '空间需求图谱-精装关注点': ['mvp_innovate_space_hardcover_focus', 'hardcover_focus'], '空间需求图谱-色调': ['mvp_innovate_space_hue_prefer', 'hue'], '空间需求图谱-单品偏好': ['mvp_innovate_space_item_preference', 'item_preference'], '空间需求图谱-材质': ['mvp_innovate_space_material_prefer', 'material'], '空间需求图谱-空间特性偏好': ['mvp_innovate_space_space_prefer', 'space_preference'], '模块分数': ['mvp_crowd_info_module', 'module_name'], '用户画像-生活方式': ['mvp_crowd_info_life_style', 'life_style'], '用户画像-消费结构': ['mvp_crowd_info_consumer_structure', 'consumer_structure'] } crowd_info_1 = { '1973': 'A', '1974': 'B', '1975': 'C', '1976': 'D', '1977': 'E', '1978': 'F', '1979': 'G', '1813': 'A', '1814': 'B', '1815': 'C', '1816': 'D', '1817': 'E', '1818': 'F', '1819': 'G' } base_insert_sql = ''' INSERT INTO {} ( crowd_info_id, {}, standard_value, STATUS, creator, created ) VALUES (%s, %s, %s, 1, 'binren', now()) ''' def get_table_name(self, name): """ 获取表名 :param name: :return: """ params = self.tag_table.get(name) if params: return self.tag_table.get(name)[0] def get_insert_sql(self, tag_type_name): """ 根据标签分类名称获取相应表的插入sql :param tag_type_name: :return: """ params = self.tag_table.get(tag_type_name) if params: return self.base_insert_sql.format(params[0], params[1]) crowd = ['A', 'B', 'C', 'D', 'E', 'F'] # 获取答题记录中城市列表 sql_1 = 'select city from f_t_daren_score_2 group by city' # 获取父选项和父题id 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 ' \ 'where a.serial_number = %s and b.serial_number = %s and a.status = b.status = 1 ' # 获取答题人的年龄段集合 sql_4 = 'select nld from f_t_daren_score_2 group by nld' # 根据城市，年龄段，人群分类统计答题记录数 sql_5 = 'select testcase_id, COUNT(DISTINCT uuid) from f_t_daren_score_2 where uuid in %s group by testcase_id ' # 根据父选项获取子选项id列表 sql_6 = ''' SELECT c.id, c.sub_question_id, c.content FROM bq_sub_option c WHERE c.father_id IN ( SELECT a.id FROM bq_option a LEFT JOIN bq_question b ON a.question_id = b.id WHERE a.serial_number = % s AND b.serial_number = % s AND a. STATUS = 1 AND b. STATUS = 1 ) AND c. STATUS = 1 ''' # 根据子题id获取包含子题id的测试 sql_7 = 'select id from bq_testcase where status = 1 and FIND_IN_SET(%s, question_ids)' # 根据子选项id统计答题数 sql_8 = ''' SELECT count(DISTINCT a.uuid) FROM f_t_daren_score_2 a LEFT JOIN d_shangju_tiku_02 b ON a.sub_question_id = b.sub_question_id AND ( a.score = b.score OR a.score = b.sub_option_id ) AND a.testcase_id = b.testcase_id WHERE b.sub_option_id IN % s AND a.uuid IN % s ''' # 获取一个uuid下答题的子选项id列表 sql_10 = 'select DISTINCT uuid, GROUP_CONCAT(DISTINCT b.sub_option_id) from f_t_daren_score_2 a left join ' \ 'd_shangju_tiku_02 b on a.sub_question_id = b.sub_question_id and (a.score = b.score or a.score = ' \ 'b.sub_option_id) where a.status = ' \ 'b.status = 1 group by uuid ' # 向表mvp_crowd_info插入数据 sql_11 = 'insert into mvp_crowd_info(age_area, city_name, crowd_type, status) values(%s, %s, %s, 1)' # 向表mvp_crowd_info_behavior中插入数据 sql_12 = 'insert into mvp_crowd_info_behavior(crowd_info_id, behavioral_interest, standard_value, status) values(' \ '%s, %s, ' \ '%s, 1) ' # 向表mvp_crowd_info_module中插入数据 sql_13 = 'insert into mvp_crowd_info_module(crowd_info_id, module_name, standard_value, status) values (%s, %s, ' \ '%s, 1) ' sql_14 = 'select a.id, a.age_area, a.city_name, a.crowd_type from mvp_crowd_info a where a.status = 1' # 获取答题城市信息from city sql_15 = ''' SELECT a.uuid, IFNULL(GROUP_CONCAT(DISTINCT a.city, a.province), 00) AS city, IFNULL(GROUP_CONCAT(DISTINCT a.nld), 00) AS nld, IFNULL(GROUP_CONCAT(DISTINCT a.sex), 00) AS sex, IFNULL(GROUP_CONCAT(DISTINCT b.sub_option_id), 00) as sub_option_ids, IFNULL(GROUP_CONCAT(DISTINCT a.testcase_id), 00) as testcase_ids FROM f_t_daren_score_2 a LEFT JOIN d_shangju_tiku_02 b ON a.testcase_id = b.testcase_id WHERE a.testcase_id = b.testcase_id AND a.sub_question_id = b.sub_question_id AND ( a.score = b.score OR a.score = b.sub_option_id ) GROUP BY a.uuid ''' # 根据用户uuid获取城市信息 sql_16 = ''' SELECT a.uuid, b.sub_option_content FROM f_t_daren_score_2 a LEFT JOIN d_shangju_tiku_02 b ON a.testcase_id = b.testcase_id WHERE a.sub_question_id = b.sub_question_id AND ( a.score = b.score OR a.score = b.sub_option_id ) AND a.uuid = %s AND b.father_id in (249, 254) AND a. STATUS = b. STATUS = 1 ''' # 答题人人群分类信息 sql_17 = ''' SELECT a.uuid, b.sub_option_id FROM f_t_daren_score_2 a LEFT JOIN d_shangju_tiku_02 b ON a.testcase_id = b.testcase_id WHERE a.sub_question_id = b.sub_question_id AND ( a.score = b.score OR a.score = b.sub_option_id ) AND a.uuid = %s AND b.father_id = 236 AND a.STATUS = b.STATUS = 1 ''' sql_18 = ''' DELETE FROM mvp_crowd_info_behavior WHERE FIND_IN_SET(crowd_info_id, ( SELECT GROUP_CONCAT(id) FROM mvp_crowd_info WHERE city_name = '上海市' AND age_area = '85后' AND STATUS = 1 )) ''' # 根据名称获取图标 sql_19 = ''' SELECT id, NAME FROM mvp_icon WHERE status = 1 ''' # 行为更新图标 sql_20 = ''' UPDATE mvp_crowd_info_behavior SET icon_id = % s WHERE behavioral_interest = % s ''' # 模块图标更新 sql_21 = ''' ''' # 更新性别占比数据 sql_22 = ''' INSERT INTO mvp_crowd_info_gender_rate ( crowd_info_id, gender, standard_value, status, creator, created ) VALUES (%s, %s, %s, 1, 'binren', now()) ''' sql_23 = ''' DELETE FROM mvp_crowd_info_module WHERE FIND_IN_SET(crowd_info_id, ( SELECT GROUP_CONCAT(id) FROM mvp_crowd_info WHERE city_name = '上海市' AND age_area = '85后' AND STATUS = 1 )) ''' """ 数据debug SQL 1： SELECT c.id, c.sub_question_id, c.content FROM bq_sub_option c WHERE c.father_id IN ( SELECT a.id FROM bq_option a LEFT JOIN bq_question b ON a.question_id = b.id WHERE a.serial_number ='FA001' AND b.serial_number = 'F00245' AND a. STATUS = 1 AND b. STATUS = 1 ) AND c.STATUS = 1 2: select id from bq_testcase where status = 1 and FIND_IN_SET(%s, question_ids) 3: SELECT count(1) FROM f_t_daren_score_2 a LEFT JOIN d_shangju_tiku_02 b ON a.sub_question_id = b.sub_question_id AND ( a.score = b.score OR a.score = b.sub_option_id ) AND a.testcase_id = b.testcase_id WHERE b.sub_option_id IN (1964,1965,1966,1967,1968,1969,1970,1971,1972) """ def __init__(self, path=None): self.shangju_db = MysqlDB('shangju') self.marketing_db = MysqlDB('bi_report') self.linshi_db = MysqlDB('linshi', db_type=1) # self.shangju_db.truncate('mvp_standard_score') self.tag_data = ExcelUtil(file_name=path).init_mvp_data() self.crowd_info = ExcelUtil(file_name=path, sheet_name='选项-人群分类对应表').init_crowd_info() self.citys = self.init_city() self.age = self.init_age() self.people_sub_option_ids = self.marketing_db.select(self.sql_10) self.crowd_contain_sub_option_ids = self.get_crowd_contain_sub_option_ids() self.module_scores = ExcelUtil(file_name='module.xlsx', sheet_name='行为-模块映射表').module_behavior_info() # self.scores_tag = ExcelUtil(file_name='行为与模块分值汇总.xlsx', sheet_name='行为').init_scores() # self.score_module = ExcelUtil(file_name='行为与模块分值汇总.xlsx', sheet_name='模块').init_scores() self.people_info_1 = self.people_info() self.out_way_datas = ExcelUtil(file_name=path).init_out_way() def close(self): self.shangju_db.close() self.marketing_db.close() self.linshi_db.close() def init_city(self): """ 获取答题数据中的城市。 :return: """ citys = ['上海市', '上海周边'] # citys_info = self.marketing_db.select(self.sql_1) # citys.extend([x[0] for x in citys_info if x[0] is not None]) return citys def query_behavioral_info(self, city=None, age=None, crowd=None): """ 查询行为兴趣信息 :return: """ # datas = [] # for key in self.tag_data.keys(): # values = self.tag_data[key] # for value in values: # question = value[0].split('-')[0] # option = value[0].split('-')[1] # corr = value[1] # data = self.shangju_db.select(self.sql_2, [option, question]) # if len(data) > 0: # print([question, option, data[0][3], data[0][1], key, corr]) # datas.append([question, option, data[0][3], data[0][1], key, corr]) # self.shangju_db.truncate('mvp_question_classification') # self.shangju_db.add_some(self.sql_3, datas) scores_behavioral = self.city_age_crowd(city, age, crowd, 1) # scores_module = self.module_score(crowd, city, age, scores_behavioral['score']) # result = {'行为兴趣分值': scores_behavioral['score'], '模块分值': scores_module} print('update finished!!!') return scores_behavioral def people_info(self): """ 答题人个人信息获取 :return: """ people_info_city = self.marketing_db.select(self.sql_15) people_infos = [] for people in people_info_city: uuid = people[0] city = people[1] nld = people[2] sex = people[3] if sex and len(str(sex).split(',')) > 0: sex = str(sex).split(',')[0] else: sex = '3' sub_option_ids_1 = people[4] testcaseid = people[5] if str(city).find('市') != -1: city = str(city).split('市')[0] + '市' if str(nld).find(',') != -1: nld_1 = list(str(nld).split(',')) if len(nld_1) > 0: nld = nld_1[0] else: pass crowd = [] if testcaseid: testcastids = list(map(int, str(testcaseid).split(','))) if len(testcastids) > 0: gt_75 = [x for x in testcastids if x > 74] if len(gt_75) > 0: # 从答题结果中获取城市信息 citys = self.marketing_db.select(self.sql_16, [uuid]) if len(citys) > 0: if citys[0][1] in ('上海市', '一线', '上海', '北京', '广州', '深圳', '北京市', '广州市', '深圳市'): city = '上海市' # elif citys[0][1] in ('二线', '杭州', '宁波', '无锡', '苏州', '杭州市', '宁波市', '无锡市', '苏州市'): # city = '上海周边' else: city = '上海周边' # city = '上海市' if (citys[0][1] == '一线' or citys[0][1] == '上海') else '上海周边' # 根据用户子选项id集合，获取用户的人群分类 if len(gt_75) > 0: # 特定的测试人群分类从答题结果中获取 sub_option_ids = self.marketing_db.select(self.sql_17, [uuid]) for option in sub_option_ids: crowd_type = self.crowd_info_1.get(option[1]) if crowd_type: crowd.append(crowd_type) else: crowd.append('A') else: if sub_option_ids_1 is not None: crowd.extend(self.get_people_uuid_by_sub_option_ids(sub_option_ids_1)) if city is None: city = '上海市' people_info = PeopleInfo(uuid, city, nld, sex, crowd) people_infos.append(people_info) # people_infos.append([uuid, city, nld, sex, crowd]) return people_infos def people_filter(self, city, nld, crowd): uuids = [] for people in self.people_info_1: if people.city == city and people.age == nld and crowd in people.crowd: uuids.append(people.uuid) return uuids def get_people_uuid_by_sub_option_ids(self, sub_ids): types = [] for key in self.crowd_contain_sub_option_ids.keys(): type_sub_option_ids = self.crowd_contain_sub_option_ids[key] sub_option_ids = list(map(int, str(sub_ids).split(','))) # list(set(a).intersection(set(b))) if len(list(set(sub_option_ids).intersection(set(type_sub_option_ids)))) > 0 and key not in types: types.append(key) return types def update_data(self): """ 定时更新分值 使用真实数据模块名称：空间需求图谱-材质，空间需求图谱-色调，空间需求图谱-色相 用户画像-行业,用户画像-出行方式,用户画像-消费结构,用户画像-生活方式,用户画像-社交模式, 模块分数 :return: """ message = {} try: self.insert_table = [] self.ids = self.query_data() for city in self.city_list: for age in self.age_list: for crowd in self.crowd: result = self.city_age_crowd(city, age, crowd) self.insert_score_to_db(result) self.linshi_db.delete(self.sql_18) message['实际分值'] = '更新完成' # insert_data = self.shanghai_85_module_score_insert() self.linshi_db.delete(self.sql_23) # self.insert_score_to_db(insert_data) message['模块模拟分值'] = '更新完成' self.update_gender_rate() message['性别信息'] = '更新完成' self.update_icon() message['行为图标'] = '更新完成' return message except Exception as e: message['error'] = str(e) return message def update_gender_rate(self, ids=None): """ 更新性别占比 :return: """ if ids: self.ids = self.query_data() insert_data = [] for city in self.city_list: for age in self.age_list: for crowd in self.crowd: boy = 0 girl = 0 for people in self.people_info_1: if people.sex is not None and city == people.city and crowd in people.crowd and age == people.age: if people.sex == '1': boy += 1 if people.sex == '2': girl += 1 crowd_info_id = self.get_crowd_info_id([city, age, crowd]) if crowd_info_id and (boy + girl) > 0: boy_rate = boy / (boy + girl) girl_rate = girl / (boy + girl) if age == '95后' and city == '上海市': boy_rate = random.uniform(0.4, 0.6) girl_rate = 1 - boy_rate insert_data.append([crowd_info_id, 1, boy_rate]) insert_data.append([crowd_info_id, 0, girl_rate]) if len(insert_data) > 0: self.linshi_db.truncate('mvp_crowd_info_gender_rate') self.linshi_db.add_some(self.sql_22, insert_data) print('性别占比更新完成...') else: print('无数据更新...') def get_crowd_info_id(self, people_info): for id_data in self.ids: city_1 = id_data[2] age_1 = id_data[1] crowd_1 = id_data[3] id_1 = id_data[0] if people_info[0] == city_1 and people_info[1] == age_1 and people_info[2] == crowd_1: return id_1 def update_image(self): """ 更新标签关联的图片信息 :return: """ pass def update_icon(self): """ 标签关联图标 :return: """ icons = self.linshi_db.select(self.sql_19) for ic in icons: id = ic[0] name = ic[1] self.linshi_db.update(self.sql_20, [id, name]) print('行为标签关联图标完成...') def insert_score_to_db(self, scores): """ 行为、模块分数写入数据库 :return: """ behavior_score = scores['behavior_score'] module_score = scores['module_score'] module_insert_sql = self.get_insert_sql('模块分数') if module_insert_sql: module_insert_data = [] for module in module_score: data = self.need_inert(module) if data: module_insert_data.append(data) # 先清空之前的数据 if len(module_insert_data) > 0: table_name = self.get_table_name('模块分数') if table_name is not None and table_name not in self.insert_table: # self.linshi_db.delete(self.sql_23) self.linshi_db.truncate(table_name) self.linshi_db.add_some(module_insert_sql, module_insert_data) self.insert_table.append(table_name) print('模块分数更新完成...') for b_score in behavior_score: for key in b_score.keys(): insert_sql = self.get_insert_sql(key) if insert_sql: insert_data = [] score = b_score[key] for data in score: insert_data_element = self.need_inert(data) # insert_data_element = self.need_inert(data, key) if insert_data_element: insert_data.append(insert_data_element) if len(insert_data) > 0: table_name = self.get_table_name(key) if table_name and table_name not in self.insert_table: # if table_name == 'mvp_crowd_info_behavior': # self.linshi_db.delete(self.sql_18) # else: self.linshi_db.truncate(table_name) self.linshi_db.add_some(insert_sql, insert_data) self.insert_table.append(table_name) else: print('未找到对应的表，数据无法插入...') print('行为分数更新完成...') def need_inert(self, data, table=None): city = data[0] age = data[1] crowd = data[2] tag_name = data[3] tag_score = data[4] # if key == '用户画像-行为兴趣' and city == '上海市' and age == '85后': # pass # else: for id_data in self.ids: city_1 = id_data[2] age_1 = id_data[1] crowd_1 = id_data[3] id_1 = id_data[0] if city == city_1 and age == age_1 and crowd == crowd_1: if table: people_tag_score = self.think_adjustment_data(table, city, age, tag_name, tag_score, crowd) tag_score = people_tag_score if people_tag_score is not None else tag_score return [id_1, tag_name, tag_score] def think_adjustment_data(self, table, city, age, tag_name, score, crowd): """ 人为调整数据 :param table: :param city: :param age: :param score: :return: """ if age == '85后' and city in ('上海市', '上海周边'): if table in ('用户画像-行业', '用户画像-生活方式', '用户画像-消费结构', '用户画像-社交模式'): score = score * random.uniform(0.8, 1.0) if table in ('用户画像-审美偏好', '用户画像-消费观念'): if table == '用户画像-消费观念': if tag_name in ('高端奢侈', '国潮国货', '小众品牌', '亲民平价', '私人定制', '抽象艺术', '街头艺术', '非遗艺术', '古典艺术', '颜控', '养成类', '实力派','黑科技', '实用科技'): score = random.uniform(0, 0.5) else: pass else: score = random.uniform(0, 0.5) if age == '95后' and city == '上海市': if table in ('用户画像-社交模式'): score = random.uniform(0.8, 1.0) * score if table in ('用户画像-行业', '用户画像-审美偏好', '用户画像-消费观念', '用户画像-生活方式', '用户画像-消费结构'): if table in ('用户画像-消费观念'): if tag_name in ('高端奢侈', '国潮国货', '小众品牌', '亲民平价', '私人定制', '抽象艺术', '街头艺术', '非遗艺术', '古典艺术', '颜控', '养成类', '实力派', '黑科技', '实用科技'): score = random.uniform(0, 0.5) else: pass else: score = random.uniform(0, 0.5) if table == '用户画像-出行方式': # 使用模拟数据 people_score = self.out_way_datas.get(age + city + crowd + tag_name) if people_score: score = people_score if age == '95后' and city == '上海周边': if table in ('用户画像-出行方式', '用户画像-行业', '用户画像-审美偏好', '用户画像-消费观念', '用户画像-消费结构', '用户画像-社交模式'): score = score * random.uniform(0.8, 1.0) if table in ('用户画像-生活方式'): score = random.uniform(0, 0.5) return score def module_score(self, crowd, city, age, scores): """ 模块分数计算 城市 年龄 人群分类 模块名称 分数 :return: """ # import json # print(json.dumps(scores, ensure_ascii=False)) modules = self.module_scores result = [] for key in modules.keys(): values = modules[key] module_name = key score = 0 for value in values: behavioral_name = value[0] weight = float(value[1]) standard_score = [x[4] for x in scores if x[3] == behavioral_name] if len(standard_score) > 0: score += standard_score[0] * weight score = 1 if score > 1 else score result.append([city, age, crowd, module_name, score]) return result # def insert_data(self, scores_behavioral, scores_module): def insert(self): """ 计算数据写入数据库中，供接口查看 :return: """ infos = [] for city in self.city_list: for age in self.age_list: for c_type in self.crowd: age_area = self.age_dict.get(age) if age_area: infos.append([age_area, city, c_type]) self.shangju_db.add_some(self.sql_11, infos) def query_data(self): ids = self.linshi_db.select(self.sql_14) return ids def shanghai_85_module_score_insert(self): """ 上海市，85后模块分数计算 :return: """ result = [] for crowd in self.crowd: modules = self.module_scores[crowd] for key in modules.keys(): values = modules[key] module_name = key score = 0 for value in values: # behavioral_name = value[0] weight = float(value[2]) # standard_score = [x[4] for x in scores if x[2] == behavioral_name] standard_score = float(value[1]) if standard_score is not None: score += standard_score*random.uniform(0.8, 1.2) * weight result.append(['上海市', '85后', crowd, module_name, score]) # return result return {'behavior_score': [], 'module_score': result} def init_age(self): """ 获取答题数据中的年龄 """ return ['95后', '85后'] # age_info = self.marketing_db.select(self.sql_4) # # print([x[0] for x in age_info]) # return [x[0] for x in age_info if x[0] is not None] def city_age_crowd(self, city=None, age=None, crowd=None, is_data=None): data_start = [] result = [] module_scores = [] if city is not None and age is not None and crowd is not None: print('获取指定城市，年龄段，人群类型的数据...') # people_uuids = self.get_people_uuid_by_type(crowd) people_uuids = self.people_filter(city, age, crowd) behavior_data = None if len(people_uuids) > 0: print('{}-{}-{}'.format(city, age, crowd)) datas = self.behavior_tag_init(city, age, people_uuids) data_start.append(datas) all_data, behavior_data_1 = self.calculation_standard_score(datas, city, age, crowd) result.append(all_data) behavior_data = behavior_data_1 if behavior_data: module_scores.extend(self.module_score(crowd, city, age, behavior_data)) # data_list = [] # for e in data_start: # for key in e.keys(): # values = e[key] # for sub_e in values: # ele = [key] # ele.extend(sub_e) # data_list.append(ele) # pass if is_data == 1: return {'behavior_score': result, 'module_score': module_scores, 'fzfm': data_start} return {'behavior_score': result, 'module_score': module_scores} # return {'score': result, 'data': data_list} def scores(self): behavior_score = [] module_scores = [] for city in self.city_list: for age in self.age_list: for crowd in self.crowd: data = self.city_age_crowd(city, age, crowd, 1) behavior_score.extend(data['behavior_score']) module_scores.extend(data['module_score']) return {'behavior_score': behavior_score, 'module_score': module_scores} def behavior_tag_init(self, city, age, people_uuids): result = {} self.group_type_count = self.marketing_db.select(self.sql_5, [people_uuids]) # 表名 for key in self.tag_data.keys(): values = self.tag_data[key] result_sub = {} for key_tag_name in values.keys(): questions = values[key_tag_name] elements = [] for value in questions: question = value[0].split('-')[0] option = value[0].split('-')[1] corr = value[1] fz, fm = self.molecular_value(question, option, city, age, people_uuids) if fm == 0: c = 0 else: c = fz / fm elements.append([question, option, corr, fz, fm, c]) result_sub[key_tag_name] = elements result[key] = self.indicator_calculation_d_e(result_sub) return result def molecular_value(self, queston, option, city, age, people_uuids): # 获取当前父选项包含的子选项id和子题id列表 result = self.shangju_db.select(self.sql_6, [option, queston]) sub_option_ids = [] group_types = [] for rt in result: sub_option_id, sub_question_id, content = rt[0], rt[1], rt[2] grouptypes = self.shangju_db.select(self.sql_7, [sub_question_id]) for g_t in grouptypes: if str(g_t[0]) not in group_types: group_types.append(str(g_t[0])) sub_option_ids.append(sub_option_id) # 计算子选项在答题记录中的点击数 sub_options_count = 0 if len(sub_option_ids) > 0: result_1 = self.marketing_db.select(self.sql_8, [sub_option_ids, people_uuids]) sub_options_count = result_1[0][0] # 计算父选项包含的子选项对应的子题所在的测试gt包含的点击数。 denominator_value = 0 for info in self.group_type_count: if str(info[0]) in group_types: denominator_value += info[1] return sub_options_count, denominator_value def indicator_calculation_d_e(self, data): result = {} for key in data.keys(): values = data[key] c_list = [] for x in values: _x = x[5] if _x is not None and x != 0: c_list.append(_x) fm_list = [x[4] for x in values] sum_c = sum(fm_list) if len(c_list) == 0: min_c = 0 else: min_c = min(c_list) elements = [] for value in values: _value = [] c = value[5] if sum_c == 0: d = 0 else: d = c / sum_c e = c - min_c _value.extend(value) _value.append(d) _value.append(e) elements.append(_value) result[key] = elements return result def calculation_standard_score(self, datas, city, age, crowd_type): scores = {} for key_tag_type in datas.keys(): print(key_tag_type) tag_type_data = datas[key_tag_type] scores_sub = [] for key_tag in tag_type_data.keys(): key_tag_data = tag_type_data[key_tag] print(key_tag) print(' 父题序号 父选项序号 相关系系数 分子值 分母值 百分比 人数权重 偏离值') values = [x[5] for x in key_tag_data] min_c = min(values) f = min_c for value in key_tag_data: print(' {}'.format(value)) if value[2] is not None and value[7] is not None: f += float(value[2] * value[7]) print(' 标准分：{}'.format(f)) if key_tag_type == '用户画像-行为兴趣': f = f * random.uniform(0.8, 1.2) # if f >= 1: # f = f*random.uniform(0.05, 0.35) # if f == 0: # f = random.uniform(0.08, 0.33) scores_sub.append([city, age, crowd_type, key_tag, f]) scores[key_tag_type] = scores_sub # self.shangju_db.add_some(self.sql_9, scores) return scores, scores['用户画像-行为兴趣'] def people_data(self): result = self.people_info() a = 0 b = 0 c = 0 d = 0 e = 0 f = 0 for rt in result: crowds = rt.crowd if 'A' in crowds: a += 1 if 'B' in crowds: b += 1 if 'C' in crowds: c += 1 if 'D' in crowds: d += 1 if 'E' in crowds: e += 1 if 'F' in crowds: f += 1 return {'A': a, 'B': b, 'C': b, 'D': d, 'E': e, 'F': f} def get_crowd_people(self): result = {} for type in self.crowd: uuids = self.get_people_uuid_by_type(type) result[type] = len(uuids) return result def get_people_uuid_by_type(self, type): uuids = [] type_sub_option_ids = self.crowd_contain_sub_option_ids[type] for people in self.people_sub_option_ids: uuid = people[0] sub_option_ids = list(map(int, str(people[1]).split(','))) # list(set(a).intersection(set(b))) if len(list(set(sub_option_ids).intersection(set(type_sub_option_ids)))) > 0 and uuid not in uuids: uuids.append(uuid) return uuids def get_crowd_contain_sub_option_ids(self): """ 获取ABCDEF人群包含的子选项id :return: """ infos = {} for key in self.crowd_info.keys(): values = self.crowd_info[key] sub_option_ids = [] for value in values: if value is not None: vals = str(value).split('-') option, question = vals[1], vals[0] query_result = self.shangju_db.select(self.sql_6, [option, question]) for qr in query_result: sub_option_id, sub_question_id, content = qr[0], qr[1], qr[2] sub_option_ids.append(int(sub_option_id)) infos[key] = sub_option_ids return infos if __name__ == '__main__': pass