[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Bug-gnubg] gnubg database interface
From: |
Joern Thyssen |
Subject: |
Re: [Bug-gnubg] gnubg database interface |
Date: |
Mon, 5 Apr 2004 07:30:08 +0000 |
User-agent: |
Mutt/1.4.2.1i |
On Mon, Apr 05, 2004 at 05:32:03AM +0000, Joern Thyssen wrote
> On Sun, Apr 04, 2004 at 08:02:46PM +0000, Joern Thyssen wrote
> >
> > I've more or less followed the schema you posted back then, although I'm
> > only storing matches.
> >
> > I have collection of 3000+ matches played on various online servers, and
> > I've plans to import those; that should hopefully reveal all the stupid
> > bugs in my python code.
>
> I've already found and fixed one.
It seems to work fine now.
Here is a few examples of what you can do:
How many matches in the database:
select count(*) from gnubg.match;
count
-------
992
select person_id, name from gnubg.person where name = 'jth';
person_id | name
-----------+-------------------------------------------------------
1 | jth
How many different opponent did I this year, and how matches did I
play against each of them? (person_id = 1 is yours truly)
select substr(name,1,20), count(*)
from (
select person_id1
from gnubg.match
where person_id0 = 1
and date >= '2004-01-01'
union all
select person_id0
from gnubg.match
where person_id1 = 1
and date >= '2004-01-01'
) as x
inner join gnubg.person p
on p.person_id = x.person_id1
group by name order by 2 desc;
substr | count
----------------------+-------
Donald | 41
Johnny | 31
Peter | 19
Jim | 18
Reginald | 18
...
What is Donald's average error rate per move broken down on match
lengths:
select
length,
round(cast(1000.0*avg(overall_error_per_move_normalised) as numeric),2)
from (
select overall_error_per_move_normalised, length
from gnubg.match m
inner join gnubg.matchstat ms
on m.person_id0 = ms.person_id
and m.match_id = ms.match_id
inner join gnubg.person p
on m.person_id0 = p.person_id
where p.name = 'Donald'
union all
select overall_error_per_move_normalised, length
from gnubg.match m
inner join gnubg.matchstat ms
on m.person_id1 = ms.person_id
and m.match_id = ms.match_id
inner join gnubg.person p
on m.person_id1 = p.person_id
where p.name = 'Donald'
) as x
group by length
order by length
;
length | round
--------+-------
1 | 2.23
3 | 6.32
5 | 3.64
Jørn
pgpdbWfySY4w_.pgp
Description: PGP signature