def get_table(search_start_date=None, search_end_date=None, server_id=-1, register_start_date=None, register_end_date=None): table_result = [] total_days = (search_end_date-search_start_date).days+1 for _day in xrange(total_days): # -------------总体----------------- total_line = [] new_user_line = [] add_line = [] rate_line = [] # 每行的日期 row_date = search_start_date + datetime.timedelta(days=_day) date_str = '_'+row_date.strftime('%Y%m%d') # 插入数据 total_line.append(row_date.strftime('%Y-%m-%d')) new_user_line.append(row_date.strftime('%Y-%m-%d')) add_line.append(row_date.strftime('%Y-%m-%d')) rate_line.append(row_date.strftime('%Y-%m-%d')) # 首冲 first_recharge = mysql_util.get_first_recharge_shop_index_uid_num('uid', 'EVENT_ACTION_RECHARGE_PLAYER'+str(date_str), row_date, 'shop_index', -1, -1, server_id, register_start_date, register_end_date) # 月卡 month_card_recharge = mysql_util.get_recharge_shop_index_uid_num('uid', 'EVENT_ACTION_RECHARGE_PLAYER'+str(date_str), row_date, 'shop_index', 1, -1, server_id, register_start_date, register_end_date) # vip总体登录 total_line.extend(['总体', first_recharge, month_card_recharge]) vip_level_lst = [0]*13 uid_level_lst = mysql_util.get_vip_distributed_uid('EVENT_ACTION_ROLE_LOGIN'+str(date_str), -1, server_id, register_start_date, register_end_date) for _uid_level in uid_level_lst: _vip_level = int(_uid_level['vip_level']) vip_level_lst[_vip_level] = int(_uid_level['count(uid)']) total_line.extend(vip_level_lst) # -------------新增----------------- new_install_num = mysql_util.get_today_new_num('uid', 'EVENT_ACTION_ROLE_LOGIN'+str(date_str), row_date, -1, server_id) add_line.extend(['今日新增用户']) add_line.extend([new_install_num] * 15) # 首冲 first_recharge = mysql_util.get_first_recharge_shop_index_uid_num('uid', 'EVENT_ACTION_RECHARGE_PLAYER'+str(date_str), row_date, 'shop_index', -1, -1, server_id, row_date, row_date) # 月卡 month_card_recharge = mysql_util.get_recharge_shop_index_uid_num('uid', 'EVENT_ACTION_RECHARGE_PLAYER'+str(date_str), row_date, 'shop_index', 1, -1, server_id, row_date, row_date) # vip 新增充值 new_user_line.extend(['新增', first_recharge, month_card_recharge]) new_vip_level_lst = [0]*13 new_uid_level_lst = mysql_util.get_vip_distributed_uid('EVENT_ACTION_ROLE_LOGIN'+str(date_str), -1, server_id, row_date, row_date) for _uid_level in new_uid_level_lst: _vip_level = int(_uid_level['vip_level']) new_vip_level_lst[_vip_level] = int(_uid_level['count(uid)']) new_user_line.extend(new_vip_level_lst) # -------------比率----------------- rate_line.extend(['比率']) for _index in xrange(2, 17): rate = division(new_user_line[_index], add_line[_index]) rate_line.append(str(rate*100)+'%') table_result.extend([total_line, new_user_line, add_line, rate_line]) return table_result
def get_row_lst(row_date, channel_id, server_id): date_str = "_"+row_date.strftime('%Y%m%d') row_lst = [] rate_row_lst = [] # 日期 row_date = row_date # 获取玩家安装游戏日期 row_lst.append(row_date.strftime('%Y-%m-%d')) rate_row_lst.append(row_date.strftime('%Y-%m-%d')+'比率') for day in xrange(1, 31): install_date = row_date - datetime.timedelta(days=day) install_date_str = "_"+install_date.strftime('%Y%m%d') retained_num = mysql_util.get_retained_num('uid', 'EVENT_ACTION_ROLE_LOGIN'+str(date_str), install_date, row_date, game_define.EVENT_ACTION_ROLE_LOGIN, channel_id, server_id) today_new_user_num = mysql_util.get_today_new_num('uid', 'EVENT_ACTION_ROLE_LOGIN'+str(install_date_str), install_date, channel_id, server_id) row_lst.append(retained_num) retain_rate = Division(retained_num, today_new_user_num) rate_row_lst.append(str(retain_rate*100)+'%') return row_lst, rate_row_lst
def get_row_lst(row_date, channel_id, server_id): date_str = "_" + row_date.strftime('%Y%m%d') row_lst = [] rate_row_lst = [] # 日期 row_date = row_date # 获取玩家安装游戏日期 row_lst.append(row_date.strftime('%Y-%m-%d')) rate_row_lst.append(row_date.strftime('%Y-%m-%d') + '比率') for day in xrange(1, 31): install_date = row_date - datetime.timedelta(days=day) install_date_str = "_" + install_date.strftime('%Y%m%d') retained_num = mysql_util.get_retained_num( 'uid', 'EVENT_ACTION_ROLE_LOGIN' + str(date_str), install_date, row_date, game_define.EVENT_ACTION_ROLE_LOGIN, channel_id, server_id) today_new_user_num = mysql_util.get_today_new_num( 'uid', 'EVENT_ACTION_ROLE_LOGIN' + str(install_date_str), install_date, channel_id, server_id) row_lst.append(retained_num) retain_rate = Division(retained_num, today_new_user_num) rate_row_lst.append(str(retain_rate * 100) + '%') return row_lst, rate_row_lst
def get_row_lst(row_date, channel_id, server_id): date_str = "_"+row_date.strftime('%Y%m%d') row_lst = [] # 日期 row_date = row_date # 今天登录设备数 today_device_num = mysql_util.get_today_num('dev_id', 'EVENT_ACTION_ROLE_LOGIN'+str(date_str), row_date, game_define.EVENT_ACTION_ROLE_LOGIN, channel_id, server_id) # 今天的登录用户数 today_user_num = mysql_util.get_today_num('uid', 'EVENT_ACTION_ROLE_LOGIN'+str(date_str), row_date, game_define.EVENT_ACTION_ROLE_LOGIN, channel_id, server_id) # 今天新增用户数 today_new_user_num = mysql_util.get_today_new_num('uid', 'EVENT_ACTION_ROLE_LOGIN'+str(date_str), row_date, channel_id, server_id) #新设备数 # today_new_device_num = mysql_util.get_all_count('dev_id', 'EVENT_ACTION_ROLE_LOGIN'+str(date_str), row_date + datetime.timedelta(days=1), channel_id, server_id) - mysql_util.get_all_count('dev_id','EVENT_ACTION_ROLE_LOGIN'+str(date_str),row_date, channel_id, server_id) today_new_device_num = mysql_util.get_today_new_num('dev_id', 'EVENT_ACTION_ROLE_LOGIN'+str(date_str), row_date, channel_id, server_id) # 新账户数 # today_new_account_num = mysql_util.get_all_count('account_id', 'EVENT_ACTION_ROLE_LOGIN'+str(date_str), row_date + datetime.timedelta(days=1), channel_id, server_id) - mysql_util.get_all_count('account_id','EVENT_ACTION_ROLE_LOGIN'+str(date_str),row_date, channel_id, server_id) today_new_account_num = mysql_util.get_today_new_num('account_id', 'EVENT_ACTION_ROLE_LOGIN'+str(date_str), row_date, channel_id, server_id) # print("today_new_account_num: "+str(today_new_account_num)) # 今天的登录账户 today_account_num = mysql_util.get_today_num('account_id', 'EVENT_ACTION_ROLE_LOGIN'+str(date_str), row_date, game_define.EVENT_ACTION_ROLE_LOGIN, channel_id, server_id) # print("today_account_num: "+str(today_account_num)) # 今天充值人数 today_recharge_user_num = mysql_util.get_recharge_uid_num('uid', 'EVENT_ACTION_RECHARGE_PLAYER'+str(date_str), row_date, channel_id, server_id) # 今天新增充值人数 today_new_recharge_user_num = mysql_util.get_new_recharge_user_num('uid', 'EVENT_ACTION_RECHARGE_PLAYER'+str(date_str), row_date, channel_id, server_id) # 充值金额 today_recharge_rmb = mysql_util.get_sum('add_rmb', row_date, 'EVENT_ACTION_RECHARGE_PLAYER'+str(date_str), game_define.EVENT_ACTION_RECHARGE_PLAYER, channel_id, server_id) # 新增充值金额 today_new_recharge_rmb = mysql_util.get_new_sum('add_rmb', row_date, 'cur_rmb', 'add_rmb', 'EVENT_ACTION_RECHARGE_PLAYER'+str(date_str), game_define.EVENT_ACTION_RECHARGE_PLAYER, channel_id, server_id) # 今天登录事件次数 today_login_action_num = mysql_util.get_all_count('id', 'EVENT_ACTION_ROLE_LOGIN'+str(date_str), row_date + datetime.timedelta(days=1), channel_id, server_id) # get_sum('uid',row_date,table_name= 'EVENT_ACTION_ROLE_LOGIN', action=game_define.EVENT_ACTION_ROLE_LOGIN) # 活跃设备数 today_active_device_num = today_device_num - today_new_device_num # 活跃用户数 today_active_user_num = today_user_num - today_new_user_num # 登录用户数 today_login_user_num = today_user_num # 登录设备数 today_login_device_num = today_device_num # 活跃账户数 today_active_account_num = today_account_num - today_new_account_num # 付费率=充值人数/登录设备 pay_rate = str(100*(Division(today_recharge_user_num, today_login_device_num)))+"%" # 付费arppu 充值金额/充值人数 pay_arppu = Division(today_recharge_rmb, today_recharge_user_num) # 登录arpu 充值金额/登陆设备 login_arpu = Division(today_recharge_rmb, today_login_device_num) # 半小时精度在线人数列表 # online_user_num_lst = daily_log_dat.get_online_user_len_lst(row_date, today_log_lst) # online_user_num_lst = [0] * 48 # 平均在线人数 acu # acu = _get_acu(online_user_num_lst) # 最高在线人数 # pcu = _get_pcu(online_user_num_lst) # 平均在线时长 # avg_online_time = _get_avg_online_time(online_user_num_lst, today_login_user_num) # 人均登入次数 avg_login_count = _get_avg_login_count(today_login_action_num, today_login_user_num) row_lst.append(row_date.strftime('%Y-%m-%d')) row_lst.append(today_active_device_num) row_lst.append(today_active_user_num) row_lst.append(today_new_device_num) row_lst.append(today_new_user_num) row_lst.append(today_login_device_num) row_lst.append(today_login_user_num) row_lst.append(today_active_account_num) row_lst.append(today_new_account_num) row_lst.append(today_recharge_user_num) row_lst.append(today_new_recharge_user_num) row_lst.append(today_recharge_rmb) row_lst.append(today_new_recharge_rmb) row_lst.append(pay_rate) row_lst.append(pay_arppu) row_lst.append(login_arpu) # row_lst.append(acu) # row_lst.append(pcu) # row_lst.append(avg_online_time) row_lst.append(avg_login_count) return row_lst
def get_table(search_start_date, search_end_date, channel_id=-1, server_id=-1): """ 获取表格 """ # 获取搜索区间日志 today_data = time.localtime() today = datetime.datetime(today_data.tm_year, today_data.tm_mon, today_data.tm_mday) search_total_day = (search_end_date - search_start_date).days table_lst = [] for i in xrange(search_total_day + 1): _register_date = search_start_date + datetime.timedelta(days=1 * i) date_str = "_" + _register_date.strftime('%Y%m%d') # 获取当天日志 # _row_log_lst = mysql_util.get_role_action_lst('EVENT_ACTION_ROLE_LOGIN'+str(date_str), _search_date, _search_date, channel_id, server_id, None, None) # 获取新用户日志 # new_user_lst = daily_log_dat.get_set_with_key(_row_log_lst, 'uid', function=lambda log: log['install'] == _search_date) # new_user_num = len(new_user_lst) new_user_num = mysql_util.get_today_new_num('uid', 'EVENT_ACTION_ROLE_LOGIN' + str(date_str), _register_date, channel_id, server_id) # print new_user_num 获取新增登录用户 ltv_day_lst = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, 30, 60] ltv_val_lst = [] for ltv_days in ltv_day_lst: # start_date + ltv_day_lst if > today return ltv_val_lst.append('-') start_date = _register_date end_date = _register_date + datetime.timedelta(days=ltv_days) total_recharge_money = 0 for _day in xrange((end_date - start_date).days): search_date = start_date + datetime.timedelta(days=_day) date_str = "_" + search_date.strftime('%Y%m%d') total_recharge_money += mysql_util.get_install_recharge_sum('add_rmb', _register_date, 'EVENT_ACTION_RECHARGE_PLAYER' + str( date_str), channel_id, server_id, None) # total_recharge_money += daily_log_dat.get_sum_int_with_key(ltv_recharge_log_lst, 'add_rmb') ltv_val = _get_ltv_value(new_user_num, total_recharge_money) ltv_val_lst.append(ltv_val) today = str(today).split(' ')[0] start_day = datetime.datetime(int(str(start_date).split('-')[0]), int(str(start_date).split('-')[1]), int(str(start_date).split('-')[2])) today = datetime.datetime(int(today.split('-')[0]), int(today.split('-')[1]), int(today.split('-')[2])) account = (today - start_day).days + 1 if len(ltv_val_lst) > account: row_lst.append('-') else: row_lst = [_register_date.strftime('%Y-%m-%d'), new_user_num] row_lst.extend(ltv_val_lst) table_lst.append(row_lst) return table_lst
def get_table(search_start_date=None, search_end_date=None, server_id=-1, register_start_date=None, register_end_date=None): table_result = [] total_days = (search_end_date - search_start_date).days + 1 for _day in xrange(total_days): # -------------总体----------------- total_line = [] new_user_line = [] add_line = [] rate_line = [] # 每行的日期 row_date = search_start_date + datetime.timedelta(days=_day) date_str = '_' + row_date.strftime('%Y%m%d') # 插入数据 total_line.append(row_date.strftime('%Y-%m-%d')) new_user_line.append(row_date.strftime('%Y-%m-%d')) add_line.append(row_date.strftime('%Y-%m-%d')) rate_line.append(row_date.strftime('%Y-%m-%d')) # 首冲 first_recharge = mysql_util.get_first_recharge_shop_index_uid_num( 'uid', 'EVENT_ACTION_RECHARGE_PLAYER' + str(date_str), row_date, 'shop_index', -1, -1, server_id, register_start_date, register_end_date) # 月卡 month_card_recharge = mysql_util.get_recharge_shop_index_uid_num( 'uid', 'EVENT_ACTION_RECHARGE_PLAYER' + str(date_str), row_date, 'shop_index', 1, -1, server_id, register_start_date, register_end_date) # vip总体登录 total_line.extend(['总体', first_recharge, month_card_recharge]) vip_level_lst = [0] * 13 uid_level_lst = mysql_util.get_vip_distributed_uid( 'EVENT_ACTION_ROLE_LOGIN' + str(date_str), -1, server_id, register_start_date, register_end_date) for _uid_level in uid_level_lst: _vip_level = int(_uid_level['vip_level']) vip_level_lst[_vip_level] = int(_uid_level['count(uid)']) total_line.extend(vip_level_lst) # -------------新增----------------- new_install_num = mysql_util.get_today_new_num( 'uid', 'EVENT_ACTION_ROLE_LOGIN' + str(date_str), row_date, -1, server_id) add_line.extend(['今日新增用户']) add_line.extend([new_install_num] * 15) # 首冲 first_recharge = mysql_util.get_first_recharge_shop_index_uid_num( 'uid', 'EVENT_ACTION_RECHARGE_PLAYER' + str(date_str), row_date, 'shop_index', -1, -1, server_id, row_date, row_date) # 月卡 month_card_recharge = mysql_util.get_recharge_shop_index_uid_num( 'uid', 'EVENT_ACTION_RECHARGE_PLAYER' + str(date_str), row_date, 'shop_index', 1, -1, server_id, row_date, row_date) # vip 新增充值 new_user_line.extend(['新增', first_recharge, month_card_recharge]) new_vip_level_lst = [0] * 13 new_uid_level_lst = mysql_util.get_vip_distributed_uid( 'EVENT_ACTION_ROLE_LOGIN' + str(date_str), -1, server_id, row_date, row_date) for _uid_level in new_uid_level_lst: _vip_level = int(_uid_level['vip_level']) new_vip_level_lst[_vip_level] = int(_uid_level['count(uid)']) new_user_line.extend(new_vip_level_lst) # -------------比率----------------- rate_line.extend(['比率']) for _index in xrange(2, 17): rate = division(new_user_line[_index], add_line[_index]) rate_line.append(str(rate * 100) + '%') table_result.extend([total_line, new_user_line, add_line, rate_line]) return table_result
def get_table(search_start_date, search_end_date, channel_id=-1, server_id=-1): """ 获取表格 """ # 获取搜索区间日志 today_data = time.localtime() today = datetime.datetime(today_data.tm_year, today_data.tm_mon, today_data.tm_mday) search_total_day = (search_end_date - search_start_date).days table_lst = [] for i in xrange(search_total_day + 1): _register_date = search_start_date + datetime.timedelta(days=1 * i) date_str = "_" + _register_date.strftime('%Y%m%d') # 获取当天日志 # _row_log_lst = mysql_util.get_role_action_lst('EVENT_ACTION_ROLE_LOGIN'+str(date_str), _search_date, _search_date, channel_id, server_id, None, None) # 获取新用户日志 # new_user_lst = daily_log_dat.get_set_with_key(_row_log_lst, 'uid', function=lambda log: log['install'] == _search_date) # new_user_num = len(new_user_lst) new_user_num = mysql_util.get_today_new_num( 'uid', 'EVENT_ACTION_ROLE_LOGIN' + str(date_str), _register_date, channel_id, server_id) # print new_user_num 获取新增登录用户 ltv_day_lst = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, 30, 60] ltv_val_lst = [] for ltv_days in ltv_day_lst: # start_date + ltv_day_lst if > today return ltv_val_lst.append('-') start_date = _register_date end_date = _register_date + datetime.timedelta(days=ltv_days) total_recharge_money = 0 for _day in xrange((end_date - start_date).days): search_date = start_date + datetime.timedelta(days=_day) date_str = "_" + search_date.strftime('%Y%m%d') total_recharge_money += mysql_util.get_install_recharge_sum( 'add_rmb', _register_date, 'EVENT_ACTION_RECHARGE_PLAYER' + str(date_str), channel_id, server_id, None) # total_recharge_money += daily_log_dat.get_sum_int_with_key(ltv_recharge_log_lst, 'add_rmb') ltv_val = _get_ltv_value(new_user_num, total_recharge_money) ltv_val_lst.append(ltv_val) today = str(today).split(' ')[0] start_day = datetime.datetime(int(str(start_date).split('-')[0]), int(str(start_date).split('-')[1]), int(str(start_date).split('-')[2])) today = datetime.datetime(int(today.split('-')[0]), int(today.split('-')[1]), int(today.split('-')[2])) account = (today - start_day).days + 1 if len(ltv_val_lst) > account: row_lst.append('-') else: row_lst = [_register_date.strftime('%Y-%m-%d'), new_user_num] row_lst.extend(ltv_val_lst) table_lst.append(row_lst) return table_lst