def avg_competitions(): sub_queries = [sub_query % ({"diff": i + 1}) for i in range(period)] final_query = query % ({ "min_year": min_year, "max_year": max_year, "sub_queries": ",".join(sub_queries) }) log.info("Connect to the database") cnx = get_database_connection() cursor = cnx.cursor(final_query) cursor.execute(final_query) table = cursor.fetchall() log.info("Found %s countries" % len(table)) out = {} out["title"] = title out["groupName"] = "Countries" out["displayMode"] = "DEFAULT" headers = ["Avg", "Country", *range(min_year, max_year + 1)] out["statistics"] = [{ "keys": [], "content": table, "headers": headers, "showPositions": True, "positionTieBreakerIndex": 0 }] cnx.close() return out
def best_podiums(): statistics = {} statistics["title"] = title statistics["groupName"] = "Results" statistics["statistics"] = [] statistics["displayMode"] = "SELECTOR" headers = [ "Competition", "Sum", "First", "Result", "Second", "Result", "Third", "Result" ] current_events = get_current_events() cnx = get_database_connection() cursor = cnx.cursor() for current_event in current_events: log.info(current_event.name) # Not an sql replacement cursor.execute(query % current_event.event_id) lines = cursor.fetchall() # Also skips 333mbf if not lines: continue stat = [] for line in lines: result = [ get_competition_html_link(line[0], line[1]), str(int(line[2])) if current_event.event_id == '333mbf' else time_format(line[2], current_event.event_id, "average") ] # Each competitor for i in range(podium_length): result.append( get_competitor_html_link(line[3 + 3 * i], line[4 + 3 * i])) result.append( time_format(line[5 + 3 * i], current_event.event_id, "average")) stat.append(result) statistics["statistics"].append({ "keys": [current_event.name], "content": stat, "headers": headers, "showPositions": True, "positionTieBreakerIndex": 1 }) cnx.close() return statistics
def find_wr_single(cursor, event): log.info("Find WR single") cursor.execute(query_wr % event) result = cursor.fetchone() log.info("Result = %s" % result) return result[0]
def main(): log.info(" ========== %s ==========" % title) ev1 = "555bf" ev2 = "444bf" data = compare_results(ev1, ev2) create_statistics(data)
def get_current_events() -> List[Event]: log.info("Get current events") events = [] cnx = get_database_connection() cursor = cnx.cursor() cursor.execute(query) for id, name in cursor: event = Event(id, name) events.append(event) cnx.close() log.info("Found %s events" % len(events)) return events
def main(): log.info(" ========== %s ==========" % title) statistics = best_podiums() create_statistics(statistics)
def main(): log.info("========== %s ==========" % title) statistics = sub_x() create_statistics(statistics)
def sub_x(): LIMIT = 10 log.info("Get database connection") cnx = get_database_connection() cursor = cnx.cursor() statistics = {} statistics["title"] = title statistics["groupName"] = "Results" statistics["statistics"] = [] headers = ["Count", "Name", "Country"] statistics["displayMode"] = "GROUPED" for current_event in get_current_events(): competitors = [] event = current_event.event_id if event == "333mbf": continue log.info("Find sub x for %s" % current_event.name) wr_single = find_wr_single(cursor, event) wr_index = wr_single if event == "333fm" else wr_single // 100 log.info("WR single for %s is %s" % (event, time_format(wr_single, event))) log.info("Compute sub %s results" % (normalize_result(wr_single, event)+RANGE)) cursor.execute(query_results % event) for wca_id, person_name, country_id, best, v1, v2, v3, v4, v5 in cursor: # We exclude people with DNF or results out of the range if can_be_discarded(best, wr_index, event): continue competitor = Competitor(wca_id) i = bisect_left(competitors, competitor) if i == len(competitors) or competitors[i] != competitor: competitor.name = person_name competitor.country = country_id competitors.insert(i, competitor) competitor = competitors[i] for x in [v1, v2, v3, v4, v5]: if not can_be_discarded(x, wr_index, event): index = normalize_result(x, event)-wr_index competitor.count[index] += 1 log.info("%s elegible competitors" % len(competitors)) for i in range(RANGE): sorted_i = sorted( filter(lambda c: sum_to_index(c, i) > 0, competitors), key=lambda c: sum_to_index(c, i))[::-1] c = 0 prev = None stat = [] for x in sorted_i: s = sum_to_index(x, i) # Ties if c >= LIMIT and s != prev: break stat.append([s, get_competitor_html_link( x.wca_id, x.name), x.country]) prev = s c += 1 index = i+wr_index+1 current_sub = index if event == "333fm" else index*100 statistics["statistics"].append( {"keys": [current_event.name, "Sub %s" % time_format(current_sub, event)], "content": stat, "headers": headers, "showPositions": True, "positionTieBreakerIndex": 0, "sqlQueryCustom": query_custom % {"event_id": event, "sub_x": (1+i+wr_single//100)*100 if event != '333fm' else (1+i+wr_single)}}) cnx.close() return statistics
def main(): log.info("========== %s ==========" % title) statistics = longest_streaks() create_statistics(statistics)
def sum_of_all_ranks(): cnx = get_database_connection() cursor = cnx.cursor() statistics = {} statistics["title"] = title statistics["groupName"] = "Competitors" statistics["statistics"] = [] statistics["displayMode"] = "DEFAULT" for result_type in ["Average", "Single"]: events = [] cursor.execute(events_query % result_type) # Not an sql replacement for event_id, event_name, max_rank in cursor: event = Event(event_id, event_name, max_rank) events.append(event) log.info("Found %s events" % len(events)) event_ids = list(map(lambda e: e.event_id, events)) cursor.execute(competitors_query % result_type) # Not an sql replacement competitors = [] prev_event = None event_index = None log.info("Handle competitors ranks") for (wca_id, world_rank, event_id, name) in cursor: competitor = Competitor(wca_id, events) index = bisect_left(competitors, competitor) if index == len(competitors) or competitor != competitors[index]: competitor.name = name competitors.insert(index, competitor) competitor = competitors[index] if event_id != prev_event: event_index = event_ids.index(event_id) prev_event = event_id competitor.ranks[event_index] = world_rank log.info("Found %s competitors" % len(competitors)) log.info("Fill sum of ranks") for competitor in competitors: competitor.sum_of_ranks = sum(competitor.ranks) log.info("Sort by sum of ranks") competitors = sorted(competitors, key=lambda c: c.sum_of_ranks) c = 0 prev = None stat = [] for competitor in competitors: s = competitor.sum_of_ranks if c >= LIMIT and s != prev: break prev = s # Mark some results as red for i in range(len(events)): if competitor.ranks[i] == events[i].max_rank: competitor.ranks[ i] = "<span style=\"color:red\">%s</span>" % ( competitor.ranks[i]) stat.append([ get_competitor_html_link(competitor.wca_id, competitor.name), s, *competitor.ranks ]) c += 1 custom_query = get_custom_query(result_type, events) headers = ["Name", "Sum", *map(lambda c: c.name, events)] statistics["statistics"].append({ "keys": [result_type], "content": stat, "headers": headers, "showPositions": True, "positionTieBreakerIndex": 1, "sqlQueryCustom": custom_query }) cnx.close() return statistics
def compare_results(ev1, ev2): """Checks if ev1 happened before ev2.""" # This won't work for 333mbf and 333mbo, since `best` is encoded differently. competitors = [] log.info("Get connection") cnx = get_database_connection() cursor = cnx.cursor() cursor.execute(query % (ev1, ev2)) for wca_id, country_id, name, best, start_date, competition_id, competition_name, event_id in cursor: competitor = Competitor(wca_id, country_id, name) i = bisect.bisect_left(competitors, competitor) if i == len(competitors) or competitors[i] != competitor: competitors.insert(i, competitor) competitor = competitors[i] # first ev1 result ever if event_id == ev1 and not competitor.first_results[0]: competitor.first_results[0] = best competitor.first_competitions[0] = [ competition_id, competition_name ] competitor.first_dates[0] = start_date elif event_id == ev2 and not competitor.first_results[ 1] and competitor.first_results[ 0] and best < competitor.first_results[0]: competitor.first_results[1] = best competitor.first_competitions[1] = [ competition_id, competition_name ] competitor.first_dates[1] = start_date competitor.diff = (competitor.first_dates[1] - competitor.first_dates[0]).days table = [] sorted_competitors = sorted(filter(lambda c: c.diff and c.diff > 0, competitors), key=lambda c: c.diff) for competitor in sorted_competitors: table.append([ competitor.diff, get_competitor_html_link(competitor.wca_id, competitor.name), time_format(competitor.first_results[0]), get_competition_html_link(competitor.first_competitions[0][0], competitor.first_competitions[0][1]), time_format(competitor.first_results[1]), get_competition_html_link(competitor.first_competitions[1][0], competitor.first_competitions[1][1]) ]) out = {} out["explanation"] = "In case of multiple first results (eg. ao3), best one is taken." out["title"] = title out["displayMode"] = "DEFAULT" out["groupName"] = "Competitors" headers = [ "Days", "Name", "First %s result" % ev1, "Competition", "First faster %s result" % ev2, "Competition" ] out["statistics"] = [{ "keys": [], "content": table, "headers": headers, "showPositions": True, "positionTieBreakerIndex": 0 }] return out
def main(): log.info(" ========== %s ==========" % title) stat = avg_competitions() create_statistics(stat)
def ranges(): LIMIT = 10 out = {} out["title"] = title out["groupName"] = "Results" out["displayMode"] = "SELECTOR" headers = ["Person", "Range Size", "Country", "Range Start", "Range End"] out["statistics"] = [] current_events = get_current_events() cnx = get_database_connection() cursor = cnx.cursor() log.info("Read results") for current_event in current_events: competitors = [] event = current_event.event_id log.info("Event = %s" % current_event.name) cursor.execute(query % current_event.event_id) for wca_id, person_name, country_id, v1, v2, v3, v4, v5 in cursor: competitor = Competitor(wca_id, person_name, country_id) i = bisect.bisect_left(competitors, competitor) if i == len(competitors) or competitors[i] != competitor: competitors.insert(i, competitor) competitor = competitors[i] for x in [v1, v2, v3, v4, v5]: if x < 1: continue if event == "333mbf": x = str(x) points = get_mbld_points(x)[0] j = bisect.bisect_left(competitor.results, points) if j == len(competitor.results ) or competitor.results[j] != points: competitor.results.insert(j, points) else: j = bisect.bisect_left(competitor.results, x) if j == len( competitor.results) or competitor.results[j] != x: competitor.results.insert(j, x) log.info("Found %s competitors" % len(competitors)) log.info("Organize ranges") for competitor in competitors: # skipping people with only 1 result range_size, range_start, range_end = largest_range( competitor.results) if range_size == 1: continue competitor.range = range_size competitor.range_start = range_start competitor.range_end = range_end log.info("Sort results") competitors = sorted(filter(lambda c: c.range, competitors), key=lambda c: -c.range) log.info("Found %s elegible competitors" % len(competitors)) table = [] log.info("Compute table") prev = None count = 0 for competitor in competitors: count += 1 if count > LIMIT and prev != competitor.range: break if event in ["333fm", "333mbf"]: range_start = competitor.range_start range_end = competitor.range_end else: range_start = time_format(competitor.range_start) range_end = time_format(competitor.range_end) link = get_competitor_html_link(competitor.wca_id, competitor.name) table.append([ link, competitor.range, competitor.country, range_start, range_end ]) prev = competitor.range explanation = "Competitors that got all results from the range start to the range end in %s" % ( "steps of 1" if event in ("333fm", "333mbf") else "steps of 0.01") out["statistics"].append({ "keys": [current_event.name], "content": table, "headers": headers, "explanation": explanation, "showPositions": True, "positionTieBreakerIndex": 1 }) cnx.close() return out
def main(): log.info(" ========== %s ==========" % title) out = ranges() create_statistics(out)
def longest_streaks(): LIMIT = 50 out = {} out["title"] = title out["groupName"] = "Results" out["displayMode"] = "SELECTOR" headers = ["Streak", "Person", "Country", "Streak Start", "Streak End"] out["statistics"] = [] log.info("Get database connection") cnx = get_database_connection() cursor = cnx.cursor() for event in get_current_events(): log.info(event.name) event_id = event.event_id competitors = [] cursor.execute(query, {"event_id": event_id}) for wca_id, person_name, country_id, competition_id, v1, v2, v3, v4, v5 in cursor: competitor = Competitor(wca_id, person_name, country_id) i = bisect.bisect_left(competitors, competitor) if i == len(competitors) or competitors[i] != competitor: competitors.insert(i, competitor) competitor = competitors[i] for x in [v1, v2, v3, v4, v5]: if x == -1: competitor.count = 0 elif x > 0: competitor.count += 1 if competitor.count >= competitor.max: competitor.max = competitor.count competitor.max_range_start = competitor.current_range_start competitor.max_range_end = competition_id if competitor.count == 1: competitor.current_range_start = competition_id competitors = sorted(competitors, key=lambda c: -c.max) table = [] count = 1 prev = None for competitor in competitors: streak = competitor.max if count > LIMIT and prev != streak: break current = competitor.count link = get_competitor_html_link(competitor.wca_id, competitor.name) table.append([ streak, link, competitor.country, get_competition_html_link(competitor.max_range_start), "-" if streak == current else get_competition_html_link( competitor.max_range_end) ]) count += 1 prev = streak out["statistics"].append({ "keys": [event.name], "content": table, "headers": headers, "showPositions": True, "positionTieBreakerIndex": 0 }) cnx.close() return out
def main(): log.info("========== %s ==========" % title) statistics = recent_success() create_statistics(statistics)
def compare_results(ev1, ev2): """Checks if a competitor got ev1 before ev2, expect for 333mbf and 333mbo, due to time encode.""" competitors = [] log.info("Connect to the database") cnx = get_database_connection() cursor = cnx.cursor() cursor.execute(query % (ev1, ev2)) evs = [ev1, ev2] log.info("Assign results") for wca_id, name, country_id, event_id, best, competition_id, competition_name, date in cursor: competitor = Competitor(wca_id, country_id, name) i = bisect.bisect_left(competitors, competitor) if i == len(competitors) or competitors[i] != competitor: competitor.first_results = [None, None] competitor.first_competitions = [None, None] competitor.first_dates = [None, None] competitor.diff = None competitors.insert(i, competitor) competitor = competitors[i] j = evs.index(event_id) if competitor.first_results[j] == None: competitor.first_results[j] = time_format(best) competitor.first_competitions[j] = [ competition_id, competition_name ] competitor.first_dates[j] = date log.info("Found %s competitors" % len(competitors)) competitors = list( filter(lambda c: c.first_results[0] and c.first_results[1], competitors)) log.info("Found %s competitors with both success" % len(competitors)) log.info("Fill diffs") for competitor in competitors: competitor.diff = (competitor.first_dates[1] - competitor.first_dates[0]).days # Filter competitors = sorted(filter(lambda c: c.diff > 0, competitors), key=lambda c: -c.diff) log.info("Found %s competitors that got %s before %s" % (len(competitors), ev1, ev2)) table = [] for competitor in competitors: link = get_competitor_html_link(competitor.wca_id, competitor.name) table.append([ competitor.diff, link, competitor.first_results[0], get_competition_html_link(competitor.first_competitions[0][0], competitor.first_competitions[0][1]), competitor.first_results[1], get_competition_html_link(competitor.first_competitions[1][0], competitor.first_competitions[1][1]) ]) headers = [ "Days", "Name", "First result %s" % ev1, "Competition", "First result %s" % ev2, "Competition" ] out = {} out["title"] = title out["groupName"] = "Events" out["displayMode"] = "DEFAULT" out["explanation"] = "In case of multiple first results (eg. ao3), best one is taken." out["statistics"] = [{ "keys": [], "content": table, "headers": headers, "showPositions": True, "positionTieBreakerIndex": 0 }] cnx.close() return out
def recent_success(): LIMIT = 10 MIN_SOLVES = 6 min_date = datetime.date.today() - relativedelta(years=1) log.info("Min date: %s" % min_date) out = {} out["title"] = title out["explanation"] = "Since %s, minimum %s successes" % (min_date, MIN_SOLVES) out["groupName"] = "Results" out["displayMode"] = "SELECTOR" headers = [ "Person", "Country", "Rate", "Success / Attempts", "Best", "Worst", "Average" ] out["statistics"] = [] log.info("Get database connection") cnx = get_database_connection() cursor = cnx.cursor() for event in get_current_events(): log.info(event.name) event_id = event.event_id competitors = [] log.info("Find results") cursor.execute(query, {"event_id": event_id, "min_date": min_date}) for wca_id, person_name, country_id, v1, v2, v3, v4, v5 in cursor: competitor = Competitor(wca_id, person_name, country_id) i = bisect.bisect_left(competitors, competitor) if i == len(competitors) or competitors[i] != competitor: competitors.insert(i, competitor) competitor = competitors[i] for x in [v1, v2, v3, v4, v5]: if x == -1 or x > 0: competitor.attempts += 1 if x > 0: competitor.results.append(x) log.info("Found %s competitors" % len(competitors)) log.info("Discard competitors with few results") competitors = list( filter(lambda c: len(c.results) >= MIN_SOLVES, competitors)) log.info("%s competitors left" % len(competitors)) log.info("Fill rates") for competitor in competitors: competitor.rate = float( "%.2f" % (len(competitor.results) / competitor.attempts)) log.info("Sort competitors") competitors = sorted( competitors, key=lambda c: [-c.rate, -len(c.results), -c.attempts, c.name]) table = [] count = 1 prev = None for competitor in competitors: rate = competitor.rate if (count > LIMIT and prev != rate ) or count > 2 * LIMIT: # We interrupt the list anyways break link = get_competitor_html_link(competitor.wca_id, competitor.name) table.append([ link, competitor.country, "%.2f" % rate, "%s / %s" % (len(competitor.results), competitor.attempts), time_format(min(competitor.results), event.event_id), time_format(max(competitor.results), event.event_id), time_format( 0 if event_id == '333mbf' else sum(competitor.results) / len(competitor.results), event.event_id, "average") ]) count += 1 prev = rate out["statistics"].append({ "keys": [event.name], "content": table, "headers": headers, "showPositions": True, "positionTieBreakerIndex": 2, "sqlQueryCustom": custom_query % { "event_id": event_id, "min_date": min_date } }) cnx.close() return out
def main(): log.info(" ========== %s ==========" % title) statistics = sum_of_all_ranks() create_statistics(statistics)
def main(): log.info(" ========== %s ==========" % title) stat = avg_events() create_statistics(stat)