import sys from egd import connection cursor=connection.cursor() def yearStart(year): return "{0}-01-01".format(year) def playerHistory(name,year=0): """Výsledky hráče s daným jménem pro daný rok nebo pro celou historii.""" query=""" select name, datum, win_count, game_count, rating_after, rating_after-rating_before from players natural join players_performance as pp join tournaments as ts on pp.tournament_code=ts.code where name=? and datum>=date(?) and datum<date(?) order by datum asc """ if year: date1=yearStart(year) date2=yearStart(year+1) else: date1="1990-01-01" date2="2020-01-01" res=cursor.execute(query,(name,date1,date2)) print("jméno\tdatum\tpočet výher\tpočet partií\trating po turnaji\tzměna") for rec in res: print("\t".join([str(x) for x in rec])) def _yearByCountry(year): """Počty hráčů s turnajem odehraným v daném roce, dělené podle země původu. Při změně příslušnosti se hráč započítá oběma zemím.""" date1=yearStart(year) date2=yearStart(year+1) query=""" select p_country_code,count(pin) as player_count from ( select pin,count(*),p_country_code from players_performance_tournaments where datum>=date(?) and datum<date(?) group by pin,p_country_code ) group by p_country_code order by p_country_code asc """ return cursor.execute(query,(date1,date2)) def yearsByCountry(year1,year2): res=dict() for year in range(year1,year2): records=_yearByCountry(year) for (countryCode,playerCount) in records: if countryCode not in res: res[countryCode]=dict() res[countryCode][year]=playerCount print("\r",year,end="",file=sys.stderr) print("\r",end="",file=sys.stderr) print("Počty aktivních hráčů podle let") print("kód země\t"+"\t".join(str(y) for y in range(year1,year2))) for countryCode in sorted(res.keys()): print(countryCode,end="\t") print("\t".join(str(res[countryCode].get(y,0)) for y in range(year1,year2))) def timeRank(year,country): """Výpis hráčů dané země aktivních v daném roce. Rating platí ke konci roku.""" date1=yearStart(year) date2=yearStart(year+1) query=""" select ppt.pin,date(?)-min_datum-1,min_datum,ppt.rating_after as rating,name from (select pin,min(datum) as min_datum,max(datum) as max_datum from players_performance_tournaments where datum<date(?) group by pin having max_datum>=date(?)) as subq join players_performance_tournaments as ppt on subq.pin=ppt.pin and subq.max_datum=ppt.datum natural join players where ppt.p_country_code=? order by rating desc """ res=cursor.execute(query,(date2,date2,date1,country)) print("pin\tcelé roky hraní\tprvní turnaj\trating") for rec in res: print("\t".join([str(x) for x in rec])) def _populationStrength(year,country): """Rozdělení populace země v daném roce podle ratingu (po stovkách). Uvažují se pouze aktivní hráči.""" 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 """ return cursor.execute(query,(date1,date2,country)) def populationStrength(year1,year2,country): res=dict() for year in range(year1,year2): records=_populationStrength(year,country) for (rating,playerCount) in records: if rating not in res: res[rating]=dict() res[rating][year]=playerCount print("\r",year,end="",file=sys.stderr) print("\r",end="",file=sys.stderr) print("Počty aktivních hráčů v {0} s uvedeným a vyšším ratingem".format(country)) print("rating\t"+"\t".join(str(y) for y in range(year1,year2))) for rating in sorted(res.keys()): print(rating,end="\t") print("\t".join(str(res[rating].get(y,0)) for y in range(year1,year2))) def _populationAge(year,country): """Rozdělení populace země v daném roce podle doby hraní (celých let od prvního turnaje). Uvažují se pouze aktivní hráči.""" date1=yearStart(year) date2=yearStart(year+1) query=""" select date(?)-min_datum-1 as playing_years,count(pin) from ( select ppt1.pin,min(ppt1.datum) as min_datum from players_performance_tournaments as ppt1 join players_performance_tournaments as ppt2 on ppt1.pin=ppt2.pin where ppt2.datum>=date(?) and ppt2.datum<date(?) and ppt2.p_country_code=? group by ppt2.pin ) group by playing_years """ return cursor.execute(query,(date2,date1,date2,country)) def populationAge(year1,year2,country): res=dict() for year in range(year1,year2): records=_populationAge(year,country) for (age,playerCount) in records: if age not in res: res[age]=dict() res[age][year]=playerCount print("\r",year,end="",file=sys.stderr) print("\r",end="",file=sys.stderr) print("Počty aktivních hráčů v {0} hrajících tolik a více let".format(country)) print("roky hraní\t"+"\t".join(str(y) for y in range(year1,year2))) for age in sorted(res.keys()): print(age,end="\t") print("\t".join(str(res[age].get(y,0)) for y in range(year1,year2))) def _tournamentAttendance(year): """Návštěvnosti turnajů za daný rok, dělené podle země konání.""" date1=yearStart(year) date2=yearStart(year+1) query="""select country_code,sum(player_count) from tournaments where datum>=date(?) and datum<date(?) group by country_code""" return cursor.execute(query,(date1,date2)) def tournamentAttendance(year1,year2): res=dict() for year in range(year1,year2): records=_tournamentAttendance(year) for (country,attendance) in records: if country not in res: res[country]=dict() res[country][year]=attendance print("\r",year,end="",file=sys.stderr) print("\r",end="",file=sys.stderr) print("Návštěvnosti (počty hráčů) na turnajích podle let") print("kód země\t"+"\t".join(str(y) for y in range(year1,year2))) for country in sorted(res.keys()): print(country,end="\t") print("\t".join(str(res[country].get(y,0)) for y in range(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""" res=cursor.execute(query,(strongerThan,country,country)) print("pin\tjméno\tmax rating") for rec in res: print("\t".join([str(x) for x in rec])) def _tournamentsToPeak(): """Vrací slovník {pin: počet_turnajů_k_dosažení_max_ratingu}""" query="""select pin,rating_after,max_rating from players_performance_tournaments join (select pin as sub_pin,max(rating_after) as max_rating from players_performance group by pin) on pin=sub_pin order by datum asc""" res=cursor.execute(query) players=dict() closed=set() for (pin,rating,maxRating) in res: tc=players.get(pin,0) if pin not in closed: players[pin]=tc+1 if rating==maxRating: closed.add(pin) return players 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.""" # 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,(julianday(min(datum))-julianday(min(_min_datum)))/365.25 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 """ tournamentCounts=_tournamentsToPeak() res=cursor.execute(query) print("pin\tmin rating\tmax_rating\tprvní turnaj\tdosažení max ratingu\ttime_to_peak\tturnaje k max ratingu") for rec in res: pin=rec[0] print("\t".join([str(x) for x in rec]+[str(tournamentCounts[pin])])) def playersProgress(): """Všem hráčům vypíše rating po jednotlivých letech. Zobrazuje rating na konci roku, zaokrouhlený dolů na stovky. Nula značí rok bez záznamu v databázi.""" y1=1996 y2=2018 class Player: def __init__(self): self.rating=dict() self.country="" self.tournamentCount=0 query=""" select pin,p_country_code as country_code,t_count,cast(strftime('%Y',datum) as int) as year,rating_after,datum from players_performance_tournaments as ppt join (select pin as sub_pin,count(tournament_code) as t_count from players_performance group by pin) on ppt.pin=sub_pin order by datum desc""" res=cursor.execute(query) players=dict() for (i,rec) in enumerate(res): (pin,country,tournamentCount,year,rating,_)=rec if pin in players: p=players[pin] else: p=Player() p.country=country p.tournamentCount=tournamentCount players[pin]=p if year not in p.rating: p.rating[year]=round(rating//100*100) if i%1000==0: print(i,"/ ?","\r",end="",file=sys.stderr) print(" "*20,file=sys.stderr) print("pin\tkód země\tpočet turnajů\t"+"\t".join(str(y) for y in range(y1,y2))) for (pin,p) in players.items(): print(pin,p.country,p.tournamentCount,*(p.rating.get(y,0) for y in range(y1,y2)),sep="\t") def lifeSpans(): """Životnosti hráčů. Datum prvního a posledního turnaje, jejich rozdíl v letech, počet odehraných turnajů.""" query="""select pin,min(datum) as min_datum,max(datum) as max_datum,(julianday(max(datum))-julianday(min(datum)))/365.25 as lifespan,count(datum) as t_count,max(rating_after) as max_rating,min(rating_before) as min_rating from players_performance_tournaments group by pin""" res=cursor.execute(query) print("pin\tmin datum\tmax datum\tmax-min\tpočet turnajů\tmax rating\tmin rating") for rec in res: print("\t".join([str(x) for x in rec])) # playerHistory("Kotowski Jaroslav") # timeRank(2017,"CZ") # yearsByCountry(2001,2018) # populationStrength(2001,2018,"RU") # populationAge(2001,2018,"RU") # tournamentAttendance(2001,2018) # fetchByRating(2600,"CZ") # timeToPeak() # playersProgress() lifeSpans()