def get_global_stats_by_rank(self, session, query_filter: QueryFilterBuilder, stats_query, stds_query,
player_rank=None, redis=None, ids=None, playlist=13):
"""
Returns the global stats based on the rank of a player.
Does modify the query_filter only setting rank.
:param session: Session
:param query_filter: a query filter.
:param stats_query: A list of global stats
:param stds_query: A list of global stats for standard deviations
:param player_rank: The player that stats are associated with. Uses unranked if rank is not found
:param redis: The local cache
:return:
"""
if ids is None:
# Set the correct rank index
if player_rank is not None:
if isinstance(player_rank, dict) or isinstance(player_rank, list):
rank_index = get_rank_tier(player_rank, playlist=playlist)
else:
rank_index = player_rank
else:
rank_index = 0
stat_list = self.get_player_stat_list()
# Check to see if we have redis available (it usually is)
if redis is not None:
stat_string = redis.get('global_stats_by_rank')
# Check to see if the key exists and if so load it
if stat_string is not None:
stats_dict = json.loads(stat_string)
if playlist is not None:
playlist = str(playlist)
if playlist not in stats_dict:
raise UnsupportedPlaylist(404, 'Playlist does not exist in global stats')
stats_dict = stats_dict[playlist]
global_stats = []
global_stds = []
for stat in stat_list:
if stat.get_field_name() in stats_dict:
global_stats.append(stats_dict[stat.get_field_name()][rank_index]['mean'])
global_stds.append(stats_dict[stat.get_field_name()][rank_index]['std'])
else:
global_stats.append(1)
global_stds.append(1)
return global_stats, global_stds
if is_local_dev():
rank_index = 0
stats = self.get_global_stats(session, with_rank=False)
global_stats = [stats[stat.get_field_name()][rank_index]['mean'] for stat in stat_list]
global_stds = [stats[stat.get_field_name()][rank_index]['std'] for stat in stat_list]
return global_stats, global_stds
raise CalculatedError(500, "Global stats unavailable or have not been calculated yet.")
else:
query_filter.clean().with_replay_ids(ids)
return (query_filter.with_stat_query(stats_query).build_query(session).first(),
query_filter.with_stat_query(stds_query).build_query(session).first())
def __init__(self, player_wrapper: PlayerWrapper):
super().__init__()
self.player_wrapper = player_wrapper
# this Object needs to be pooled per a session so only one is used at a time
self.player_stats_filter = QueryFilterBuilder()
if not ignore_filtering():
self.player_stats_filter.with_relative_start_time(days_ago=30).with_safe_checking().sticky()
def have_permission_to_change_game(game_hash: str, user_id, session=None) -> bool:
entry = session.query(GameVisibility).filter(GameVisibility.player == user_id,
GameVisibility.game == game_hash).first()
if entry is None:
# Check if user has right to change visibility:
builder = QueryFilterBuilder().as_game().set_replay_id(game_hash).with_players([user_id])
if builder.build_query(session).first() is not None:
return True
else:
# Replay might actually exist, but user should not know.
raise ReplayNotFound()
else:
return True
def api_v1_get_playergames_by_rank(session=None):
if 'days' in request.args:
days = int(request.args['days'])
else:
days = 3 * 30
builder = QueryFilterBuilder().with_stat_query([PlayerGame]).with_relative_start_time(days)
QueryFilterBuilder.apply_arguments_to_query(builder, request.args)
games = builder.build_query(session).order_by(func.random())[:1000]
columns = [c.name for c in games[0].__table__.columns]
data = {
'data': [[getattr(g, c.name) for c in g.__table__.columns] for g in games],
'columns': columns
}
return jsonify(data)
def create_with_filters(page: int,
limit: int,
session=None,
**kwargs) -> 'MatchHistory':
tags = JsonTag.get_tags_from_query_params(**kwargs, session=session)
builder = QueryFilterBuilder().as_game()
if len(tags) > 0:
builder.with_tags([tag.id for tag in tags])
QueryFilterBuilder.apply_arguments_to_query(builder, kwargs)
query = builder.build_query(session)
if 'min_length' in kwargs:
query = query.filter(Game.length > kwargs['min_length'])
if 'max_length' in kwargs:
query = query.filter(Game.length < kwargs['max_length'])
if 'map' in kwargs:
query = query.filter(Game.map == kwargs['map'])
count = query.count()
games = query.order_by(desc(Game.match_date))[page * limit:(page + 1) *
limit]
matches = MatchHistory(
count, [Replay.create_from_game(game) for game in games])
return matches
def _get_counts(self, session, player_filter=None, replay_ids=None):
stats = QueryFilterBuilder().with_stat_query((PlayerGame,))
if player_filter is not None:
stats.with_players(player_filter)
if replay_ids is not None:
stats.with_replay_ids(replay_ids)
return stats.build_query(session).filter(PlayerGame.time_in_game > 0).count()
def create_with_filters(page: int, limit: int, session=None, **kwargs) -> 'MatchHistory':
# TODO: move this somewhere else and make it reusable
if limit > 100:
limit = 100
builder = QueryFilterBuilder().as_game().with_stat_query([Game])
QueryFilterBuilder.apply_arguments_to_query(builder, kwargs)
query = builder.build_query(session)
if 'min_length' in kwargs:
query = query.filter(Game.length > kwargs['min_length'])
if 'max_length' in kwargs:
query = query.filter(Game.length < kwargs['max_length'])
if 'map' in kwargs:
query = query.filter(Game.map == kwargs['map'])
count = query.count()
games = query.order_by(desc(Game.match_date))[page * limit: (page + 1) * limit]
matches = MatchHistory(count, [Replay.create_from_game(game) for game in games])
return matches
def _create_group_stats_from_query(self, session, query, player_filter=None, replay_ids=None):
stats = QueryFilterBuilder().with_stat_query(query)
if player_filter is not None:
stats.with_players(player_filter)
if replay_ids is not None:
stats.with_replay_ids(replay_ids)
stats = stats.build_query(session).filter(PlayerGame.time_in_game > 0).first()
stats = {n.get_query_key(): round(float(s), 2) for n, s in zip(self.player_stats.stat_list, stats) if s is not None}
return stats
def get_global_stats_by_rank(self, session, query_filter: QueryFilterBuilder, stats_query, stds_query,
player_rank=None, redis=None):
"""
Returns the global stats based on the rank of a player.
Does modify the query_filter only setting rank.
:param session: Session
:param query_filter: a query filter.
:param stats_query: A list of global stats
:param stds_query: A list of global stats for standard deviations
:param player_rank: The player that stats are associated with. Uses unranked if rank is not found
:param redis: The local cache
:return:
"""
rank_index = get_rank_number(player_rank)
if redis is not None:
stat_string = redis.get('global_stats')
if stat_string is not None:
stats_dict = json.loads(stat_string)
global_stats = [stats_dict[s.field_name][rank_index]['mean'] for s in self.field_names]
global_stds = [stats_dict[s.field_name][rank_index]['std'] for s in self.field_names]
return global_stats, global_stds
if player_rank is not None and not get_local_dev():
logger.debug('Filtering by rank')
query_filter.clean().with_rank(rank_index)
else:
query_filter.clean()
return (query_filter.with_stat_query(stats_query).build_query(session).first(),
query_filter.with_stat_query(stds_query).build_query(session).first())
class PlayerStatWrapper(GlobalStatWrapper):
def __init__(self, player_wrapper: PlayerWrapper):
super().__init__()
self.player_wrapper = player_wrapper
# this Object needs to be pooled per a session so only one is used at a time
self.player_stats_filter = QueryFilterBuilder()
self.player_stats_filter.with_relative_start_time(
days_ago=30 * 6).with_team_size(3).with_safe_checking().sticky()
def get_wrapped_stats(self, stats):
zipped_stats = dict()
for i in range(len(self.field_names)):
zipped_stats[self.field_names[i].field_name] = stats[i]
return zipped_stats
def get_stats(self,
session,
id_,
stats_query,
std_query,
rank=None,
redis=None):
global_stats, global_stds = self.get_global_stats_by_rank(
session,
self.player_stats_filter,
stats_query,
std_query,
player_rank=rank,
redis=redis)
self.player_stats_filter.clean().with_stat_query(
stats_query).with_players([id_])
stats = list(self.player_stats_filter.build_query(session).first())
return self.compare_to_global(stats, global_stats, global_stds)
def get_averaged_stats(self, session, id_, rank=None, redis=None):
stats_query = self.stats_query
std_query = self.std_query
total_games = self.player_wrapper.get_total_games(session, id_)
if total_games > 0:
stats = self.get_stats(session,
id_,
stats_query,
std_query,
rank=rank,
redis=redis)
else:
stats = [0.0] * len(stats_query)
return self.get_wrapped_stats(stats)
@staticmethod
def get_stat_spider_charts():
titles = [ # 'Basic',
'Aggressiveness', 'Chemistry', 'Skill', 'Tendencies', 'Luck'
]
groups = [ # ['score', 'goals', 'assists', 'saves', 'turnovers'], # basic
[
'shots', 'possession', 'hits', 'shots/hit', 'boost usage',
'speed'
], # agressive
['boost wasted', 'assists', 'passes/hit', 'passes',
'assists/hit'], # chemistry
[
'turnovers', 'useful/hits', 'aerials', 'won turnovers',
'avg hit dist'
], # skill
['att 1/3', 'att 1/2', 'def 1/2', 'def 1/3', '< ball', '> ball']
] # , # tendencies
# ['luck1', 'luck2', 'luck3', 'luck4']] # luck
return [{
'title': title,
'group': group
} for title, group in zip(titles, groups)]
def _create_stats(self, session, query_filter=None, ids=None):
if query_filter is not None and ids is not None:
query_filter = and_(query_filter, PlayerGame.game.in_(ids))
elif ids is not None:
query_filter = PlayerGame.game.in_(ids)
average = session.query(*self.stats_query)
std_devs = session.query(*self.std_query)
if query_filter is not None:
average = average.filter(query_filter)
std_devs = std_devs.filter(query_filter)
average = average.first()
std_devs = std_devs.first()
average = {
n.field_name: round(float(s), 2)
for n, s in zip(self.field_names, average) if s is not None
}
std_devs = {
n.field_name: round(float(s), 2)
for n, s in zip(self.field_names, std_devs) if s is not None
}
return {'average': average, 'std_dev': std_devs}
def get_group_stats(self, session, ids):
return_obj = {}
# Players
player_tuples = session.query(PlayerGame.player,
func.count(PlayerGame.player)).filter(
PlayerGame.game.in_(ids)).group_by(
PlayerGame.player).all()
return_obj['playerStats'] = {}
ensemble = []
for player_tuple in player_tuples:
player, count = player_tuple
if count > 1:
player_stats = self._create_stats(session,
PlayerGame.player == player,
ids=ids)
return_obj['playerStats'][player] = player_stats
else:
ensemble.append(player)
if len(ensemble) > 0:
ensemble_stats = self._create_stats(
session, PlayerGame.player.in_(ensemble), ids=ids)
return_obj['ensembleStats'] = ensemble_stats
# STATS
# Global
global_stats = self._create_stats(session, ids=ids)
return_obj['globalStats'] = global_stats
return return_obj
def test_query_builder(self):
query = QueryFilterBuilder().with_relative_start_time(days_ago=10).sticky().clean()
print(query)
from backend.database.wrapper.query_filter_builder import QueryFilterBuilder
if __name__ == "__main__":
query = QueryFilterBuilder().with_relative_start_time(
days_ago=10).sticky().clean()
print(query)
class PlayerStatWrapper(GlobalStatWrapper):
def __init__(self, player_wrapper: PlayerWrapper):
super().__init__()
self.player_wrapper = player_wrapper
# this Object needs to be pooled per a session so only one is used at a time
self.player_stats_filter = QueryFilterBuilder()
if not ignore_filtering():
self.player_stats_filter.with_relative_start_time(
days_ago=30 * 6).with_safe_checking().sticky()
def get_stats(self,
session,
id_,
stats_query,
std_query,
rank=None,
redis=None,
raw=False,
replay_ids=None,
playlist=13,
win: bool = None):
player_stats_filter = self.player_stats_filter.clean().clone()
player_stats_filter.clean().with_stat_query(stats_query).with_players(
[id_])
if replay_ids is not None:
player_stats_filter.with_replay_ids(replay_ids)
else:
player_stats_filter.with_playlists([playlist])
query = player_stats_filter.build_query(session).filter(
PlayerGame.time_in_game > 0).filter(
PlayerGame.game != '').group_by(PlayerGame.player)
if win is not None:
query = query.filter(PlayerGame.win == win)
stats = list(query.one())
stats = [0 if s is None else s for s in stats]
if raw:
return [self.float_maybe(s) for s in stats]
else:
global_stats, global_stds = self.get_global_stats_by_rank(
session,
player_stats_filter,
stats_query,
std_query,
player_rank=rank,
redis=redis,
ids=replay_ids,
playlist=playlist)
return self.compare_to_global(stats, global_stats, global_stds)
def get_averaged_stats(self,
session,
id_: str,
rank: int = None,
redis=None,
raw: bool = False,
replay_ids: list = None,
playlist: int = 13,
win: bool = None):
"""
Gets the averaged stats for the given ID, in either raw or standard deviation form.
:param session: DBSession
:param id_: Player ID to retrieve
:param rank: rank in integer form (0-19) (optional)
:param redis: optional redis instance, for caching/retrieving
:param raw: return the values in raw form instead of compared to global
:param replay_ids: replay ids to get stats from
:param playlist: what playlist to filter by (only when replay_ids is None)
:param win: filter by wins/losses (win = True)
:return: stats
"""
stats_query = self.get_player_stat_query()
std_query = self.get_player_stat_std_query()
total_games = self.player_wrapper.get_total_games(
session, id_, replay_ids=replay_ids)
if total_games > 0:
stats = self.get_stats(session,
id_,
stats_query,
std_query,
rank=rank,
redis=redis,
raw=raw,
replay_ids=replay_ids,
playlist=playlist,
win=win)
else:
stats = [0.0] * len(stats_query)
return self.get_wrapped_stats(stats, self.player_stats)
def get_progression_stats(self,
session,
id_,
time_unit: 'TimeUnit' = TimeUnit.MONTH,
start_date: datetime.datetime = None,
end_date: datetime.datetime = None,
playlist: int = 13):
if time_unit == TimeUnit.MONTH:
date = func.to_char(Game.match_date, 'YY-MM')
time_format = "%y-%m"
elif time_unit == TimeUnit.DAY:
date = func.to_char(Game.match_date, 'YY-MM-DD')
time_format = "%y-%m-%d"
elif time_unit == TimeUnit.YEAR:
date = func.to_char(Game.match_date, 'YY')
time_format = "%y"
elif time_unit == TimeUnit.QUARTER:
date = func.concat(
func.to_char(Game.match_date, 'YY'), '-',
func.floor(
cast(func.extract('quarter', Game.match_date),
sqlalchemy.Numeric)))
time_format = '%y-%m'
else:
# Mainly to help the linter know that time_unit is assigned.
logger.error(
f'Unknown time unit: {time_unit}. Falling back onto month.')
date = func.to_char(Game.match_date, 'YY-MM')
time_format = "%y-%m"
date = date.label('date')
mean_query = session.query(date, func.count(
Game.hash), *self.get_player_stat_query()).join(PlayerGame).filter(
PlayerGame.time_in_game > 0).filter(
PlayerGame.player == id_).group_by('date').order_by('date')
std_query = session.query(
date, func.count(Game.hash),
*self.get_player_stat_std_query()).join(PlayerGame).filter(
PlayerGame.time_in_game > 0).filter(
PlayerGame.player == id_).group_by('date').order_by('date')
if start_date is not None:
mean_query = mean_query.filter(Game.match_date > start_date)
std_query = std_query.filter(Game.match_date > start_date)
if end_date is not None:
mean_query = mean_query.filter(Game.match_date < end_date)
std_query = std_query.filter(Game.match_date < end_date)
if playlist is not None:
mean_query = mean_query.filter(Game.playlist == playlist)
std_query = std_query.filter(Game.playlist == playlist)
mean_query = mean_query.all()
std_query = std_query.all()
mean_query = [list(q) for q in mean_query]
std_query = [list(q) for q in std_query]
results = []
for q, s in zip(mean_query, std_query):
result = {
'name':
datetime.datetime.strptime(q[0], time_format),
'average':
self.get_wrapped_stats([self.float_maybe(qn) for qn in q[2:]],
self.player_stats),
'std_dev':
self.get_wrapped_stats([self.float_maybe(qn) for qn in s[2:]],
self.player_stats),
'count':
q[1]
}
if time_unit == 'quarter':
date = result['name']
result['name'] = datetime.datetime(date.year,
(date.month - 1) * 3 + 1, 1)
result['name'] = result['name'].isoformat()
results.append(result)
return results
@staticmethod
def get_stat_spider_charts():
titles = [ # 'Basic',
'Aggressiveness', 'Chemistry', 'Skill', 'Tendencies', 'Luck'
]
groups = [ # ['score', 'goals', 'assists', 'saves', 'turnovers'], # basic
[
'shots', 'possession_time', 'total_hits', 'shots/hit',
'boost_usage', 'average_speed'
], # agressive
[
'total_boost_efficiency', 'assists', 'passes/hit',
'total_passes', 'assists/hit'
], # chemistry
[
'turnover_efficiency', 'useful/hits', 'total_aerials',
'won_turnovers', 'average_hit_distance'
], # skill
[
'time_in_attacking_third', 'time_in_attacking_half',
'time_in_defending_half', 'time_in_defending_third',
'time_behind_ball', 'time_in_front_ball'
]
] # , # tendencies
# ['luck1', 'luck2', 'luck3', 'luck4']] # luck
return [{
'title': title,
'group': group
} for title, group in zip(titles, groups)]
def _create_group_stats_from_query(self,
session,
query,
player_filter=None,
replay_ids=None):
stats = QueryFilterBuilder().with_stat_query(query)
if player_filter is not None:
stats.with_players(player_filter)
if replay_ids is not None:
stats.with_replay_ids(replay_ids)
stats = stats.build_query(session).filter(
PlayerGame.time_in_game > 0).first()
stats = {
n.get_query_key(): round(float(s), 2)
for n, s in zip(self.player_stats.stat_list, stats)
if s is not None
}
return stats
def _get_counts(self, session, player_filter=None, replay_ids=None):
stats = QueryFilterBuilder().with_stat_query((PlayerGame, ))
if player_filter is not None:
stats.with_players(player_filter)
if replay_ids is not None:
stats.with_replay_ids(replay_ids)
return stats.build_query(session).filter(
PlayerGame.time_in_game > 0).count()
def _split_by_category(self, stats):
stats_dict = {}
for chart_metadata in player_group_stats_metadata:
if chart_metadata.stat_name not in stats:
continue
stats_dict[chart_metadata.stat_name] = {
"value": stats[chart_metadata.stat_name],
"subcategory": chart_metadata.subcategory
}
for stat in set(stats.keys()) - set(
[stat.stat_name for stat in player_group_stats_metadata]):
stats_dict[stat] = {"value": stats[stat], "subcategory": "Misc"}
return stats_dict
def _create_group_stats(self,
session,
player_filter=None,
replay_ids=None):
average = self._create_group_stats_from_query(
session, self.get_player_stat_query(), player_filter, replay_ids)
individual = self._create_group_stats_from_query(
session, self.player_stats.individual_query, player_filter,
replay_ids)
total = {}
per_game = {}
per_norm_game = {}
per_minute = {}
factor_per_game = self._get_counts(session, player_filter, replay_ids)
factor_per_minute = individual[
'time_in_game'] / 60.0 if 'time_in_game' in individual else None
factor_per_norm_game = factor_per_minute / 5 if factor_per_minute is not None else None
for stat in self.get_player_stat_list():
stat_query_key = stat.get_query_key()
if stat_query_key not in individual or stat_query_key not in average:
continue
if stat_query_key in self.replay_group_stats.grouped_stat_total:
if stat.is_percent or stat.is_averaged:
total[stat_query_key] = average[stat_query_key]
else:
total[stat_query_key] = individual[stat_query_key]
if stat_query_key in self.replay_group_stats.grouped_stat_per_game and factor_per_game is not None:
per_game[stat_query_key] = individual[
stat_query_key] / factor_per_game
if stat_query_key in self.replay_group_stats.grouped_stat_per_minute and factor_per_minute is not None:
per_minute[stat_query_key] = individual[
stat_query_key] / factor_per_minute
if factor_per_norm_game is not None:
if stat_query_key in self.replay_group_stats.grouped_stat_total \
and (stat.is_percent or stat.is_averaged):
per_norm_game[stat_query_key +
" (Total)"] = average[stat_query_key]
else:
per_norm_game[stat_query_key] = individual[
stat_query_key] / factor_per_norm_game
return {
'stats': {
'(Total)': total,
'(per Game)': per_game,
'(per Norm Game)': per_norm_game,
'(per Minute)': per_minute
}
}
@with_session
def get_group_team_stats(self, replay_ids, session=None):
query = session.query(
PlayerGame.game,
func.array_agg(
aggregate_order_by(
PlayerGame.player,
PlayerGame.player)).label("team")).filter(
PlayerGame.game.in_(replay_ids)).group_by(
PlayerGame.game).group_by(
PlayerGame.is_orange).subquery()
teams = session.query(query.c.team, func.array_agg(
query.c.game)).group_by(query.c.team).all()
return {
"teamStats": [{
"team":
team[0],
"games":
team[1],
"names": [
name
for (name, ) in session.query(func.min(
PlayerGame.name)).filter(PlayerGame.game.in_(team[1])).
filter(PlayerGame.player.in_(team[0])).order_by(
PlayerGame.player).group_by(PlayerGame.player).all()
],
**self._create_group_stats(session,
player_filter=team[0],
replay_ids=team[1])
} for team in teams]
}
@with_session
def get_group_stats(self, replay_ids, session=None):
return_obj = {}
# Players
player_tuples: List[Tuple[str, str, int]] = session.query(
PlayerGame.player, func.min(PlayerGame.name),
func.count(PlayerGame.player)).filter(
PlayerGame.game.in_(replay_ids)).group_by(
PlayerGame.player).all()
return_obj['playerStats'] = []
# ensemble are the players that do not have enough replays to make an individual analysis for them
ensemble = []
for player_tuple in player_tuples:
player, name, count = player_tuple
if count > 1:
player_stats = self._create_group_stats(session,
player_filter=player,
replay_ids=replay_ids)
player_stats['name'] = name
player_stats['player'] = player
return_obj['playerStats'].append(player_stats)
else:
ensemble.append(player)
if len(ensemble) > 0:
# create stats that only includes the ensemble
ensemble_stats = self._create_group_stats(session,
player_filter=ensemble,
replay_ids=replay_ids)
return_obj['ensembleStats'] = ensemble_stats
# STATS
# Global
# create stats that include all the players in the game
# global_stats = self._create_group_stats(session, ids=replay_ids)
# return_obj['globalStats'] = global_stats
num_replays = len(replay_ids)
if num_replays > 1 and all(
[player_tuple[2] == num_replays
for player_tuple in player_tuples]):
assert 'ensembleStats' not in return_obj
# all players have played every game
is_orange = {player_tuple[0]: [] for player_tuple in player_tuples}
for replay_id in replay_ids:
game: Game = session.query(Game).filter(
Game.hash == replay_id).first()
if game is None:
raise ReplayNotFound()
playergames: List = session.query(
func.max(PlayerGame.player),
func.bool_and(PlayerGame.is_orange)).filter(
PlayerGame.game == replay_id).group_by(
PlayerGame.player).all()
for playergame in playergames:
assert len(playergame) == 2
player, is_orange_game = playergame
assert player in is_orange
is_orange[player].append(is_orange_game)
# if the player is always in the same team
if all([
len(set(player_is_orange)) == 1
for player_is_orange in is_orange.values()
]):
for i in range(len(return_obj['playerStats'])):
player = return_obj['playerStats'][i]['player']
return_obj['playerStats'][i]['is_orange'] = is_orange[
player][0]
return_obj['playerStats'] = sorted(
sorted(return_obj['playerStats'],
key=lambda x: x['name'].lower()),
key=lambda x: x['is_orange'])
return return_obj
class PlayerStatWrapper(GlobalStatWrapper):
def __init__(self, player_wrapper: PlayerWrapper):
super().__init__()
self.player_wrapper = player_wrapper
# this Object needs to be pooled per a session so only one is used at a time
self.player_stats_filter = QueryFilterBuilder()
if not ignore_filtering():
self.player_stats_filter.with_relative_start_time(
days_ago=30 * 6).with_safe_checking().sticky()
def get_stats(self,
session,
id_,
stats_query,
std_query,
rank=None,
redis=None,
raw=False,
replay_ids=None,
playlist=13,
win: bool = None):
player_stats_filter = self.player_stats_filter.clean().clone()
player_stats_filter.clean().with_stat_query(stats_query).with_players(
[id_])
if replay_ids is not None:
player_stats_filter.with_replay_ids(replay_ids)
else:
player_stats_filter.with_playlists([playlist])
query = player_stats_filter.build_query(session).filter(
PlayerGame.time_in_game > 0).filter(
PlayerGame.game != '').group_by(PlayerGame.player)
if win is not None:
query = query.filter(PlayerGame.win == win)
if query.count() < 1:
raise CalculatedError(404, 'User does not have enough replays.')
stats = list(query.first())
stats = [0 if s is None else s for s in stats]
if raw:
return [self.float_maybe(s) for s in stats]
else:
global_stats, global_stds = self.get_global_stats_by_rank(
session,
player_stats_filter,
stats_query,
std_query,
player_rank=rank,
redis=redis,
ids=replay_ids,
playlist=playlist)
return self.compare_to_global(stats, global_stats, global_stds)
def get_averaged_stats(self,
session,
id_: str,
rank: int = None,
redis=None,
raw: bool = False,
replay_ids: list = None,
playlist: int = 13,
win: bool = None):
"""
Gets the averaged stats for the given ID, in either raw or standard deviation form.
:param session: DBSession
:param id_: Player ID to retrieve
:param rank: rank in integer form (0-19) (optional)
:param redis: optional redis instance, for caching/retrieving
:param raw: return the values in raw form instead of compared to global
:param replay_ids: replay ids to get stats from
:param playlist: what playlist to filter by (only when replay_ids is None)
:param win: filter by wins/losses (win = True)
:return: stats
"""
stats_query = self.get_player_stat_query()
std_query = self.get_player_stat_std_query()
total_games = self.player_wrapper.get_total_games(
session, id_, replay_ids=replay_ids)
if total_games > 0:
stats = self.get_stats(session,
id_,
stats_query,
std_query,
rank=rank,
redis=redis,
raw=raw,
replay_ids=replay_ids,
playlist=playlist,
win=win)
else:
stats = [0.0] * len(stats_query)
return self.get_wrapped_stats(stats, self.player_stats)
def get_progression_stats(self,
session,
id_,
time_unit: 'TimeUnit' = TimeUnit.MONTH,
start_date: datetime.datetime = None,
end_date: datetime.datetime = None,
playlist: int = 13):
if time_unit == TimeUnit.MONTH:
date = func.to_char(Game.match_date, 'YY-MM')
time_format = "%y-%m"
elif time_unit == TimeUnit.DAY:
date = func.to_char(Game.match_date, 'YY-MM-DD')
time_format = "%y-%m-%d"
elif time_unit == TimeUnit.YEAR:
date = func.to_char(Game.match_date, 'YY')
time_format = "%y"
elif time_unit == TimeUnit.QUARTER:
date = func.concat(
func.to_char(Game.match_date, 'YY'), '-',
func.floor(
cast(func.extract('quarter', Game.match_date),
sqlalchemy.Numeric)))
time_format = '%y-%m'
else:
# Mainly to help the linter know that time_unit is assigned.
logger.error(
f'Unknown time unit: {time_unit}. Falling back onto month.')
date = func.to_char(Game.match_date, 'YY-MM')
time_format = "%y-%m"
date = date.label('date')
mean_query = session.query(date, func.count(
Game.hash), *self.get_player_stat_query()).join(PlayerGame).filter(
PlayerGame.time_in_game > 0).filter(
PlayerGame.player == id_).group_by('date').order_by('date')
std_query = session.query(
date, func.count(Game.hash),
*self.get_player_stat_std_query()).join(PlayerGame).filter(
PlayerGame.time_in_game > 0).filter(
PlayerGame.player == id_).group_by('date').order_by('date')
if start_date is not None:
mean_query = mean_query.filter(Game.match_date > start_date)
std_query = std_query.filter(Game.match_date > start_date)
if end_date is not None:
mean_query = mean_query.filter(Game.match_date < end_date)
std_query = std_query.filter(Game.match_date < end_date)
if playlist is not None:
mean_query = mean_query.filter(Game.playlist == playlist)
std_query = std_query.filter(Game.playlist == playlist)
mean_query = mean_query.all()
std_query = std_query.all()
mean_query = [list(q) for q in mean_query]
std_query = [list(q) for q in std_query]
results = []
for q, s in zip(mean_query, std_query):
result = {
'name':
datetime.datetime.strptime(q[0], time_format),
'average':
self.get_wrapped_stats([self.float_maybe(qn) for qn in q[2:]],
self.player_stats),
'std_dev':
self.get_wrapped_stats([self.float_maybe(qn) for qn in s[2:]],
self.player_stats),
'count':
q[1]
}
if time_unit == 'quarter':
date = result['name']
result['name'] = datetime.datetime(date.year,
(date.month - 1) * 3 + 1, 1)
result['name'] = result['name'].isoformat()
results.append(result)
return results
@staticmethod
def get_stat_spider_charts():
titles = [ # 'Basic',
'Aggressiveness', 'Chemistry', 'Skill', 'Tendencies', 'Luck'
]
groups = [ # ['score', 'goals', 'assists', 'saves', 'turnovers'], # basic
[
'shots', 'possession_time', 'total_hits', 'shots/hit',
'boost_usage', 'average_speed'
], # agressive
[
'total boost efficiency', 'assists', 'passes/hit',
'total_passes', 'assists/hit'
], # chemistry
[
'turnover efficiency', 'useful/hits', 'total_aerials',
'won_turnovers', 'average_hit_distance'
], # skill
[
'time_in_attacking_third', 'time_in_attacking_half',
'time_in_defending_half', 'time_in_defending_third',
'time_behind_ball', 'time_in_front_ball'
]
] # , # tendencies
# ['luck1', 'luck2', 'luck3', 'luck4']] # luck
return [{
'title': title,
'group': group
} for title, group in zip(titles, groups)]
def _create_stats(self, session, player_filter=None, replay_ids=None):
average = QueryFilterBuilder().with_stat_query(
self.get_player_stat_query())
std_devs = QueryFilterBuilder().with_stat_query(
self.get_player_stat_std_query())
if player_filter is not None:
average.with_players(player_filter)
std_devs.with_players(player_filter)
if replay_ids is not None:
average.with_replay_ids(replay_ids)
std_devs.with_replay_ids(replay_ids)
average = average.build_query(session).filter(
PlayerGame.time_in_game > 0).first()
std_devs = std_devs.build_query(session).filter(
PlayerGame.time_in_game > 0).first()
average = {
n.get_field_name(): round(float(s), 2)
for n, s in zip(self.player_stats.stat_list, average)
if s is not None
}
std_devs = {
n.get_field_name(): round(float(s), 2)
for n, s in zip(self.player_stats.stat_list, std_devs)
if s is not None
}
return {'average': average, 'std_dev': std_devs}
@with_session
def get_group_stats(self, replay_ids, session=None):
return_obj = {}
# Players
player_tuples: List[Tuple[str, str, int]] = session.query(
PlayerGame.player, func.min(PlayerGame.name),
func.count(PlayerGame.player)).filter(
PlayerGame.game.in_(replay_ids)).group_by(
PlayerGame.player).all()
return_obj['playerStats'] = {}
# ensemble are the players that do not have enough replays to make an individual analysis for them
ensemble = []
for player_tuple in player_tuples:
player, name, count = player_tuple
if count > 1:
player_stats = self._create_stats(session,
player_filter=player,
replay_ids=replay_ids)
player_stats['name'] = name
return_obj['playerStats'][player] = player_stats
else:
ensemble.append(player)
if len(ensemble) > 0:
# create stats that only includes the ensemble
ensemble_stats = self._create_stats(session,
player_filter=ensemble,
replay_ids=replay_ids)
return_obj['ensembleStats'] = ensemble_stats
# STATS
# Global
# create stats that include all the players in the game
# global_stats = self._create_stats(session, ids=replay_ids)
# return_obj['globalStats'] = global_stats
return return_obj
def _create_stats(self, session, player_filter=None, replay_ids=None):
average = QueryFilterBuilder().with_stat_query(
self.get_player_stat_query())
std_devs = QueryFilterBuilder().with_stat_query(
self.get_player_stat_std_query())
if player_filter is not None:
average.with_players(player_filter)
std_devs.with_players(player_filter)
if replay_ids is not None:
average.with_replay_ids(replay_ids)
std_devs.with_replay_ids(replay_ids)
average = average.build_query(session).filter(
PlayerGame.time_in_game > 0).first()
std_devs = std_devs.build_query(session).filter(
PlayerGame.time_in_game > 0).first()
average = {
n.get_field_name(): round(float(s), 2)
for n, s in zip(self.player_stats.stat_list, average)
if s is not None
}
std_devs = {
n.get_field_name(): round(float(s), 2)
for n, s in zip(self.player_stats.stat_list, std_devs)
if s is not None
}
return {'average': average, 'std_dev': std_devs}
def __init__(self):
super().__init__()
# this Object needs to be pooled per a session so only one is used at a time
self.base_query = QueryFilterBuilder().with_relative_start_time(
days_ago=self.get_timeframe()).with_safe_checking().sticky()
class GlobalStatWrapper(SharedStatsWrapper):
"""
A database wrapper for global stats. Acts additionally on global stats in addition to player stats
"""
def __init__(self):
super().__init__()
# this Object needs to be pooled per a session so only one is used at a time
self.base_query = QueryFilterBuilder().with_relative_start_time(
days_ago=self.get_timeframe()).with_safe_checking().sticky()
def get_global_stats(self, sess, with_rank=True):
"""
:return: A list of stats by rank for every field.
"""
if with_rank:
ranks = list(range(20))
else:
ranks = [0]
playlist_results = {}
for playlist in GLOBAL_STATS_PLAYLISTS:
results = {}
for column, q in zip(self.get_player_stat_list(),
self.get_player_stat_query()):
column_results = []
# set the column result
self.base_query.clean().with_stat_query(
[PlayerGame.player, q.label('avg')])
for rank in ranks:
result = self._get_global_stats_result(self.base_query,
playlist,
rank,
sess,
with_rank=with_rank)
column_results.append({
'mean': self.float_maybe(result[0]),
'std': self.float_maybe(result[1])
})
results[column.get_field_name()] = column_results
playlist_results[playlist] = results
return playlist_results
def _get_global_stats_result(self,
query,
playlist,
rank,
session,
with_rank=True):
if with_rank:
query = query.with_rank(rank)
if not ignore_filtering():
query.with_playlists([playlist])
query = query.build_query(session)
query = query.group_by(PlayerGame.player)
if ignore_filtering():
query = query.subquery()
else:
query = query.filter(PlayerGame.game != "").filter(
PlayerGame.time_in_game > 0).having(
func.count(PlayerGame.player) > 5).subquery()
return session.query(func.avg(query.c.avg),
func.stddev_samp(query.c.avg)).first()
def get_global_stats_by_rank(self,
session,
query_filter: QueryFilterBuilder,
stats_query,
stds_query,
player_rank=None,
redis=None,
ids=None,
playlist=13):
"""
Returns the global stats based on the rank of a player.
Does modify the query_filter only setting rank.
:param session: Session
:param query_filter: a query filter.
:param stats_query: A list of global stats
:param stds_query: A list of global stats for standard deviations
:param player_rank: The player that stats are associated with. Uses unranked if rank is not found
:param redis: The local cache
:return:
"""
if ids is None:
# Set the correct rank index
if player_rank is not None:
if isinstance(player_rank, dict) or isinstance(
player_rank, list):
rank_index = get_rank_tier(player_rank, playlist=playlist)
else:
rank_index = player_rank
else:
rank_index = 0
stat_list = self.get_player_stat_list()
# Check to see if we have redis available (it usually is)
if redis is not None:
stat_string = redis.get('global_stats')
# Check to see if the key exists and if so load it
if stat_string is not None:
stats_dict = json.loads(stat_string)
if playlist is not None:
playlist = str(playlist)
if playlist not in stats_dict:
raise CalculatedError(
404, 'Playlist does not exist in global stats')
stats_dict = stats_dict[playlist]
global_stats = []
global_stds = []
for stat in stat_list:
if stat.get_field_name() in stats_dict:
global_stats.append(stats_dict[
stat.get_field_name()][rank_index]['mean'])
global_stds.append(stats_dict[
stat.get_field_name()][rank_index]['std'])
else:
global_stats.append(1)
global_stds.append(1)
return global_stats, global_stds
if is_local_dev():
rank_index = 0
stats = self.get_global_stats(session, with_rank=False)
global_stats = [
stats[stat.get_field_name()][rank_index]['mean']
for stat in stat_list
]
global_stds = [
stats[stat.get_field_name()][rank_index]['std']
for stat in stat_list
]
return global_stats, global_stds
raise CalculatedError(
500,
"Global stats unavailable or have not been calculated yet.")
else:
query_filter.clean().with_replay_ids(ids)
return (query_filter.with_stat_query(stats_query).build_query(
session).first(), query_filter.with_stat_query(
stds_query).build_query(session).first())
@staticmethod
def get_timeframe():
"""Returns the number of days we accept old stats"""
try:
from flask import current_app
return current_app.config['STAT_DAY_LIMIT']
except:
return 30 * 6
class GlobalStatWrapper(SharedStatsWrapper):
"""
A database wrapper for global stats. Acts additionally on global stats in addition to player stats
"""
def __init__(self):
super().__init__()
# this Object needs to be pooled per a session so only one is used at a time
self.base_query = QueryFilterBuilder().with_relative_start_time(days_ago=self.get_timeframe()).with_team_size(
3).sticky()
def get_global_stats(self, sess):
"""
:return: A list of stats by rank for every field.
"""
results = {}
ranks = list(range(20))
def float_maybe(f):
if f is None:
return None
else:
return float(f)
for column, q in zip(self.field_names, self.stats_query):
column_results = []
# set the column result
self.base_query.clean().with_stat_query([PlayerGame.player, q.label('avg')])
for rank in ranks:
query = self.base_query.with_rank(rank).build_query(sess)
query = query.group_by(PlayerGame.player).having(func.count(PlayerGame.player) > 5).subquery()
result = sess.query(func.avg(query.c.avg), func.stddev_samp(query.c.avg)).first()
column_results.append({'mean': float_maybe(result[0]), 'std': float_maybe(result[1])})
results[column.field_name] = column_results
return results
def get_global_stats_by_rank(self, session, query_filter: QueryFilterBuilder, stats_query, stds_query,
player_rank=None, redis=None):
"""
Returns the global stats based on the rank of a player.
Does modify the query_filter only setting rank.
:param session: Session
:param query_filter: a query filter.
:param stats_query: A list of global stats
:param stds_query: A list of global stats for standard deviations
:param player_rank: The player that stats are associated with. Uses unranked if rank is not found
:param redis: The local cache
:return:
"""
rank_index = get_rank_number(player_rank)
if redis is not None:
stat_string = redis.get('global_stats')
if stat_string is not None:
stats_dict = json.loads(stat_string)
global_stats = [stats_dict[s.field_name][rank_index]['mean'] for s in self.field_names]
global_stds = [stats_dict[s.field_name][rank_index]['std'] for s in self.field_names]
return global_stats, global_stds
if player_rank is not None and not get_local_dev():
logger.debug('Filtering by rank')
query_filter.clean().with_rank(rank_index)
else:
query_filter.clean()
return (query_filter.with_stat_query(stats_query).build_query(session).first(),
query_filter.with_stat_query(stds_query).build_query(session).first())
@staticmethod
def get_timeframe():
"""Returns the number of days we accept old stats"""
try:
from flask import current_app
return current_app.config['STAT_DAY_LIMIT']
except:
return 30 * 6
class PlayerStatWrapper(GlobalStatWrapper):
def __init__(self, player_wrapper: PlayerWrapper):
super().__init__()
self.player_wrapper = player_wrapper
# this Object needs to be pooled per a session so only one is used at a time
self.player_stats_filter = QueryFilterBuilder()
if not ignore_filtering():
self.player_stats_filter.with_relative_start_time(
days_ago=30 *
6).with_team_size(3).with_safe_checking().sticky()
def get_wrapped_stats(self, stats):
zipped_stats = dict()
for i in range(len(self.stat_list)):
zipped_stats[self.stat_list[i].get_field_name()] = stats[i]
return zipped_stats
def get_stats(self,
session,
id_,
stats_query,
std_query,
rank=None,
redis=None,
raw=False,
replay_ids=None):
player_stats_filter = self.player_stats_filter.clean().clone()
global_stats, global_stds = self.get_global_stats_by_rank(
session,
player_stats_filter,
stats_query,
std_query,
player_rank=rank,
redis=redis,
ids=replay_ids)
player_stats_filter.clean().with_stat_query(stats_query).with_players(
[id_])
if replay_ids is not None:
player_stats_filter.with_replay_ids(replay_ids)
query = player_stats_filter.build_query(session).filter(
PlayerGame.time_in_game > 0).filter(
PlayerGame.game != '').group_by(PlayerGame.player)
stats = list(query.first())
stats = [0 if s is None else s for s in stats]
if raw:
return [float(s) for s in stats], [float(s) for s in global_stats]
else:
return self.compare_to_global(stats, global_stats,
global_stds), len(stats) * [0.0]
def get_averaged_stats(self,
session,
id_,
rank=None,
redis=None,
raw=False,
replay_ids=None):
stats_query = self.stats_query
std_query = self.std_query
total_games = self.player_wrapper.get_total_games(
session, id_, replay_ids=replay_ids)
if total_games > 0:
stats, global_stats = self.get_stats(session,
id_,
stats_query,
std_query,
rank=rank,
redis=redis,
raw=raw,
replay_ids=replay_ids)
else:
stats = [0.0] * len(stats_query)
global_stats = [0.0] * len(stats_query)
return self.get_wrapped_stats(stats), self.get_wrapped_stats(
global_stats)
def get_progression_stats(self, session, id_):
def float_maybe(f):
if f is None:
return None
else:
return float(f)
mean_query = session.query(
func.to_char(Game.match_date,
'YY-MM').label('date'), *self.stats_query
).join(PlayerGame).filter(PlayerGame.time_in_game > 0).filter(
PlayerGame.player == id_).group_by('date').order_by('date').all()
std_query = session.query(
func.to_char(Game.match_date,
'YY-MM').label('date'), *self.std_query
).join(PlayerGame).filter(PlayerGame.time_in_game > 0).filter(
PlayerGame.player == id_).group_by('date').order_by('date').all()
mean_query = [list(q) for q in mean_query]
std_query = [list(q) for q in std_query]
results = []
for q, s in zip(mean_query, std_query):
result = {
'name':
datetime.datetime.strptime(q[0], '%y-%m').isoformat(),
'average':
self.get_wrapped_stats([float_maybe(qn) for qn in q[1:]]),
'std_dev':
self.get_wrapped_stats([float_maybe(qn) for qn in s[1:]])
}
results.append(result)
return results
@staticmethod
def get_stat_spider_charts():
titles = [ # 'Basic',
'Aggressiveness', 'Chemistry', 'Skill', 'Tendencies', 'Luck'
]
groups = [ # ['score', 'goals', 'assists', 'saves', 'turnovers'], # basic
[
'shots', 'possession', 'hits', 'shots/hit', 'boost usage',
'speed'
], # agressive
[
'total boost efficiency', 'assists', 'passes/hit', 'passes',
'assists/hit'
], # chemistry
[
'turnover efficiency', 'useful/hits', 'aerials',
'won turnovers', 'avg hit dist'
], # skill
['att 1/3', 'att 1/2', 'def 1/2', 'def 1/3', '< ball', '> ball']
] # , # tendencies
# ['luck1', 'luck2', 'luck3', 'luck4']] # luck
return [{
'title': title,
'group': group
} for title, group in zip(titles, groups)]
def _create_stats(self, session, player_filter=None, replay_ids=None):
average = QueryFilterBuilder().with_stat_query(self.stats_query)
std_devs = QueryFilterBuilder().with_stat_query(self.std_query)
if player_filter is not None:
average.with_players(player_filter)
std_devs.with_players(player_filter)
if replay_ids is not None:
average.with_replay_ids(replay_ids)
std_devs.with_replay_ids(replay_ids)
average = average.build_query(session).filter(
PlayerGame.time_in_game > 0).first()
std_devs = std_devs.build_query(session).filter(
PlayerGame.time_in_game > 0).first()
average = {
n.field_name: round(float(s), 2)
for n, s in zip(self.field_names, average) if s is not None
}
std_devs = {
n.field_name: round(float(s), 2)
for n, s in zip(self.field_names, std_devs) if s is not None
}
return {'average': average, 'std_dev': std_devs}
def get_group_stats(self, session, replay_ids):
return_obj = {}
# Players
player_tuples: List[Tuple[str, str, int]] = session.query(
PlayerGame.player, func.min(PlayerGame.name),
func.count(PlayerGame.player)).filter(
PlayerGame.game.in_(replay_ids)).group_by(
PlayerGame.player).all()
return_obj['playerStats'] = {}
# ensemble are the players that do not have enough replays to make an individual analysis for them
ensemble = []
for player_tuple in player_tuples:
player, name, count = player_tuple
if count > 1:
player_stats = self._create_stats(session,
player_filter=player,
replay_ids=replay_ids)
player_stats['name'] = name
return_obj['playerStats'][player] = player_stats
else:
ensemble.append(player)
if len(ensemble) > 0:
# create stats that only includes the ensemble
ensemble_stats = self._create_stats(session,
player_filter=ensemble,
replay_ids=replay_ids)
return_obj['ensembleStats'] = ensemble_stats
# STATS
# Global
# create stats that include all the players in the game
# global_stats = self._create_stats(session, ids=replay_ids)
# return_obj['globalStats'] = global_stats
return return_obj