Zitat von Massimiliano Maini <[EMAIL PROTECTED]>:
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
_______________________________________________
Bug-gnubg mailing list
[email protected]
http://lists.gnu.org/mailman/listinfo/bug-gnubg
