Re: [Bug-gnubg] gnubg database interface

[Joern Thyssen]

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