EGD-mine Changeset - e8e04440f196

Changeset - e8e04440f196

Parent rev.

Child rev.

[Not reviewed]

default

0 1 0

Laman - 7 years ago 2018-05-04 16:38:26

dotaz timeToPeak

1 file changed with 27 insertions and 1 deletions:

queries.py

0 comments (0 inline, 0 general)

queries.py

➞

Show inline comments

@@ @@ -174,27 +174,53 @@ def tournamentAttendance(year1,year2): @@
 		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"""
 	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 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<date('2017-01-01')
 	"""
 	res=cursor.execute(query)
 	print("pin\tmin rating\tmax rating\tprvní turnaj\tdosažení max ratingu\troky k max ratingu")
 	for rec in res:
 		print("\t".join([str(x) for x in rec]))
 # playerYear("Kotowski Jaroslav",2017)
 # timeRank(2017,"CZ")
 # yearsByCountry(2001,2018)
 # populationStrength(2001,2018,"RU")
 # populationAge(2001,2018,"RU")
 # tournamentAttendance(2001,2018)
 # fetchByRating(2600,"CZ")
 # timeToPeak()

0 comments (0 inline, 0 general)