Changeset - d72a89609095
Parent rev.
Child rev.
[Not reviewed]
default
0 2 0
Laman - 7 years ago 2018-03-27 12:34:06

timeRank query
2 files changed with 31 insertions and 6 deletions:
egd.py
5
queries.py
26
6
0 comments (0 inline, 0 general)
egd.py
➞
Show inline comments
 
@@ -61,6 +61,11 @@ def setupDB():
 
	cursor.execute("""create table tournaments (code text primary key, datum text, class text, round_count integer, player_count integer, country_code text, description text)""")
 
	cursor.execute("""create table players (pin integer primary key, name text)""")
 
	cursor.execute("""create table players_performance (pin integer references players(pin), tournament_code text references tournaments(code), country_code text, club text, win_count integer, game_count integer, rating_before real, rating_after real, primary key (pin,tournament_code))""")
 
	cursor.execute("""
 
		create view players_performance_tournaments (pin,p_country_code,rating_before,rating_after,t_country_code,datum) as
 
			select pin,pp.country_code,rating_before,rating_after,ts.country_code,datum
 
				from players_performance as pp join tournaments as ts on pp.tournament_code=ts.code
 
	""")
 

			




	
	
	
		
 
	connection.commit()

			




	
	
	
		
 

			




        

    


    
    

    

        

                

                    queries.py
                

                

                  
                      
                        

                      ➞

                      
                        
                  

                  
                      
                  
                      
                  
                      
                  
                      
                  
                  
                

                

                    Show inline comments
                    
                

        

        

            



	
	
		
 
@@ -4,6 +4,10 @@ from egd import connection

			




	
	
	
		
 
cursor=connection.cursor()

			




	
	
	
		
 

			




	
	
	
		
 

			




	
	
	
		
 
def yearStart(year):

			




	
	
	
		
 
	return "{0}-01-01".format(year)

			




	
	
	
		
 

			




	
	
	
		
 

			




	
	
	
		
 
def playerYear(name,year):

			




	
	
	
		
 
	query="""

			




	
	
	
		
 
		select name, datum, win_count, game_count, rating_after, rating_after-rating_before

			




	
	
		
 
@@ -11,8 +15,8 @@ def playerYear(name,year):

			




	
	
	
		
 
			where name=? and datum>=date(?) and datum<date(?)

			




	
	
	
		
 
			order by datum asc

			




	
	
	
		
 
	"""

			




	
	
	
		
 
	date1="{0}-01-01".format(year)

			




	
	
	
		
 
	date2="{0}-01-01".format(year+1)

			




	
	
	
		
 
	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")

			




	
	
		
 
@@ -21,8 +25,8 @@ def playerYear(name,year):

			




	
	
	
		
 

			




	
	
	
		
 

			




	
	
	
		
 
def yearByCountry(year):

			




	
	
	
		
 
	date1="{0}-01-01".format(year)

			




	
	
	
		
 
	date2="{0}-01-01".format(year+1)

			




	
	
	
		
 
	date1=yearStart(year)

			




	
	
	
		
 
	date2=yearStart(year+1)

			




	
	
	
		
 
	query="""

			




	
	
	
		
 
		select country_code,count(pin) from (

			




	
	
	
		
 
			select pin,count(*),pp.country_code from

			




	
	
		
 
@@ -39,6 +43,22 @@ def yearByCountry(year):

			




	
	
	
		
 
		print("\t".join([str(x) for x in rec]))

			




	
	
	
		
 

			




	
	
	
		
 

			




	
	
	
		
 
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

			




	
	
	
		
 
			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=?"""

			




	
	
	
		
 

			




	
	
	
		
 
	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():

			




	
	
	
		
 
	pass

			




	
	
	
		
 

			




	
	
		
 
@@ -48,5 +68,5 @@ def populationAge():

			




	
	
	
		
 

			




	
	
	
		
 

			




	
	
	
		
 
# playerYear("Kotowski Jaroslav",2017)

			




	
	
	
		
 
# yearByCountry(2016)

			




	
	
	
		
 

			




	
	
	
		
 
# yearByCountry(2017)

			




	
	
	
		
 
timeRank(2017,"CZ")

			




        

    



    


    



    



      

      





    
    0 comments (0 inline, 0 general)
    





    


  

  







  


    




    








    
        Server instance: Sirius-560
    
    
        This site is powered by
            Kallithea 0.7.0,
        which is
        © 2010–2023 by various authors & licensed under GPLv3.
            – Support