Aug 04
In the first part I showed some general information about the sqlite database in GNU Backgammon. We now start some useful queries:
sqlite> select a.session_id as No, b.length as Length, a.actual_result+0.5 as Result, round(a.luck_adjusted_result*100,2) as Luck_Adjusted, round(a.snowie_error_rate_per_move*1000,2) as Snowie, date(b.added) as Date from matchstat a, session b where a.session_id = b.session_id and a.player_id = 2 group by a.session_id limit 10;
No Length Result Luck_Adjusted Snowie Date ------ ------- ---------- ------------- ---------- ------------ 1 1 1.0 29.68 2.99 2008-08-04 2 1 0.0 1.06 3.67 2008-08-04 3 1 0.0 -12.52 0.58 2008-08-04 4 3 1.0 6.9 3.27 2008-08-04 5 3 0.0 2.47 0.54 2008-08-04 6 3 1.0 18.69 6.35 2008-08-04 7 3 0.0 9.16 1.79 2008-08-04 8 1 1.0 1.58 2.56 2008-08-04 9 1 0.0 -14.08 8.35 2008-08-04 10 1 1.0 6.21 0.89 2008-08-04
Explanations:
| round(…*100,2) | = | change output into percentages, round up and restrict number to 2 digits after decimal point |
| round(…*1000,2) | = | round up and restrict number to 2 digits after decimal point |
| date(…) | = | print only the date, not hours, minutes and seconds |
| limit 10 | = | restrict the output to the first ten lines |
A more sophisticated query with nicer output could be the following:
sqlite>select a.session_id as No, b.length as Length, round(a.actual_result+0.5) as Result, round(50+a.luck_adjusted_result*100,2) as Luck_Adjusted, round(a.error_based_fibs_rating,1) as FIBS, round(a.snowie_error_rate_per_move*1000,2) as Snowie, date(b.added) as Date from matchstat a, session b where a.session_id = b.session_id and a.player_id = 2 group by a.session_id;
No Length Result Luck_Adjusted FIBS Snowie Date ------- -------- -------- ------------- ------- -------- ---------- 267 1 0.0 53.53 1947.8 0.52 2008-08-04 268 1 1.0 44.78 1691.9 4.01 2008-08-04 269 1 1.0 37.92 1491.0 6.2 2008-08-04 270 1 1.0 49.63 1726.4 4.45 2008-08-04 271 1 1.0 58.7 1720.4 4.34 2008-08-04 272 3 1.0 36.64 1883.4 4.01 2008-08-04 273 1 1.0 67.96 1878.3 2.16 2008-08-04 274 1 1.0 57.63 1888.9 1.92 2008-08-04 275 1 1.0 61.18 1861.5 2.61 2008-08-04 276 3 1.0 57.31 1883.9 4.54 2008-08-04
