def get_new_recharge_user_num(spe_column,
table_name,
row_date,
channel_id=-1,
server_id=-1):
"""
指定日期新增充值人数
today_new_recharge_user_num = daily_log_dat.get_set_num_with_key(today_log_lst, 'uid', game_define.EVENT_ACTION_RECHARGE_PLAYER, lambda log:log['cur_rmb']== log['add_rmb'])
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id) + " and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id) + " and "
connection = mysql_connection.get_log_mysql_connection_haima()
# 数据库表是否存在
sql_exist = "SELECT table_name FROM information_schema.tables where table_schema = 'manager_haima' and table_name = '%s';" % table_name
exist_table = connection.query(sql_exist)
if exist_table == []:
return 0
# 获取次数
sql = "SELECT count(distinct %s) FROM %s where %s action = %s and cur_rmb = add_rmb and log_time >= '%s' and log_time < '%s'" % (
spe_column, table_name, channel_server_str,
game_define.EVENT_ACTION_RECHARGE_PLAYER, row_date,
row_date + datetime.timedelta(days=1))
# print sql
count = connection.query(sql)
# print count
return count[0].values()[0]
def get_recharge_shop_index_uid_num(spe_column,
row_date,
specfy_column_name='shop_index',
shop_index=-1,
channel_id=-1,
server_id=-1):
"""
指定日期指定充值金额档充值人数
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id) + " and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id) + " and "
shop_index_str = ""
if shop_index > 0:
shop_index_str += " and " + str(specfy_column_name) + " = " + str(
shop_index)
connection = mysql_connection.get_log_mysql_connection_haima()
# 获取次数
sql = "SELECT count(%s) FROM EVENT_ACTION_RECHARGE_PLAYER where %s action = %s and log_time >= '%s' and log_time < '%s' %s " % (
spe_column, channel_server_str,
game_define.EVENT_ACTION_RECHARGE_PLAYER, row_date,
row_date + datetime.timedelta(days=1), shop_index_str)
# print sql
count = connection.query(sql)
# print count
return count[0].values()[0]
def get_role_charge_lst(search_date=None,
channel_id=-1,
server_id=-1,
register_start_date=None,
register_end_date=None):
"""
指定日期流失人数
function=lambda log: register_start_date <= log['install'] <= register_end_date)
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id) + " and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id) + " and "
if register_start_date:
channel_server_str += " '%s' <= install and " % register_start_date
if register_end_date:
channel_server_str += " install <= '%s' and " % register_end_date
connection = mysql_connection.get_log_mysql_connection_haima()
# 获取次数
sql = "SELECT * FROM EVENT_ACTION_RECHARGE_PLAYER where %s log_time >= '%s' and log_time < '%s'" % (
channel_server_str, search_date,
search_date + datetime.timedelta(days=1))
print sql
count = connection.query(sql)
# print count
return count
def get_today_num(spe_column,
table_name,
row_date,
action=game_define.EVENT_ACTION_ROLE_LOGIN,
channel_id=-1,
server_id=-1):
"""
指定日期登录设备数
"""
# 获取物品购买次数
# sql = "SELECT COUNT(dev_id) FROM server_world_boss WHERE time >= '%s' and time < '%s'" % (star_time, end_time)
connection = mysql_connection.get_log_mysql_connection_haima()
# 数据库表是否存在
sql_exist = "SELECT table_name FROM information_schema.tables where table_schema = 'manager_haima' and table_name = '%s';" % table_name
exist_table = connection.query(sql_exist)
if exist_table == []:
return 0
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id) + " and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id) + " and "
# 获取次数
sql = "SELECT count(distinct %s) FROM %s where %s action = %s and log_time >= '%s' and log_time < '%s'" % (
spe_column, table_name, channel_server_str, action, row_date,
row_date + datetime.timedelta(days=1))
# print sql
count = connection.query(sql)
# print count
return count[0].values()[0]
def get_role_action_lst(table_name='EVENT_ACTION_ROLE_LOGIN',
from_date=None,
to_date=None,
channel_id=-1,
server_id=-1,
register_start_date=None,
register_end_date=None):
"""
指定日期玩家登陆
function=lambda log: register_start_date <= log['install'] <= register_end_date)
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += " platform_id = " + str(channel_id) + " and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id) + " and "
if register_start_date:
channel_server_str += " '%s' <= install and " % register_start_date
if register_end_date:
channel_server_str += " install <= '%s' and " % register_end_date
connection = mysql_connection.get_log_mysql_connection_haima()
# 数据库表是否存在
sql_exist = "SELECT table_name FROM information_schema.tables where table_schema = 'manager_haima' and table_name = '%s';" % table_name
exist_table = connection.query(sql_exist)
if exist_table == []:
return 0
sql = "SELECT * FROM %s where %s log_time >= '%s' and log_time < '%s'" % (
table_name, channel_server_str, from_date,
to_date + datetime.timedelta(days=1))
# print sql
count = connection.query(sql)
# print (count)
return count
def get_today_new_num(spe_column,
table_name,
row_date,
channel_id=-1,
server_id=-1):
"""
指定日期今天的新增用户数
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id) + " and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id) + " and "
connection = mysql_connection.get_log_mysql_connection_haima()
# 数据库表是否存在
sql_exist = "SELECT table_name FROM information_schema.tables where table_schema = 'manager_haima' and table_name = '%s';" % table_name
exist_table = connection.query(sql_exist)
if exist_table == []:
return 0
# 获取次数
sql = "SELECT count(distinct %s) FROM %s where %s action = %s and install = '%s' and log_time >= '%s' and log_time < '%s'" % (
spe_column, table_name, channel_server_str,
game_define.EVENT_ACTION_ROLE_LOGIN, row_date, row_date,
row_date + datetime.timedelta(days=1))
count = connection.query(sql)
return count[0].values()[0]
def get_role_action_lst(table_name='EVENT_ACTION_ROLE_LOGIN',from_date=None, to_date=None, channel_id=-1, server_id=-1,register_start_date=None, register_end_date=None):
"""
指定日期玩家登陆
function=lambda log: register_start_date <= log['install'] <= register_end_date)
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += " platform_id = " + str(channel_id)+" and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id)+" and "
if register_start_date:
channel_server_str += " '%s' <= install and " % register_start_date
if register_end_date:
channel_server_str += " install <= '%s' and " % register_end_date
connection = mysql_connection.get_log_mysql_connection_haima()
# 数据库表是否存在
sql_exist = "SELECT table_name FROM information_schema.tables where table_schema = 'manager_haima' and table_name = '%s';" % table_name
exist_table = connection.query(sql_exist)
if exist_table == []:
return 0
sql = "SELECT * FROM %s where %s log_time >= '%s' and log_time < '%s'" % (table_name,channel_server_str, from_date,to_date + datetime.timedelta(days=1))
# print sql
count = connection.query(sql)
# print (count)
return count
def get_new_sum(spe_column,
row_date,
cur_val,
add_val,
table_name='EVENT_ACTION_RECHARGE_PLAYER',
action=game_define.EVENT_ACTION_RECHARGE_PLAYER,
channel_id=-1,
server_id=-1):
"""
指定日期充值金额
today_recharge_rmb = daily_log_dat.get_sum_int_with_key(today_log_lst, 'add_rmb', game_define.EVENT_ACTION_RECHARGE_PLAYER)
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id) + " and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id) + " and "
connection = mysql_connection.get_log_mysql_connection_haima()
# 数据库表是否存在
sql_exist = "SELECT table_name FROM information_schema.tables where table_schema = 'manager_haima' and table_name = '%s';" % table_name
exist_table = connection.query(sql_exist)
if exist_table == []:
return 0
# 获取次数
sql = "SELECT sum(%s) FROM %s where %s action = %s and %s = %s and log_time >= '%s' and log_time < '%s'" % (
spe_column, table_name, channel_server_str, action, cur_val, add_val,
row_date, row_date + datetime.timedelta(days=1))
# print sql
count = connection.query(sql)
if not count[0].values()[0]:
return 0
# print count
return count[0].values()[0]
def get_spec_sum(spe_column=None,
from_date=None,
to_date=None,
specfy_column_name='shop_index',
shop_index=-1,
table_name='EVENT_ACTION_RECHARGE_PLAYER',
action=game_define.EVENT_ACTION_RECHARGE_PLAYER,
channel_id=-1,
server_id=-1,
register_start_date=None,
register_end_date=None,
ext=None):
"""
指定日期指定充值档充值金额金额总数
function=lambda log: register_start_date <= log['install'] <= register_end_date)
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id) + " and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id) + " and "
if register_start_date:
channel_server_str += " '%s' <= install and " % register_start_date
if register_end_date:
channel_server_str += " install <= '%s' and " % register_end_date
if spe_column == 'add_rmb':
spe_column = 'sum(add_rmb) '
if spe_column == 'uid':
spe_column = 'count(distinct uid) '
if not spe_column:
spe_column = 'count(uid) '
shop_index_str = ""
if shop_index > 0:
shop_index_str += " and " + str(specfy_column_name) + str(shop_index)
# 扩展字段
ext_str = ""
if ext:
ext_str += " and %s " % ext
connection = mysql_connection.get_log_mysql_connection_haima()
# 数据库表是否存在
sql_exist = "SELECT table_name FROM information_schema.tables where table_schema = 'manager_haima' and table_name = '%s';" % table_name
exist_table = connection.query(sql_exist)
if exist_table == []:
return 0
# 获取次数
sql = "SELECT %s FROM %s where %s action = %s and log_time >= '%s' and log_time < '%s' %s %s" % (
spe_column, table_name, channel_server_str, action, from_date, to_date,
shop_index_str, ext_str)
print sql
count = connection.query(sql)
if not count[0].values()[0]:
return 0
# print count
return count[0].values()[0]
def search(search_data,server_id):
'''
查询时间段内的所有数据
'''
connect = mysql_connection.get_log_mysql_connection_haima()
sql = r"select uid,add_rmb from EVENT_ACTION_RECHARGE_PLAYER_{search_data_no} where log_time > date'{search_data}' and log_time < date_add('{search_data}', interval 1 day) and server_id = {server_id};"\
.format(
search_data = search_data,
server_id = server_id,
search_data_no = str(search_data).replace('-','')
)
return connect.query(sql)
def get_recharge_uid_num(spe_column,table_name, row_date, channel_id=-1, server_id=-1):
"""
指定日期充值人数
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id)+" and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id)+" and "
connection = mysql_connection.get_log_mysql_connection_haima()
# 获取次数
sql = "SELECT count(distinct %s) FROM %s where %s action = %s and log_time >= '%s' and log_time < '%s'" % (spe_column ,table_name, channel_server_str,game_define.EVENT_ACTION_RECHARGE_PLAYER, row_date, row_date + datetime.timedelta(days=1))
# print sql
count = connection.query(sql)
# print count
return count[0].values()[0]
def get_spec_sum(spe_column=None,from_date=None,to_date=None,specfy_column_name='shop_index', shop_index = -1,table_name= 'EVENT_ACTION_RECHARGE_PLAYER', action=game_define.EVENT_ACTION_RECHARGE_PLAYER, channel_id=-1, server_id=-1,register_start_date=None, register_end_date=None,ext=None):
"""
指定日期指定充值档充值金额金额总数
function=lambda log: register_start_date <= log['install'] <= register_end_date)
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id)+" and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id)+" and "
if register_start_date:
channel_server_str += " '%s' <= install and " % register_start_date
if register_end_date:
channel_server_str += " install <= '%s' and " % register_end_date
if spe_column == 'add_rmb':
spe_column = 'sum(add_rmb) '
if spe_column == 'uid':
spe_column = 'count(distinct uid) '
if not spe_column:
spe_column = 'count(uid) '
shop_index_str = ""
if shop_index > 0:
shop_index_str += " and " + str(specfy_column_name) + str(shop_index)
# 扩展字段
ext_str = ""
if ext:
ext_str += " and %s " %ext
connection = mysql_connection.get_log_mysql_connection_haima()
# 数据库表是否存在
sql_exist = "SELECT table_name FROM information_schema.tables where table_schema = 'manager_haima' and table_name = '%s';" % table_name
exist_table = connection.query(sql_exist)
if exist_table == []:
return 0
# 获取次数
sql = "SELECT %s FROM %s where %s action = %s and log_time >= '%s' and log_time < '%s' %s %s" % (spe_column,table_name,channel_server_str,action, from_date, to_date,shop_index_str,ext_str)
print sql
count = connection.query(sql)
if not count[0].values()[0]:
return 0
# print count
return count[0].values()[0]
def get_all_count(spe_column,table_name, row_date, channel_id=-1, server_id=-1):
"""
指定截止到日期获取开始的所有设备数
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id)+" and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id)+" and "
connection = mysql_connection.get_log_mysql_connection_haima()
# 数据库表是否存在
sql_exist = "SELECT table_name FROM information_schema.tables where table_schema = 'manager_haima' and table_name = '%s';" % table_name
exist_table = connection.query(sql_exist)
if exist_table == []:
return 0
# 获取次数
sql = "SELECT count(distinct %s) FROM %s where %s action = %s and log_time < '%s'" % (spe_column,table_name,channel_server_str ,game_define.EVENT_ACTION_ROLE_LOGIN , row_date)
count = connection.query(sql)
return count[0].values()[0]
def get_role_charge_lst(search_date=None, channel_id=-1, server_id=-1,register_start_date=None, register_end_date=None):
"""
指定日期流失人数
function=lambda log: register_start_date <= log['install'] <= register_end_date)
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id)+" and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id)+" and "
if register_start_date:
channel_server_str += " '%s' <= install and " % register_start_date
if register_end_date:
channel_server_str += " install <= '%s' and " % register_end_date
connection = mysql_connection.get_log_mysql_connection_haima()
# 获取次数
sql = "SELECT * FROM EVENT_ACTION_RECHARGE_PLAYER where %s log_time >= '%s' and log_time < '%s'" % (channel_server_str,search_date,search_date + datetime.timedelta(days=1))
print sql
count = connection.query(sql)
# print count
return count
def get_new_recharge_user_num(spe_column,table_name, row_date, channel_id=-1, server_id=-1):
"""
指定日期新增充值人数
today_new_recharge_user_num = daily_log_dat.get_set_num_with_key(today_log_lst, 'uid', game_define.EVENT_ACTION_RECHARGE_PLAYER, lambda log:log['cur_rmb']== log['add_rmb'])
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id)+" and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id)+" and "
connection = mysql_connection.get_log_mysql_connection_haima()
# 数据库表是否存在
sql_exist = "SELECT table_name FROM information_schema.tables where table_schema = 'manager_haima' and table_name = '%s';" % table_name
exist_table = connection.query(sql_exist)
if exist_table == []:
return 0
# 获取次数
sql = "SELECT count(distinct %s) FROM %s where %s action = %s and cur_rmb = add_rmb and log_time >= '%s' and log_time < '%s'" % (spe_column,table_name, channel_server_str,game_define.EVENT_ACTION_RECHARGE_PLAYER, row_date, row_date + datetime.timedelta(days=1))
# print sql
count = connection.query(sql)
# print count
return count[0].values()[0]
def get_today_num(spe_column,table_name, row_date,action=game_define.EVENT_ACTION_ROLE_LOGIN, channel_id=-1, server_id=-1):
"""
指定日期登录设备数
"""
# 获取物品购买次数
# sql = "SELECT COUNT(dev_id) FROM server_world_boss WHERE time >= '%s' and time < '%s'" % (star_time, end_time)
connection = mysql_connection.get_log_mysql_connection_haima()
# 数据库表是否存在
sql_exist = "SELECT table_name FROM information_schema.tables where table_schema = 'manager_haima' and table_name = '%s';" % table_name
exist_table = connection.query(sql_exist)
if exist_table == []:
return 0
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id)+" and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id)+" and "
# 获取次数
sql = "SELECT count(distinct %s) FROM %s where %s action = %s and log_time >= '%s' and log_time < '%s'" % (spe_column,table_name, channel_server_str ,action, row_date, row_date + datetime.timedelta(days=1))
# print sql
count = connection.query(sql)
# print count
return count[0].values()[0]
def get_recharge_uid_num(spe_column,
table_name,
row_date,
channel_id=-1,
server_id=-1):
"""
指定日期充值人数
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id) + " and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id) + " and "
connection = mysql_connection.get_log_mysql_connection_haima()
# 获取次数
sql = "SELECT count(distinct %s) FROM %s where %s action = %s and log_time >= '%s' and log_time < '%s'" % (
spe_column, table_name, channel_server_str,
game_define.EVENT_ACTION_RECHARGE_PLAYER, row_date,
row_date + datetime.timedelta(days=1))
# print sql
count = connection.query(sql)
# print count
return count[0].values()[0]
def get_new_sum(spe_column,row_date,cur_val, add_val,table_name= 'EVENT_ACTION_RECHARGE_PLAYER', action=game_define.EVENT_ACTION_RECHARGE_PLAYER, channel_id=-1, server_id=-1):
"""
指定日期充值金额
today_recharge_rmb = daily_log_dat.get_sum_int_with_key(today_log_lst, 'add_rmb', game_define.EVENT_ACTION_RECHARGE_PLAYER)
"""
channel_server_str = ""
if channel_id >= 0:
channel_server_str += "platform_id = " + str(channel_id)+" and "
if server_id >= 0:
channel_server_str += " server_id = " + str(server_id)+" and "
connection = mysql_connection.get_log_mysql_connection_haima()
# 数据库表是否存在
sql_exist = "SELECT table_name FROM information_schema.tables where table_schema = 'manager_haima' and table_name = '%s';" % table_name
exist_table = connection.query(sql_exist)
if exist_table == []:
return 0
# 获取次数
sql = "SELECT sum(%s) FROM %s where %s action = %s and %s = %s and log_time >= '%s' and log_time < '%s'" % (spe_column,table_name,channel_server_str,action,cur_val, add_val, row_date, row_date + datetime.timedelta(days=1))
# print sql
count = connection.query(sql)
if not count[0].values()[0]:
return 0
# print count
return count[0].values()[0]
def query(sql):
connection = mysql_connection.get_log_mysql_connection_haima()
return connection.query(sql)
def query(sql):
connection = mysql_connection.get_log_mysql_connection_haima()
return connection.query(sql)
