def mysql_search(start_date_date, end_date_date): """ 查询时间段内的所有数据 """ global CUR_SERVER_ID, CUR_UID_ID if CUR_UID_ID: connect = mysql_connection.get_log_mysql_connection() sql = "select * from EVENT_ACTION_RECHARGE_PLAYER_{date} where '{start_time}' <= log_time and log_time <= date_add('{end_time}', interval 1 day) and uid = '{uid_id}';"\ .format( date=str(end_date_date).replace('-', ''), start_time=start_date_date, end_time=end_date_date, uid_id=int(CUR_UID_ID), ) # print sql # 情况二 无效UID 输出全部表 else: print() connect = mysql_connection.get_log_mysql_connection() sql = "select * from EVENT_ACTION_RECHARGE_PLAYER_{date} where '{start_time}' <= log_time and log_time <= date_add('{end_time}', interval 1 day);"\ .format( date=str(end_date_date).replace('-', ''), start_time=start_date_date, end_time=end_date_date, ) # print sql return connect.query(sql)
def get_vip_distributed_uid_num(spe_column, table_name, vip_level, channel_id=-1, server_id=-1, register_start_date=None, register_end_date=None): """ 指定日期指定vip等级充值人数 """ 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() # 数据库表是否存在 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(%s) FROM %s where %s vip_level = %s " % ( spe_column, table_name, channel_server_str, vip_level) # print sql count = connection.query(sql) # print count return count[0].values()[0]
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() # 数据库表是否存在 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_vip_distributed_uid(table_name, channel_id=-1, server_id=-1, register_start_date=None, register_end_date=None): """ 指定日期每个vip等级充值玩家数量 select vip_level,count(uid) from (select uid,vip_level from EVENT_ACTION_ROLE_LOGIN_20150724 group by uid) as uid_vip group by vip_level """ 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) if register_start_date: channel_server_str += " and '%s' <= install" % register_start_date if register_end_date: channel_server_str += " and install <= '%s'" % register_end_date connection = mysql_connection.get_log_mysql_connection() # 数据库表是否存在 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 (select uid,max(vip_level) as vip_level from %s where %s group by uid) as uid_vip group by vip_level" % ( 'vip_level,count(uid)', table_name, channel_server_str) # print sql count = connection.query(sql) # print count return count
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() # 数据库表是否存在 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_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() # 数据库表是否存在 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_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() # 获取次数 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)) 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() # 数据库表是否存在 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_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() # 数据库表是否存在 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_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() # 数据库表是否存在 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) count = connection.query(sql) if not count[0].values()[0]: return 0 # print count return count[0].values()[0]
def search(start_date_date,end_date_date): ''' 查询时间段内的所有数据 ''' connect = mysql_connection.get_log_mysql_connection() sql = "select uid,add_rmb from EVENT_ACTION_RECHARGE_PLAYER where" \ " '{start_time}' <= log_time and log_time <= date_add('{end_time}', interval 1 day);"\ .format( start_time = start_date_date, end_time = end_date_date, ) return connect.query(sql)
def get_first_recharge_shop_index_uid_num(spe_column, table_name, row_date, specfy_column_name='shop_index', shop_index=-1, channel_id=-1, server_id=-1, register_start_date=None, register_end_date=None): """ 指定日期指定充值金额档充值人数 """ 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 shop_index_str = "" if shop_index > 0: shop_index_str += " and " + str(specfy_column_name) + " = " + str( shop_index) first_recharge_str = " and old_rmb = 0" connection = mysql_connection.get_log_mysql_connection() # 数据库表是否存在 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(%s) FROM %s where %s action = %s %s %s " % ( spe_column, table_name, channel_server_str, game_define.EVENT_ACTION_RECHARGE_PLAYER, shop_index_str, first_recharge_str) # print sql count = connection.query(sql) # print count return count[0].values()[0]
def get_install_recharge_sum(spe_column=None, install_date=None, table_name='EVENT_ACTION_RECHARGE_PLAYER', channel_id=-1, server_id=-1, ext=None): """ 指定安装日期充值金额总数 """ 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 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) ' # 扩展字段 ext_str = "" if ext: ext_str += " and %s " % ext connection = mysql_connection.get_log_mysql_connection() # 数据库表是否存在 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 install = '%s' %s" % ( spe_column, table_name, channel_server_str, install_date, ext_str) count = connection.query(sql) if not count[0].values()[0]: return 0 # 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() # 获取次数 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 query(sql): connection = mysql_connection.get_log_mysql_connection() return connection.query(sql)