elab_mvp/mvp.py

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 = ['上海市', '上海周边']

    # 用户画像-消费结构 用户画像-生活方式
    # 需要更新的模块：用户画像-性别、用户画像-行业、用户画像-出行方式、
    # 用户画像-消费结构、用户画像-生活方式、用户画像-社交模式、用户画像-审美偏好
    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 = 249
            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()

    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)
        # 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:
                            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)
                        insert_data.append([crowd_info_id, 1, boy_rate])
                        girl_rate = girl / (boy + girl)
                        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)
                        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):
        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:
                return [id_1, tag_name, tag_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):
        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
        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)
                    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)
                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