rov-offline/region_rule_rank_h.py

# -*- coding: utf-8 -*-
# @ModuleName: region_rule_rank_h
# @Author: Liqian
# @Time: 2022/5/5 15:54
# @Software: PyCharm

import datetime
import pandas as pd
import math
from odps import ODPS
from threading import Timer
from utils import MysqlHelper, RedisHelper, get_data_from_odps
from config import set_config
from log import Log

config_, _ = set_config()
log_ = Log()

region_code = {
    '河北省': '130000',
    '山西省': '140000',
    '辽宁省': '210000',
    '吉林省': '220000',
    '黑龙江省': '230000',
    '江苏省': '320000',
    '浙江省': '330000',
    '安徽省': '340000',
    '福建省': '350000',
    '江西省': '360000',
    '山东省': '370000',
    '河南省': '410000',
    '湖北省': '420000',
    '湖南省': '430000',
    '广东省': '440000',
    '海南省': '460000',
    '四川省': '510000',
    '贵州省': '520000',
    '云南省': '530000',
    '陕西省': '610000',
    '甘肃省': '620000',
    '青海省': '630000',
    '台湾省': '710000',
    '北京': '110000',
    '天津': '120000',
    '内蒙古': '150000',
    '上海': '310000',
    '广西': '450000',
    '重庆': '500000',
    '西藏': '540000',
    '宁夏': '640000',
    '新疆': '650000',
    '香港': '810000',
    '澳门': '820000',
}

features = [
    'videoid',
    'lastonehour_preview',  # 过去1小时预曝光人数
    'lastonehour_view',  # 过去1小时曝光人数
    'lastonehour_play',  # 过去1小时播放人数
    'lastonehour_share',  # 过去1小时分享人数
    'lastonehour_return',  # 过去1小时分享，过去1小时回流人数
    'lastonehour_preview_total_final',  # 过去1小时预曝光次数
    'lastonehour_view_total_final',  # 过去1小时曝光次数
    'lastonehour_play_total_final',  # 过去1小时播放次数
    'lastonehour_share_total_final',  # 过去1小时分享次数
]


def get_region_code(region):
    """获取省份对应的code"""
    mysql_helper = MysqlHelper(mysql_info=config_.MYSQL_INFO)
    sql = f"SELECT ad_code FROM region_adcode WHERE parent_id = 0 AND region LIKE '{region}%';"
    ad_code = mysql_helper.get_data(sql=sql)
    return ad_code[0][0]


def h_data_check(project, table, now_date):
    """检查数据是否准备好"""
    odps = ODPS(
        access_id=config_.ODPS_CONFIG['ACCESSID'],
        secret_access_key=config_.ODPS_CONFIG['ACCESSKEY'],
        project=project,
        endpoint=config_.ODPS_CONFIG['ENDPOINT'],
        connect_timeout=3000,
        read_timeout=500000,
        pool_maxsize=1000,
        pool_connections=1000
    )

    try:
        dt = datetime.datetime.strftime(now_date, '%Y%m%d%H')
        sql = f'select * from {project}.{table} where dt = {dt}'
        with odps.execute_sql(sql=sql).open_reader() as reader:
            data_count = reader.count
    except Exception as e:
        data_count = 0
    return data_count


def get_rov_redis_key(now_date):
    """获取rov模型结果存放key"""
    redis_helper = RedisHelper()
    now_dt = datetime.datetime.strftime(now_date, '%Y%m%d')
    key_name = f'{config_.RECALL_KEY_NAME_PREFIX}{now_dt}'
    if not redis_helper.key_exists(key_name=key_name):
        pre_dt = datetime.datetime.strftime(now_date - datetime.timedelta(days=1), '%Y%m%d')
        key_name = f'{config_.RECALL_KEY_NAME_PREFIX}{pre_dt}'
    return key_name


def get_feature_data(project, table, now_date):
    """获取特征数据"""
    dt = datetime.datetime.strftime(now_date, '%Y%m%d%H')
    # dt = '2022041310'
    records = get_data_from_odps(date=dt, project=project, table=table)
    feature_data = []
    for record in records:
        item = {}
        for feature_name in features:
            item[feature_name] = record[feature_name]
        feature_data.append(item)
    feature_df = pd.DataFrame(feature_data)
    return feature_df


def cal_score(df):
    """
    计算score
    :param df: 特征数据
    :return:
    """
    # score计算公式: sharerate*backrate*logback*ctr
    # sharerate = lastonehour_share/(lastonehour_play+1000)
    # backrate = lastonehour_return/(lastonehour_share+10)
    # ctr = lastonehour_play/(lastonehour_preview+1000), 对ctr限最大值：K2 = 0.6 if ctr > 0.6 else ctr
    # score = sharerate * backrate * LOG(lastonehour_return+1) * K2

    df = df.fillna(0)
    df['share_rate'] = df['lastonehour_share'] / (df['lastonehour_play'] + 1000)
    df['back_rate'] = df['lastonehour_return'] / (df['lastonehour_share'] + 10)
    df['log_back'] = (df['lastonehour_return'] + 1).apply(math.log)
    df['ctr'] = df['lastonehour_play'] / (df['lastonehour_preview'] + 1000)
    df['K2'] = df['ctr'].apply(lambda x: 0.6 if x > 0.6 else x)
    df['score'] = df['share_rate'] * df['back_rate'] * df['log_back'] * df['K2']
    df = df.sort_values(by=['score'], ascending=False)
    return df


def video_rank(df, now_date, now_h, rule_key, param, region):
    """
    获取符合进入召回源条件的视频，与每日更新的rov模型结果视频列表进行合并
    :param df:
    :param now_date:
    :param now_h:
    :param rule_key: 小时级数据进入条件
    :param param: 小时级数据进入条件参数
    :param region: 所属地域
    :return:
    """
    # 获取rov模型结果
    redis_helper = RedisHelper()
    key_name = get_rov_redis_key(now_date=now_date)
    initial_data = redis_helper.get_data_zset_with_index(key_name=key_name, start=0, end=-1, with_scores=True)
    log_.info(f'initial data count = {len(initial_data)}')

    # 获取符合进入召回源条件的视频，进入条件：小时级回流>=20 && score>=0.005
    return_count = param.get('return_count')
    score_value = param.get('score_rule')
    h_recall_df = df[(df['lastonehour_return'] >= return_count) & (df['score'] >= score_value)]
    h_recall_videos = h_recall_df['videoid'].to_list()
    log_.info(f'h_recall videos count = {len(h_recall_videos)}')
    # 写入对应的redis
    h_video_ids =[]
    h_recall_result = {}
    for video_id in h_recall_videos:
        score = h_recall_df[h_recall_df['videoid'] == video_id]['score']
        h_recall_result[int(video_id)] = float(score)
        h_video_ids.append(int(video_id))
    h_recall_key_name = \
        f"{config_.RECALL_KEY_NAME_PREFIX_REGION_BY_H}{region}.{rule_key}.{datetime.datetime.strftime(now_date, '%Y%m%d')}.{now_h}"
    if len(h_recall_result) > 0:
        redis_helper.add_data_with_zset(key_name=h_recall_key_name, data=h_recall_result, expire_time=23 * 3600)
        # 清空线上过滤应用列表
        redis_helper.del_keys(key_name=f"{config_.REGION_H_VIDEO_FILER}{region}.{rule_key}")

    # 去重更新rov模型结果，并另存为redis中
    initial_data_dup = {}
    for video_id, score in initial_data:
        if int(video_id) not in h_video_ids:
            initial_data_dup[int(video_id)] = score
    log_.info(f"initial data dup count = {len(initial_data_dup)}")
    initial_key_name = \
        f"{config_.RECALL_KEY_NAME_PREFIX_DUP_REGION_H}{region}.{rule_key}.{datetime.datetime.strftime(now_date, '%Y%m%d')}.{now_h}"
    if len(initial_data_dup) > 0:
        redis_helper.add_data_with_zset(key_name=initial_key_name, data=initial_data_dup, expire_time=23 * 3600)


def rank_by_h(project, table, now_date, now_h, rule_params, region_code_list):
    # 获取特征数据
    feature_df = get_feature_data(project=project, table=table, now_date=now_date)
    # 获取所有的region
    # region_code_list = list(set(feature_df[''].to_list()))
    # rank
    for key, value in rule_params.items():
        log_.info(f"rule = {key}, param = {value}")
        for region in region_code_list:
            log_.info(f"region = {region}")
            # 计算score
            score_df = cal_score(df=feature_df)
            video_rank(df=score_df, now_date=now_date, now_h=now_h, rule_key=key, param=value, region=region)
            # to-csv
            score_filename = f"score_{region}_{key}_{datetime.datetime.strftime(now_date, '%Y%m%d%H')}.csv"
            score_df.to_csv(f'./data/{score_filename}')


def h_rank_bottom(now_date, now_h, rule_key, region_code_list):
    """未按时更新数据，用上一小时结果作为当前小时的数据"""
    log_.info(f"rule_key = {rule_key}")
    # 获取rov模型结果
    redis_helper = RedisHelper()
    if now_h == 0:
        redis_dt = datetime.datetime.strftime(now_date - datetime.timedelta(days=1), '%Y%m%d')
        redis_h = 23
    else:
        redis_dt = datetime.datetime.strftime(now_date, '%Y%m%d')
        redis_h = now_h - 1

    key_prefix_list = [config_.RECALL_KEY_NAME_PREFIX_REGION_BY_H, config_.RECALL_KEY_NAME_PREFIX_DUP_REGION_H]
    # fea_df = get_feature_data(project=project, table=table, now_date=now_date - datetime.timedelta(hours=1))
    # region_list = list(set(fea_df[''].to_list()))
    for region in region_code_list:
        log_.info(f"region = {region}")
        for key_prefix in key_prefix_list:
            key_name = f"{key_prefix}{region}.{rule_key}.{redis_dt}.{redis_h}"
            initial_data = redis_helper.get_data_zset_with_index(key_name=key_name, start=0, end=-1, with_scores=True)
            final_data = dict()
            for video_id, score in initial_data:
                final_data[video_id] = score
            # 存入对应的redis
            final_key_name = \
                f"{key_prefix}{region}.{rule_key}.{datetime.datetime.strftime(now_date, '%Y%m%d')}.{now_h}"
            if len(final_data) > 0:
                redis_helper.add_data_with_zset(key_name=final_key_name, data=final_data, expire_time=23 * 3600)
        # 清空线上过滤应用列表
        redis_helper.del_keys(key_name=f"{config_.REGION_H_VIDEO_FILER}{region}.{rule_key}")


def h_timer_check():
    rule_params = config_.RULE_PARAMS_REGION
    project = config_.PROJECT_REGION
    table = config_.TABLE_REGION
    region_code_list = [code for region, code in region_code.items()]
    now_date = datetime.datetime.today()
    log_.info(f"now_date: {datetime.datetime.strftime(now_date, '%Y%m%d%H')}")
    now_h = datetime.datetime.now().hour
    now_min = datetime.datetime.now().minute
    if now_h == 0:
        for key, _ in rule_params.items():
            h_rank_bottom(now_date=now_date, now_h=now_h, rule_key=key, region_code_list=region_code_list)
        return
    # 查看当前小时更新的数据是否已准备好
    h_data_count = h_data_check(project=project, table=table, now_date=now_date)
    if h_data_count > 0:
        log_.info(f'h_data_count = {h_data_count}')
        # 数据准备好，进行更新
        rank_by_h(now_date=now_date, now_h=now_h, rule_params=rule_params,
                  project=project, table=table, region_code_list=region_code_list)
    elif now_min > 50:
        log_.info('h_recall data is None, use bottom data!')
        for key, _ in rule_params.items():
            h_rank_bottom(now_date=now_date, now_h=now_h, rule_key=key, region_code_list=region_code_list)
    else:
        # 数据没准备好，1分钟后重新检查
        Timer(60, h_timer_check).start()


if __name__ == '__main__':
    h_timer_check()