sqlite3 で SELECT 結果の行番号を取得する
SQL を使っていると、SELECT した結果セットの各行について、結果セット中での行番号を知りたい、という場面にたまに遭遇します。こういうやつです。SELECT * FROM books ORDER BY price; id title price ---------- -------------- ---------- 3 Art with Basis 600 #行番号: 1 4 Book List 990 #行番号: 2 1 Civilizations 1000 #行番号: 3 2 Dream Post 1200 #行番号: 4 5 El sant magina 1500 #行番号: 5一般的な一覧系の処理では先頭から N 件とりだすことが多いので、OFFSET で取り出した件数を指定すれば行番号を意識せずに続きをとることができます。
ただ、ID からその行の結果セット中での位置を知ろうとすると、やはり行番号が欲しくなってくるのです。結果セットがソートされていたり、ソートされているカラムがユニークでなかったりするとかなり面倒なことになります。特定の ID の行の前後 3 行ずつを取りたい、というような時に困ってしまうわけです。
結果セット中での行番号を取得するために、Oracle では ROWNUM 擬似列や ROW_NUMBER() 関数が用意されているそうです。
また、MySQL ではユーザ定義変数を使うことで、SELECT 時に行番号を計算し、出力することができます。
SET @rownum := 0; SELECT *, (@rownum := @rownum + 1) FROM books;
しかし sqlite3 では ROW_NUMBER() 的な機能もユーザー定義変数的な機能も実装されていないので、これらの方針をとることはできません。ネットを見てみると以下のようなクエリで計算している例がありますが、境界値として WHERE 句で使っているカラム（ここでは id カラム）がユニークでない場合、境界値として機能しないため、厳密な行番号はとれません。
SELECT id, title, price, (SELECT COUNT(*) FROM books b2 WHERE b1.id >= b2.id) AS rownum FROM books b1 ORDER BY rownum; id title price rownum ---------- -------------- ---------- ---------- 3 Art with Basis 600 1 4 Book List 990 2 1 Civilizations 1000 3 2 Dream Post 1200 4 5 El sant magina 1500 5※ ORDER BY rownum は見やすさ重視のためにつけたもので、必須ではありません。
もう 1 点、この方法では 1 行毎にサブクエリで行番号をカウントするため、参照性能に問題があります。MySQL では悪名高き DEPENDENT SUBQUERY にあたるためです。
あらかじめ行番号を数えておく
sqlite3 で結果セット中での行番号をとるためにたどり着いた方法は、あらかじめ行番号を計算してテーブルにいれておくことでした。高尚な解答というわけではなく、やっぱりそれしかないかな、という感じですね。プログラム側でソートした結果セットの行を数えて DB に行番号を書き込んでおくほうが DB には優しいでしょうが、そもそも sqlite3 だと別プロセスで動かして利用することはまずないでしょうから、ここでは DB にやらせてしまうことにします。
行番号を数え方ですが、sqlite3 にはユーザー定義変数的なものはありませんが、AUTO INCREMENT はあるので、これ（と同等のもの）を使おうと思います。別テーブルにソートした結果セットを一度いれておき、その時に行作成順に番号をふらせてしまいます。
CREATE TABLE book_index (book_id INTEGER NOT NULL); DELETE FROM book_index; INSERT INTO book_index (book_id) SELECT id FROM books ORDER BY price ASC;book_index テーブルに id INTEGER PRIMARY KEY AUTOINCREMENT カラムがあると想定するとわかりやすいと思います。ソートした結果セットをそのまま book_index テーブルに INSERT すれば、その順番通りに id 値に連番がふられることになります。
ただ、sqlite3 では暗黙のカラムとして rowid というカラムがあり、これが id INTEGER PRIMARY KEY AUTOINCREMENT カラムと同等の働きをしてくれるため、AUTOINCREMENT なカラムを追加する必要はありません。
book_index テーブルを SELECT するとこんな感じになります。
SELECT rowid, * FROM book_index; rowid book_id ---------- ---------- 1 3 2 4 3 1 4 2 5 5
このテーブルを books テーブルに JOIN すれば、好きな books.id 値から行番号をとれることになります。
SELECT b.*, i.rowid FROM books b INNER JOIN book_index i ON i.book_id = b.id ORDER BY i.ROWID ASC; id title price rowid ---------- -------------- ---------- ---------- 3 Art with Basis 600 1 4 Book List 990 2 1 Civilizations 1000 3 2 Dream Post 1200 4 5 El sant magina 1500 5
あらかじめ行番号を数えておく場合の注意
1 点、この book_index テーブルの行番号は数えた時点での行番号であることに注意が必要です。books テーブルのレコードが増減したり、ソートするためのカラム（この例では price）の値が更新された場合には数え直しが必要になります。一見これは手間のようにも見えますが、逆にいうと参照時には数えなくてよい、ということになります。
計算コストはソート結果が変わるタイミングでのみ払えばよく、この特徴は更新頻度より参照頻度が多いプログラムほど、有利に働くと思います。
しかし弱点もあります。ソートの種類毎に行番号用のテーブルを用意しておかなければならないことです。1 つくらいなら計算しておくこともできるでしょうが、3 つも 4 つもソートの種類が必要になると、その数だけ行番号をいれておくテーブルを用意しなければなりません。
そもそも行番号を必要とすること自体がレアケースなので、あまり困りそうなことではないのですが、その場合は……さすがに諦めて別のデータベースを使うほうがよいかもしれません。
WEB 技術 コメント (0) 2017/02/01 19:45:13