def index_old(self): from gviz_data_table import Table from rockpack.mainsite.services.user.models import User, UserActivity user_count = readonly_session.query(func.count(User.id)).\ filter(User.refresh_token != '').scalar() header = ('user count', 'max lifetime', 'avg lifetime', 'stddev lifetime', 'max active days', 'avg active days', 'stddev active days') lifetime = func.date_part('days', func.max(UserActivity.date_actioned) - func.min(UserActivity.date_actioned)).label('lifetime') active_days = func.count(func.distinct(func.date( UserActivity.date_actioned))).label('active_days') activity = readonly_session.query(UserActivity.user, lifetime, active_days).\ group_by(UserActivity.user) ctx = {} for key, having_expr in ('all', None), ('1day', lifetime > 1), ('7day', lifetime > 7): data = activity.having(having_expr).from_self( func.count('*'), func.max(lifetime), func.avg(lifetime), func.stddev_samp(lifetime), func.max(active_days), func.avg(active_days), func.stddev_samp(active_days) ).one() table = Table([ dict(id='metric', type=str), dict(id='value', type=float), dict(id='%', type=str), ]) pdata = ('%d%%' % (data[0] * 100 / user_count),) + ('',) * 6 table.extend(zip(*(header, map(float, data), pdata))) ctx['ret_%s_data' % key] = table.encode() return self.render('admin/retention_stats_old.html', **ctx)
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_apartments_stats(): from app.main import db agg_stats = db.session.query( func.avg(Apartment.price_uah), func.stddev_samp(Apartment.price_uah), func.avg(Apartment.total_square_meters), func.stddev_samp(Apartment.total_square_meters), func.count(Apartment.id)).all()[0] agg_stats = ["{:.2f}".format(float(x)) for x in agg_stats] return { 'mean_price': agg_stats[0], 'std_price': agg_stats[1], 'mean_total_square_meters': agg_stats[2], 'std_total_square_meters': agg_stats[3], 'total_apartments_number': int(agg_stats[4]) }
def get_stats_query(stat_list: List[QueryFieldWrapper]): avg_list = [] std_list = [] for stat in stat_list: if stat.is_cumulative: std_list.append(literal(1)) avg_list.append(stat.query) elif stat.is_averaged or stat.is_percent: std_list.append(func.stddev_samp(stat.query)) avg_list.append(func.avg(stat.query)) elif stat.is_boolean: std_list.append(literal(1)) avg_list.append(func.count()) else: std_list.append(func.stddev_samp(stat.query)) avg_list.append(300 * func.sum(stat.query) / safe_divide( func.sum(PlayerGame.time_in_game), default=300)) return avg_list, std_list
def convert_unary_expr(self, expr, input): fname = expr.__class__.__name__.lower() # replacements if fname == "stdev": return func.stddev_samp(input) # Warning: may create some functions not available in Postgres return getattr(func, fname)(input)
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_stats_query(): field_list = create_and_filter_proto_field( proto_message=player_pb2.Player, blacklist_field_names=['name', 'title_id', 'is_orange', 'is_bot'], blacklist_message_types=[ 'api.metadata.CameraSettings', 'api.metadata.PlayerLoadout', 'api.PlayerId' ], db_object=PlayerGame) stat_list = [] for field in field_list: field = getattr(PlayerGame, field.field_name) stat_list.append(field) stat_list += [ PlayerGame.boost_usage, PlayerGame.average_speed, PlayerGame.possession_time, PlayerGame.total_hits - PlayerGame.total_dribble_conts, # hits that are not dribbles (100 * PlayerGame.shots) / safe_divide(PlayerGame.total_hits - PlayerGame.total_dribble_conts ), # Shots per non dribble (100 * PlayerGame.total_passes) / safe_divide(PlayerGame.total_hits - PlayerGame.total_dribble_conts ), # passes per non dribble (100 * PlayerGame.assists) / safe_divide(PlayerGame.total_hits - PlayerGame.total_dribble_conts ), # assists per non dribble 100 * (PlayerGame.shots + PlayerGame.total_passes + PlayerGame.total_saves + PlayerGame.total_goals) / safe_divide(PlayerGame.total_hits - PlayerGame.total_dribble_conts ), # useful hit per non dribble PlayerGame.turnovers, func.sum(PlayerGame.goals) / safe_divide(cast(func.sum(PlayerGame.shots), sqlalchemy.Numeric)), PlayerGame.total_aerials, PlayerGame.time_in_attacking_half, PlayerGame.time_in_attacking_third, PlayerGame.time_in_defending_half, PlayerGame.time_in_defending_third, PlayerGame.time_behind_ball, PlayerGame.time_in_front_ball, func.random(), func.random(), func.random(), func.random(), PlayerGame.won_turnovers, PlayerGame.average_hit_distance, PlayerGame.total_passes, PlayerGame.wasted_collection, ] field_list += add_dynamic_fields([ 'boost usage', 'speed', 'possession', 'hits', 'shots/hit', 'passes/hit', 'assists/hit', 'useful/hits', 'turnovers', 'shot %', 'aerials', 'att 1/2', 'att 1/3', 'def 1/2', 'def 1/3', '< ball', '> ball', 'luck1', 'luck2', 'luck3', 'luck4', 'won turnovers', 'avg hit dist', 'passes', 'boost wasted' ]) avg_list = [] std_list = [] for i, s in enumerate(stat_list): if field_list[i].field_name in ['shot %']: std_list.append(literal(1)) avg_list.append(s) elif field_list[i].field_name in ['is_keyboard']: std_list.append(func.count(s)) avg_list.append(func.count(s)) else: std_list.append(func.stddev_samp(s)) avg_list.append( func.sum(s) / safe_divide(func.sum(PlayerGame.time_in_game)) * 300) return avg_list, field_list, std_list