diff --git a/queries.py b/queries.py --- a/queries.py +++ b/queries.py @@ -183,7 +183,12 @@ def tournamentAttendance(year1,year2): def fetchByRating(strongerThan,country=""): """Hráči, kteří dosáhli rating strongerThan nebo vyšší, nepovinně filtrovaní podle země, seřazení od nejlepšího dosaženého ratingu.""" - query="""select pin,name,max(rating_after) as max_rating from players_performance natural join players where rating_after>=? and (?='' or country_code=?) group by pin order by max_rating desc""" + query=""" + select pin,name,max(rating_after) as max_rating + from players_performance natural join players + where rating_after>=? and (?='' or country_code=?) + group by pin + order by max_rating desc""" res=cursor.execute(query,(strongerThan,country,country)) print("pin\tjméno\tmax rating") @@ -191,6 +196,26 @@ def fetchByRating(strongerThan,country=" print("\t".join([str(x) for x in rec])) +def timeToPeak(): + """Jakého hráči dosáhli maximálního ratingu a jak dlouho jim to trvalo. + + Uvažuje hráče začínající s ratingem<=600 a s vrcholem před 1.1.2017.""" + # join on pin, max_rating může duplikovat řádky, když měl hráč stejný max_rating vícekrát. + # proto ten ošklivý vnější group by :-ú + query=""" + select subq.pin,min(min_rating),min(max_rating),min(_min_datum) as min_datum,min(datum) as peak_datum,min(datum)-min(_min_datum) as time_to_peak + from players_performance_tournaments as ppt + join (select pin,min(rating_before) as min_rating,max(rating_after) as max_rating,min(datum) as _min_datum from players_performance_tournaments group by pin having min_rating<=600) as subq on ppt.pin=subq.pin and ppt.rating_after=max_rating + group by ppt.pin + having peak_datum