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