search

ChessAnalysis / chess-analysis

14 stars 3 forks source link

Get informations from database #7

Closed fresnault closed 8 years ago

fresnault commented 9 years ago

From the conversation with Mathieu :

It is important to retrieve information from database.

fresnault commented 9 years ago

See https://github.com/fresnault/chess-analysis/commit/1824a48f3c498475b85b921fb59e1804b7c97741 commit. We retrieve data from database and parse log in order to get move, score, ...

We principally use this file : chess-analysis/src/main/java/stockfish/ParseDatabase.java For now, that generates :

Next tomorrow!

fresnault commented 8 years ago

Now it's possible https://github.com/fresnault/chess-analysis/commit/d0b422c82afbfa89ef8f752b2b75e1f5a930dbfa

That calculates score of FEN and evaluation of Move. We can easily retrieve highest evolutions scores/blunders/fluctuations.

fresnault commented 8 years ago

SQL Request to get :

Evolution curve

SELECT Move.halfMove, FEN.score
FROM Game, Move, FEN
WHERE Game.id = Move.idGame AND FEN.id = Move.idFEN AND Game.id = 4784903 
ORDER BY Move.halfMove ASC
Ply Score
0 26
1 40
2 34
... ...
73 -291

Highest evolutions score

SELECT Move.evaluation, FEN.id
FROM Move, FEN
WHERE Move.idFEN = FEN.id
ORDER BY ABS(Move.evaluation) DESC
Evaluation FEN
-3686 8/8/1k2n3/1p1P4/1N2r2P/p7/2K5/8 w - - 0 61
-1556 1k6/R7/4P3/1p6/1b1PNn1P/p1r2N2/8/3K4 b - - 2 56
1134 1k6/R7/4P3/1p6/1b1PNn1P/p2r1N2/8/3K4 w - - 3 56

Games with highest fluctuations

SELECT Game.id, SUM(ABS(Move.evaluation))
FROM Game, Move
WHERE Game.id = Move.idGame
GROUP BY Game.id
ORDER BY SUM(ABS(Move.evaluation)) DESC
Game Sum Fluctuation
4784910 10991
4784905 2850
4784912 2724
fresnault commented 8 years ago

Evolution score player

(SELECT Game.date, Game.whiteElo AS 'Elo Rating'
FROM Player, Game
WHERE Player.name = 'Balcerak, Jakob' AND Player.id = Game.whiteId
ORDER BY Game.date ASC)
UNION
(SELECT Game.date, Game.blackElo AS 'Elo Rating'
FROM Player, Game
WHERE Player.name = 'Balcerak, Jakob' AND Player.id = Game.blackId
ORDER BY Game.date ASC)
Date Elo Rating
2000-08-03 1900
2000-10-14 2000
2015-08-21 34
fresnault commented 8 years ago

SELECT MIN(totalPlyCount) AS "Minimum", AVG(totalPlyCount) AS "Moyenne", MAX(totalPlyCount) AS "Maximum" FROM Game WHERE movesSAN LIKE '%#%';

Minimum 5 plys
Moves 1. e4 g5 2. d4 f6 3. Qh5# 1-0
FEN rnbqkbnr/ppppp2p/5p2/6pQ/3PP3/8/PPP2PPP/RNB1KBNR b KQkq - 1 3

Average : 74 plys

Maximum 360 plys
Moves 1. e4 e5 2. Nf3 Nc6 3. Bb5 a6 4. Ba4 Nf6 5. O-O Be7 6. Re1 b5 7. Bb3 d6 8. c3 O-O 9. h3 Bb7 10. d4 Re8 11. Nbd2 Bf8 12. a4 h6 13. Bc2 exd4 14. cxd4 Nb4 15. Bb1 c5 16. d5 Nd7 17. Ra3 c4 18. axb5 axb5 19. Nd4 Qb6 20. Nf5 Ne5 21. Rg3 g6 22. Nf3 Ned3 23. Be3 Qd8 24. Bxh6 Qf6 25. Qd2 Nxe1 26. Bxf8 Nxf3+ 27. Rxf3 Kxf8 28. Nh6 Qg7 29. Rxf7+ Qxf7 30. Nxf7 Kxf7 31. Qxb4 Ra1 32. Qxb5 Rxb1+ 33. Kh2 Re7 34. Qb4 Rd7 35. Qd2 Kg8 36. Qc2 Ra1 37. Qxc4 Ra8 38. b4 Rf8 39. f3 Rc8 40. Qd4 Rf7 41. h4 Rcc7 42. b5 Rc5 43. Qb4 Kh7 44. b6 Rd7 45. Qa4 Re7 46. Kg3 Rc8 47. Qb4 Rd8 48. Kf4 Bc8 49. g4 Rf8+ 50. Ke3 Rd8 51. h5 gxh5 52. gxh5 Bb7 53. Qb2 Rf8 54. Qh2 Rf6 55. Qh4 Ref7 56. f4 Rh6 57. Qg5 Ba8 58. Kd3 Bb7 59. Kd4 Rhf6 60. f5 Ba6 61. Kc3 Bb7 62. Kb4 Ba6 63. Ka5 Bb7 64. Kb5 Ba8 65. Qg6+ Kh8 66. Qg3 Bb7 67. Qf4 Rh7 68. Qd2 Rhh6 69. Qg5 Kh7 70. Kc4 Ba6+ 71. Kd4 Bb7 72. Kc3 Bc8 73. Kc2 Ba6 74. Kb2 Bb7 75. Kc1 Ba6 76. Kc2 Bb7 77. Kb2 Ba6 78. Kb3 Bb7 79. Kc2 Ba6 80. Kc1 Kh8 81. Kb1 Kh7 82. Ka2 Bb7 83. Kb3 Kh8 84. Kb4 Kh7 85. Kb5 Kh8 86. Ka4 Kh7 87. Ka5 Kh8 88. Qh4 Rf7 89. Qf4 Kh7 90. Kb5 Rff6 91. Qc1 Rf7 92. Qa3 Rg7 93. Qf3 Rf6 94. Qc3 Rgf7 95. Qd2 Rh6 96. Qg2 Rg7 97. Qh2 Kg8 98. Qh4 Kh7 99. Qh1 Rf7 100. Ka5 Rg7 101. Qh4 Rd7 102. Qg3 Rg7 103. Qf3 Re7 104. Kb5 Rg7 105. Qc3 Rf7 106. Qh3 Rhf6 107. Qe3 Rg7 108. Qc1 Rgf7 109. h6 Rxh6 110. Qd2 Kg7 111. Qg5+ Kh7 112. Kb4 Rhf6 113. Kc4 Ba6+ 114. Kb3 Bc8 115. Kc3 Ba6 116. Kb2 Rh6 117. Kb3 Rg7 118. Qc1 Re7 119. Qf4 Rb7 120. f6 Rf7 121. Qxd6 Rhxf6 122. Qe5 Rxb6+ 123. Kc3 Rh6 124. Kd4 Be2 125. Qe8 Bh5 126. Qd8 Bg4 127. e5 Rd7 128. Qe8 Kg7 129. Qb8 Be6 130. d6 Rh4+ 131. Ke3 Kg6 132. Qf8 Rf7 133. Qd8 Rb4 134. Qe8 Kf5 135. Qh8 Rb3+ 136. Kd4 Kg4 137. Qg8+ Kf3 138. Qa8+ Kg3 139. Qg8+ Kf2 140. Qg5 Rb4+ 141. Kc5 Rff4 142. d7 Bxd7 143. Qh5 Rfc4+ 144. Kd6 Rd4+ 145. Ke7 Bg4 146. Qh2+ Kf3 147. e6 Re4 148. Qh1+ Kf4 149. Qc1+ Kf5 150. Qf1+ Ke5 151. Qa1+ Rbd4 152. Qg1 Bxe6 153. Qg5+ Bf5 154. Qg3+ Kd5+ 155. Kf6 Rf4 156. Qb3+ Kc6 157. Kg5 Bd7 158. Qc2+ Rc4 159. Qg2+ Kc5 160. Qg1+ Kd5 161. Qg2+ Ke6 162. Qg3 Rf8 163. Qh3+ Ke7 164. Qe3+ Kf7 165. Qf2+ Kg8 166. Qa2 Be6 167. Qa1 Rg4+ 168. Kh6 Rc8 169. Qa6 Bd7 170. Qa2+ Rcc4 171. Qa7 Rcd4 172. Qa2+ Kf8 173. Qb3 Ke7 174. Qa3+ Rb4 175. Qe3+ Kf6 176. Qf3+ Rgf4 177. Qc3+ Rbd4 178. Qf3 Rxf3 179. Kh7 Bf5+ 180. Kh8 Rd8# 0-1
FEN 3r3K/8/5k2/5b2/8/5r2/8/8 w - - 4 181