diff --git a/egd.py b/egd.py --- a/egd.py +++ b/egd.py @@ -67,6 +67,9 @@ def setupDB(): from players_performance as pp join tournaments as ts on pp.tournament_code=ts.code """) + cursor.execute("""create index pp_country_ind on players_performance (country_code)""") + cursor.execute("""create index t_datum_ind on tournaments (datum)""") + connection.commit() diff --git a/queries.py b/queries.py --- a/queries.py +++ b/queries.py @@ -59,14 +59,32 @@ def timeRank(year,country): print("\t".join([str(x) for x in rec])) -def populationStrength(): - pass +def populationStrength(year,country): + date1=yearStart(year) + date2=yearStart(year+1) + query=""" + select round((rating_after-50)/100)*100 as rating, count(ppt.pin) + from players_performance_tournaments as ppt + join (select pin,max(datum) as max_datum from players_performance_tournaments where datum>=date(?) and datum<date(?) and p_country_code=? group by pin) as subq on ppt.pin=subq.pin and ppt.datum=subq.max_datum + group by rating + order by rating desc + """ + + res=cursor.execute(query,(date1,date2,country)) + print("rating\tpočet hráčů s takovým a vyšším ratingem") + for rec in res: + print("\t".join([str(x) for x in rec])) def populationAge(): pass +def tournamentAttendance(): + pass + + # playerYear("Kotowski Jaroslav",2017) # yearByCountry(2017) -timeRank(2017,"CZ") +# timeRank(2017,"CZ") +# populationStrength(2017,"CZ")