EGD-mine Changeset - 0aeebf95bfe7

Changeset - 0aeebf95bfe7

Parent rev.

Child rev.

[Not reviewed]

default

0 1 0

Laman - 7 years ago 2018-06-08 00:30:35

dotaz playersProgress, úprava timeToPeak

1 file changed with 59 insertions and 4 deletions:

queries.py

0 comments (0 inline, 0 general)

queries.py

➞

Show inline comments

@@ @@ -202,10 +202,25 @@ def fetchByRating(strongerThan,country=" @@
 		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 a s vrcholem před 1.1.2017."""
 	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="""
@@ @@ -213,13 +228,52 @@ def timeToPeak(): @@
 			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<date('2017-01-01')
 	"""
 	tournamentCounts=_tournamentsToPeak()
 	res=cursor.execute(query)
 	print("pin\tmin rating\tmax rating\tprvní turnaj\tdosažení max ratingu\troky k max ratingu")
 	print("pin\tmin rating\tmax rating\tprvní turnaj\tdosažení max ratingu\troky k max ratingu\tturnaje k max ratingu")
 	for rec in res:
 		print("\t".join([str(x) for x in rec]))
 		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")
 # playerHistory("Kotowski Jaroslav")
@@ @@ -230,3 +284,4 @@ def timeToPeak(): @@
 # tournamentAttendance(2001,2018)
 # fetchByRating(2600,"CZ")
 # timeToPeak()
 # playersProgress()

0 comments (0 inline, 0 general)