EGD-mine Changeset - 4ebd2c7c3657

Changeset - 4ebd2c7c3657

Parent rev.

Child rev.

[Not reviewed]

default

0 1 0

Laman - 8 years ago 2018-04-01 13:08:32

yearByCountry rozšířeno na yearsByCountry

1 file changed with 37 insertions and 7 deletions:

queries.py

0 comments (0 inline, 0 general)

queries.py

➞

Show inline comments

 import sys
 from egd import connection
 cursor=connection.cursor()
 year1=2001
 year2=2018
 def yearStart(year):
 	return "{0}-01-01".format(year)
 def playerYear(name,year):
 	"""Výsledky hráče s daným jménem pro daný rok."""
 	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
 	"""
 	date1=yearStart(year)
 	date2=yearStart(year+1)
 	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):
+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)
 	# co hráči, kteří změní zemi?
 	query="""
 		select p_country_code,count(pin) from (
+		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;
 			order by p_country_code asc
 		"""
 	res=cursor.execute(query,(date1,date2))
 	return res
 def yearByCountry(year):
 	print("kód země\tpočet aktivních hráčů")
-	for rec in res:
+	for rec in _yearByCountry(year):
 		print("\t".join([str(x) for x in rec]))
 def yearsByCountry():
 	res=dict()
 	for year in range(year1,year2):
 		records=_yearByCountry(year)
 		for rec in records:
 			(countryCode,playerCount)=rec
 			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."""
 	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"""
 			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
 	"""
 	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(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 ppt1.p_country_code=?
 					group by ppt1.pin
+			)
 			group by playing_years"""
 			group by playing_years
 	"""
 	res=cursor.execute(query,(date2,date1,date2,country))
 	print("celé roky hraní\tpočet hráčů")
 	for rec in res:
 		print("\t".join([str(x) for x in rec]))
 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"""
 	res=cursor.execute(query,(date1,date2))
 	print("kód země\tpočet hráčů na turnajích")
 	for rec in res:
 		print("\t".join([str(x) for x in rec]))
 # playerYear("Kotowski Jaroslav",2017)
 # yearByCountry(2017)
 # timeRank(2017,"CZ")
 # populationStrength(2017,"CZ")
 # populationAge(2017,"CZ")
 # tournamentAttendance(2010)
 yearsByCountry()

0 comments (0 inline, 0 general)