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 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
