Files
@ 9c927dc379bc
Branch filter:
Location: EGD-mine/queries.py - annotation
9c927dc379bc
3.2 KiB
text/x-python
populationAge query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 | c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 d72a89609095 d72a89609095 d72a89609095 d72a89609095 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 d72a89609095 d72a89609095 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 d72a89609095 d72a89609095 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 c41a90c1ee53 d72a89609095 d72a89609095 d72a89609095 d72a89609095 d72a89609095 d72a89609095 d72a89609095 d72a89609095 d72a89609095 d72a89609095 d72a89609095 d72a89609095 d72a89609095 d72a89609095 d72a89609095 d72a89609095 6127b170f21c 6127b170f21c 6127b170f21c 6127b170f21c 6127b170f21c 6127b170f21c 6127b170f21c 6127b170f21c 6127b170f21c 6127b170f21c 6127b170f21c 6127b170f21c 6127b170f21c 6127b170f21c 6127b170f21c c41a90c1ee53 c41a90c1ee53 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc 9c927dc379bc c41a90c1ee53 c41a90c1ee53 6127b170f21c 6127b170f21c 6127b170f21c 6127b170f21c c41a90c1ee53 d72a89609095 6127b170f21c 6127b170f21c 9c927dc379bc | 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
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):
date1=yearStart(year)
date2=yearStart(year+1)
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
where datum>=date(?) and datum<date(?)
group by pin
)
group by country_code
order by country_code asc;
"""
res=cursor.execute(query,(date1,date2))
print("kód země\tpočet aktivních hráčů")
for rec in res:
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(year,country):
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):
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"""
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():
pass
# playerYear("Kotowski Jaroslav",2017)
# yearByCountry(2017)
# timeRank(2017,"CZ")
# populationStrength(2017,"CZ")
# populationAge(2017,"CZ")
|