ある試合の直前の点数をJOINするには?(パフォーマンス良く)
あるゲームのスコア履歴テーブルがあります。これは33万レコードあります。
日付、プレイヤー、スコアの3カラムあり、(日付, プレイヤー)
でユニークとなります。
このとき、ある日付・プレイヤーの直前の試合のスコアをJOINしたいのですが、
下記のSQLではパフォーマンスが悪く、disk fullで停止してしまいました。
RDBMSはsqliteです。
SQLを書き直す、適切にINDEXを張るなどの改善方法をお聞きしたいです。
スコアテーブルの具体例
score_historyテーブル:
game_date |player |score
-------------------------
2018-01-01|player1 |2
2018-01-01|player2 |3
2018-01-02|player1 |4
2018-01-03|player1 |5
期待する結果:
game_date |player |score |prev_score
------------------------------------
2018-01-01|player1 |2 |NULL
2018-01-01|player2 |3 |NULL
2018-01-02|player1 |4 |2
2018-01-03|player1 |5 |4
実装したSQL:
select s1.game_date, s1.player, s1.score, s2.score prevScore, max(s2.game_date) dummy
from score_history s1
left outer join score_history s2
on s1.player = s2.player and s2.game_date < s1.game_date
group by s1.game_date, s1.player
ちなみに、少ないレコード数で実行したところ、上記のSQLで期待する結果は得られました。
どうぞよろしくお願いいたします。