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)