EGD-mine Changeset - e9a044cb8ce8

Changeset - e9a044cb8ce8

Parent rev.

Child rev.

[Not reviewed]

default

0 1 0

Laman - 7 years ago 2018-03-29 12:31:52

timeRank vrací jméno, upřesnění yearByCountry

1 file changed with 12 insertions and 10 deletions:

queries.py

0 comments (0 inline, 0 general)

queries.py

➞

Show inline comments

@@ @@ -26,18 +26,19 @@ def playerYear(name,year): @@
 def yearByCountry(year):
 	"""Počty hráčů s turnajem odehraným v daném roce, dělené podle země původu."""
+	"""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 country_code,count(pin) from (
 			select pin,count(*),pp.country_code from
 				players_performance as pp join tournaments as ts on pp.tournament_code=ts.code
 		select p_country_code,count(pin) from (
 			select pin,count(*),p_country_code from
 				players_performance_tournaments
 				where datum>=date(?) and datum<date(?)
 				group by pin
+				group by pin,p_country_code
+			)
 			group by country_code
 			order by country_code asc;
 			group by p_country_code
 			order by p_country_code asc;
 		"""
 	res=cursor.execute(query,(date1,date2))
 	print("kód země\tpočet aktivních hráčů")
@@ @@ -50,11 +51,12 @@ def timeRank(year,country): @@
 	date1=yearStart(year)
 	date2=yearStart(year+1)
 	query="""
 		select ppt.pin,date(?)-min_datum-1,min_datum,ppt.rating_after as rating
+		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
 			where ppt.p_country_code=?"""
 				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")

0 comments (0 inline, 0 general)