41615


あるゲームのスコア履歴テーブルがあります。これは33万レコードあります。  
日付、プレイヤー、スコアの3カラムあり、`(日付, プレイヤー)`でユニークとなります。  
このとき、ある日付・プレイヤーの直前の試合のスコアをJOINしたいのですが、  
下記のSQLではパフォーマンスが悪く、disk fullで停止してしまいました。  
RDBMSはsqliteです。

SQLを書き直す、適切にINDEXを張るなどの改善方法をお聞きしたいです。

### スコアテーブルの具体例

score_historyテーブル:

[code]

    game_date |player  |score
    -------------------------
    2018-01-01|player1 |2
    2018-01-01|player2 |3
    2018-01-02|player1 |4
    2018-01-03|player1 |5
    
[/code]

期待する結果:

[code]

    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
    
[/code]

実装したSQL:

[code]

    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
    
[/code]

ちなみに、少ないレコード数で実行したところ、上記のSQLで期待する結果は得られました。

どうぞよろしくお願いいたします。


CC BY-SA 3.0
2018-02-09T12:26:10.003
41613
2018-02-10T03:20:32.193
2018-02-10T03:20:32.193
18920
18920
question
sql sqlite
ある試合の直前の点数をJOINするには？（パフォーマンス良く）


## INDEXを張る

`player`と`game_date`でユニークとのことなので順当にインデックスを張ってみるのはどうでしょうか。

[code]

    CREATE TABLE score_history (
        game_date INTEGER NOT NULL,
        player TEXT NOT NULL,
        score INTEGER NOT NULL,
        PRIMARY KEY (player ASC, game_date DESC)
    );
    
[/code]

なお、`player`ごとの`game_date`（前日）のようにアクセスパターンに合わせたインデックスを作成すると効率的です。

## CLUSTERED INDEXを張る

`ROWID`を使わないのであればインデックスを元にテーブルを構成するクラスター化インデックスというものもあります。

[code]

    CREATE TABLE score_history (
        game_date INTEGER NOT NULL,
        player TEXT NOT NULL,
        score INTEGER NOT NULL,
        PRIMARY KEY (player ASC, game_date DESC)
    ) WITHOUT ROWID;
    
[/code]

## CORRELATED SUBQUERYを使う

`s1 left outer join s2`ではdisk
fullとのことなので、効率は悪くなりますが相関サブクエリを使うことで１行ずつ処理するのはどうでしょうか。

[code]

    SELECT
        s1.game_date,
        s1.player,
        s1.score,
        (SELECT s2.score
         FROM score_history s2
         WHERE s1.player = s2.player and s2.game_date < s1.game_date
         ORDER BY s2.game_date
         LIMIT 1) prevScore
    FROM score_history s1;
    
[/code]

何かしらヒントになればと。


CC BY-SA 3.0
2018-02-09T14:36:06.843
41615
2018-02-09T14:36:06.843
4236
41613
answer