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