EGD-mine Changeset - 4ebd2c7c3657

Changeset - 4ebd2c7c3657

Parent rev.

Child rev.

[Not reviewed]

default

0 1 0

Laman - 7 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):
@@ @@ -25,27 +29,50 @@ def playerYear(name,year): @@
 		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)
@@ @@ -56,7 +83,8 @@ def timeRank(year,country): @@
 				(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")
@@ @@ -95,7 +123,8 @@ def populationAge(year,country): @@
 					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áčů")
@@ @@ -121,3 +150,4 @@ def tournamentAttendance(year): @@
 # populationStrength(2017,"CZ")
 # populationAge(2017,"CZ")
 # tournamentAttendance(2010)
 yearsByCountry()

0 comments (0 inline, 0 general)