from db.mysql_db import MysqlDB from utils.excel_util import ExcelUtil class Mvp: """ ce mvp 答题数据统计 城市特例 北京市，上海市， 重庆市，天津市 """ age_dict = { '00-04年生': '00后', '05-09年生': '05后', '50-59年生': '50后', '60-69年生': '60后', '70-74年生': '70后', '75-79年生': '75后', '80-84年生': '80后', '85-89年生': '85后', '90-94年生': '90后', '95-99年生': '95后' } 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 ' # 数据插入表mvp_question_classification sql_3 = 'insert into mvp_question_classification(question_serial_number, question_content, ' \ 'option_serial_number, option_content, tag, corr) values(%s, %s, %s, %s, %s, %s) ' # 获取答题人的年龄段集合 sql_4 = 'select nld from f_t_daren_score_2 group by nld' # 根据城市，年龄段，人群分类统计答题记录数 sql_5 = 'select group_type, COUNT(uuid) from f_t_daren_score_2 where (city = %s or province = %s) and nld ' \ '= %s and uuid in %s group by group_type ' # 根据父选项获取子选项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 group_type from bq_testcase where status = 1 and FIND_IN_SET(%s, question_ids)' # 根据子选项id统计答题数 sql_8 = 'SELECT count(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 WHERE a.testcase_id = b.testcase_id and b.sub_option_id in %s' \ 'and (a.city = %s or a.province = %s) and a.nld = %s and a.uuid in %s' # 计算值写入表汇总 sql_9 = 'insert into mvp_standard_score(city, age, tag, crowd_type, score) VALUES(%s, %s, %s, %s, %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 where a.status = ' \ 'b.status = 1 group by uuid ' def __init__(self, path=None): self.shangju_db = MysqlDB('shangju') self.marketing_db = MysqlDB('marketing_db') self.shangju_db.truncate('mvp_standard_score') self.tag_data = ExcelUtil(path=path).init_mvp_data() self.crowd_info = ExcelUtil(path=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() 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 write_tag(self, city=None, age=None, crowd=None): """ 将excel中的配置信息写入到数据库表中 :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) result = self.city_age_crowd(city, age, crowd) print('update finished!!!') return result def init_age(self): """ 获取答题数据中的年龄 """ 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): result = [] 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) if len(people_uuids) > 0: print('{}-{}-{}'.format(city, age, crowd)) datas = self.behavior_tag_init(city, age, people_uuids) result.extend(self.calculation_standard_score(datas, city, age, crowd)) pass else: print('获取所有case的数据...') for city in self.citys: for age in self.age: if city != '上海市' and age != '85-89年生': for crowd_type in self.crowd: # print(' {}{}'.format(city, age)) people_uuids = self.get_people_uuid_by_type(crowd_type) if len(people_uuids) > 0: print('{}-{}-{}'.format(city, age, crowd_type)) datas = self.behavior_tag_init(city, age, people_uuids) result.extend(self.calculation_standard_score(datas, city, age, crowd_type)) return result def behavior_tag_init(self, city, age, people_uuids): result = {} self.group_type_count = self.marketing_db.select(self.sql_5, [city, city, age, people_uuids]) for key in self.tag_data: values = self.tag_data[key] elements = [] for value in values: 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[key] = elements return self.indicator_calculation_d_e(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 g_t[0] not in group_types: group_types.append(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, city, city, age, people_uuids]) sub_options_count = result_1[0][0] # 计算父选项包含的子选项对应的子题所在的测试gt包含的点击数。 denominator_value = 0 for info in self.group_type_count: if 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 = [x[5] for x in values] fm_list = [x[4] for x in values] sum_c = sum(fm_list) 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 in datas.keys(): print(key) print(' 父题序号 父选项序号 相关系系数 分子值 分母值 百分比 人数权重 偏离值') values = [x[5] for x in datas[key]] min_c = min(values) f = min_c for value in datas[key]: print(' {}'.format(value)) if value[2] is not None and value[7] is not None: f += float(value[2] * value[7]) print(' 标准分：{}'.format(f)) scores.append([city, age, key, crowd_type, f]) # self.shangju_db.add_some(self.sql_9, scores) return scores 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): # 获取每个答题者所答题的子选项id 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 = str(people[1]).split(',') # list(set(a).intersection(set(b))) if len(list(set(sub_option_ids).intersection(set(type_sub_option_ids)))) > 0: 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(sub_option_id) infos[key] = sub_option_ids return infos if __name__ == '__main__': mvp = Mvp() mvp.write_tag()