Re: [Bug-gnubg] LAR, FIBS and MWC

[mailinglists]

Zitat von Massimiliano Maini <address@hidden>:

> Use subqueries (not sure if they are supported everywhere): have 2 queries
> that creates
> one table each, then a third query that does a join (on session_id?) of
> the two tables
> obtained calling the two other queries. Does this help ?
>
> MaX.

I finally got it (with my SQL pocket guide and a little help from a friend):

STATS_SEARCH=" select s.session_id as No
        , s.player_id0 as Player
        , round(m1.snowie_error_rate_per_move*1000,2) as Snowie
        , round(m1.error_based_fibs_rating,1) as Fibs
        , s.player_id1 as Opp
        , p1.name as Name
        , round(m2.snowie_error_rate_per_move*1000,2) as Snowie
        , round(m2.error_based_fibs_rating,1) as Fibs_Opp
        , round(50+m1.luck_adjusted_result*100,2) as LAR
        , s.length as Length
        , round(m1.actual_result+0.5) as Result
 from session as s
 join matchstat as m1
        on m1.session_id = s.session_id
        and m1.player_id = s.player_id0
 join matchstat as m2
        on m2.session_id = s.session_id
        and m2.player_id = s.player_id1
 join player as p2
        on p2.player_id = s.player_id0
        and p1.player_id = s.player_id1
 join player as p1
        on p1.player_id = s.player_id1
        and p2.player_id = s.player_id0
 where player_id0 = '$NAME_ID_RESULT'
union
 select s.session_id
        , s.player_id1 as Player
        , round(m1.snowie_error_rate_per_move*1000,2) as Snowie
        , round(m1.error_based_fibs_rating,1) as Fibs
        , s.player_id0 as Opp
        , p2.name as name
        , round(m2.snowie_error_rate_per_move*1000,2) as Snowie
        , round(m2.error_based_fibs_rating,1) Fibs_Opp
        , round(50+m1.luck_adjusted_result*100,2) as LAR
        , s.length as Length
        , round(m1.actual_result+0.5) as Result
 from session  as s
 join matchstat as m2
        on m2.session_id = s.session_id
        and m2.player_id = s.player_id0
 join matchstat as m1
        on m1.session_id = s.session_id
        and m1.player_id = s.player_id1
 join player as p2
        on p2.player_id = s.player_id0
        and p1.player_id = s.player_id1
 join player as p1
        on p1.player_id = s.player_id1
        and p2.player_id = s.player_id0
 where player_id1 = '$NAME_ID_RESULT' LIMIT 10;"


produces something like this:


#: query_player.sh itsme

No  Player Snowie Fibs    Opp  Name  Snowie Fibs_Opp  LAR   Length  Result
--- ------ ------ ----    ---  ----  ------ --------  ---   ------  ------
417  2     0.51   2011.3  322  xyz   10.68  1316.9    72.98  1      1.0
418  2     1.59   1881.7  323  abc   15.48  1015.3    87.58  1      0.0
419  2     2.65   1858.9  210  def   0.0    2050.0    48.96  1      0.0
420  2     6.3    1602.4  324  fgh   5.82   1622.0    43.23  1      0.0
421  2     0.14   2040.3  325  jkl   3.94   1707.7    64.42  1      1.0

Result itsme (2): 249.0 win(s) in 422 matches. Snowie error rate: 3.59

Unfortunately sqlite can't handle sqrt, so I still have to put  
everything into a spreadsheet, but this is easy with ".mode csv".

Anyhow, I'm pretty satisfied now with the output. I'm thinking of  
implementing some useful queries into the GUI ...

Ciao

Achim