def get_students_grade_gpa(self):
'''
Returns a query that can returns a table with "grade", "district", "school_code" column to the
high_school_gpa table
'''
high_school_gpa = db_tables.clean_high_school_gpa_table
all_snapshots = db_tables.clean_all_snapshots_table
left = sql.select([
all_snapshots.c.student_lookup,
all_snapshots.c.grade,
all_snapshots.c.school_year,
all_snapshots.c.district,
all_snapshots.c.school_code
]).\
where(
sql.and_(
all_snapshots.c.grade >= 9,
all_snapshots.c.grade <= 12
)
).alias('a')
right = high_school_gpa.alias('b')
joined = sql.join(
left=left,
right=right,
onclause=sql.and_(
left.c.student_lookup == right.c.student_lookup,
left.c.school_year == right.c.school_year,
)
)
overall_percentile = func.percent_rank().\
over(order_by= joined.c.b_gpa.desc(),
partition_by=[joined.c.a_school_year, joined.c.a_grade])
district_percentile = func.percent_rank().\
over(order_by= joined.c.b_gpa.desc(),
partition_by=[joined.c.a_school_year, joined.c.a_grade, joined.c.a_district])
school_percentile = func.percent_rank().\
over(order_by= joined.c.b_gpa.desc(),
partition_by=[joined.c.a_school_year, joined.c.a_grade, joined.c.a_district, joined.c.a_school_code])
return sql.select([
joined.c.a_student_lookup,
joined.c.a_grade,
joined.c.a_school_year,
joined.c.a_district,
joined.c.a_school_code,
joined.c.b_gpa,
joined.c.b_num_classes,
overall_percentile.label('overall_percentile'),
district_percentile.label('district_percentile'),
school_percentile.label('school_percentile')
]).\
select_from(joined).\
group_by(*list(joined.c))
def test_percent_rank(self):
expr = func.percent_rank(0.5).within_group(
column('data', Integer))
is_(expr.type._type_affinity, Numeric)
def generateTeamsService(players):
if (len(players) < 2):
return {'error': 'Too few players signed up.'}
player1s = []
authed_players = []
for player in players:
if player.startswith("player") and player[len("player"):] in map(
str, range(1, 13)):
player1 = Trueskillrating()
player1.username = player
player1s.append(player1)
else:
authed_players.append(player)
# Get the "percent rank" for each player passed in
subquery = db.session.query(
Trueskillrating.username,
func.percent_rank().over(
order_by=Trueskillrating.rating.desc()).label('pct-rnk'),
).filter_by(matchtype='pickup-fortress-all').subquery()
query = db.session.query(subquery).filter(
func.lower(subquery.c.username).in_(
[p.lower() for p in authed_players]))
percent_ranked_ratings = query.all()
s_tiers, a_tiers, b_tiers, c_tiers = ([] for i in range(4))
s_tier = .1 # top 10% of rankings
a_tier = .25 # 10-25%
b_tier = .6 # 25-60%
c_tier = 1 # 60-100
# Shuffle the players so that players within a tier are in a random order. We will sort by tier next.
random.shuffle(percent_ranked_ratings)
for rating in percent_ranked_ratings:
percent_rank = rating[1]
if (percent_rank <= s_tier):
s_tiers.append(rating)
elif (percent_rank <= a_tier):
a_tiers.append(rating)
elif (percent_rank <= b_tier):
b_tiers.append(rating)
elif (percent_rank <= c_tier):
c_tiers.append(rating)
c_tiers.extend(player1s)
# Shuffle c_tiers again so player1s aren't always at the end
random.shuffle(c_tiers)
# Separate s, a, and b tier players for selecting captain, if we have enough from those tiers.
semi_sorted_ratings_sab = s_tiers + a_tiers + b_tiers
semi_sorted_ratings_all = s_tiers + a_tiers + b_tiers + c_tiers
# If we have enough players in s, a and b tiers, randomly choose captains from there
if (len(semi_sorted_ratings_sab) > 3):
captain_1_index = random.randrange(0,
len(semi_sorted_ratings_sab) - 1, 2)
captain_2_index = captain_1_index + 1
captain_2 = semi_sorted_ratings_sab[captain_2_index].username
captain_1 = semi_sorted_ratings_sab[captain_1_index].username
else:
captain_1_index = random.randrange(0,
len(semi_sorted_ratings_all) - 1, 2)
captain_2_index = captain_1_index + 1
captain_2 = semi_sorted_ratings_all[captain_2_index].username
captain_1 = semi_sorted_ratings_all[captain_1_index].username
team_2 = []
team_1 = []
counter = 2
# Assign players to teams using abbaabb... approach.
while (len(semi_sorted_ratings_all) > 0):
if (counter <= 2):
team_2.append(semi_sorted_ratings_all.pop(0).username)
elif (counter <= 4):
team_1.append(semi_sorted_ratings_all.pop(0).username)
elif (counter == 5):
counter = 0
counter += 1
# Figure out which team our captians are in since we assigned teams after choosing captains.
if captain_1 in team_1:
team_1_captain = captain_1
team_2_captain = captain_2
else:
team_1_captain = captain_2
team_2_captain = captain_1
return {
'team_1': {
'captain': team_1_captain,
'players': team_1
},
'team_2': {
'captain': team_2_captain,
'players': team_2
}
}
