【SQLite/MySQL】グループごとの上位N件ずつを取得
MySQL でグループごとに上位のN件ずつを取得する方法は以前に書きました。
サーバサイドの MySQL はこれで解決しました。
しかし、スマホアプリ側で似たテーブル構成にしておいて同様のクエリを使いたくてもできません。
そこで、Android や iOS のアプリ開発でよく使われる SQLite でも実現できる方法を調べました。
SQLite の Window 関数について
新しいバージョンの SQLite では Window 関数が使えます。
しかし、この記事を書いている時点の Android / iOS ではまだ使えません。
詳細は以前に書いた下記記事をご覧ください。
【Android / iOS】スマホOSとSQLiteのバージョン対応 - のんびり精進
参考ページ
ここのベストアンサーの方法を参考にしています。
Window 関数も MySQL のユーザ定義関数も使われていません。
SQLite でも使えるはずです。
また、MySQL でももう一つの方法として使えそうです。
SELECT b.BookId, a.AuthorId, a.AuthorName, b.Title FROM Author a join Book b on a.AuthorId = b.AuthorId where (select count(*) from book b2 where b2.bookId <= b.BookId and b2.AuthorId = b.AuthorId ) <= 2;
- 書籍のテーブル（著者のIDも持つ）と著者のテーブルがある
- 著者ごとに書籍ID順で2件ずつを取得する
お断り
SQLite で動作確認済みなのですが、その後に記事を書く時点では諸事情により SQLite で確認しにくかったため、DDL・実行計画などは MySQL のものを掲載しています。
使用するデータ
MySQL の記事で使ったのと同じデータで試します。
※正規化していないのはシンプルな例にするためですので、ご了承ください。
CREATE TABLE students ( id int, class varchar(8), name varchar(16), score int );
INSERT INTO students VALUES (1, 'A組', '佐藤', 95), (2, 'A組', '鈴木', 87), (3, 'A組', '高橋', 80), (4, 'B組', '田中', 100), (5, 'B組', '渡辺', 72), (6, 'B組', '伊藤', 53), (7, NULL, '山本', 99), (8, NULL, '中村', 96), (9, NULL, '小林', 93);
- クラスごとに点数が高い順に2名ずつ取得
- クラスが不明（
classがNULL）な生徒もいる
→ その生徒たちの中からも上位2名を取得
COALESCE() を使う方法
SELECT s.* FROM students AS s WHERE ( SELECT COUNT(*) FROM students AS s2 WHERE COALESCE(s2.class, '') = COALESCE(s.class, '') AND s2.score >= s.score ) <= 2 ORDER BY s.class, s.score DESC;
SQLite でも MySQL でも期待通りの取得ができました。
MySQL の記事のクエリよりシンプルです。
| id | class | name | score |
|---|---|---|---|
| 7 | NULL | 山本 | 99 |
| 8 | NULL | 中村 | 96 |
| 1 | A組 | 佐藤 | 95 |
| 2 | A組 | 鈴木 | 87 |
| 4 | B組 | 田中 | 100 |
| 5 | B組 | 渡辺 | 72 |
参考ページのクエリとの違いは次のとおりです（JOIN の有無は除く）。
- 書籍IDは昇順、点数は降順なので、不等号が逆
・ 参考クエリ：b2.bookId <= b.BookId
・ このクエリ：s2.score >= s.score classがNULLになっている場合を考慮してCOALESCE()を使用
COALESCE() の第2引数を空文字（''）にしましたが、念のために class と同じ文字列型にしただけです。
WHERE 句での比較にしか使っていないので、別の型でも問題ありません。
実行計画
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1 | PRIMARY | s | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | Using where; Using filesort | | 2 | DEPENDENT SUBQUERY | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 33.33 | Using where | +----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
COALESCE() を使わない方法
COALESCE() は効率が悪く思えるので、使わない版も試しました。
SELECT s.* FROM students AS s WHERE ( SELECT COUNT(*) FROM students AS s2 WHERE (s2.class = s.class OR (s2.class IS NULL AND s.class IS NULL)) AND s2.score >= s.score ) <= 2 ORDER BY s.class, s.score DESC;
違いは、WHERE 句の条件のうちの前半だけです。
- COALESCE() 使用版
COALESCE(s2.class, '') = COALESCE(s.class, '')
- COALESCE() 不使用版
・s2.classとs.classが同じ値（NULL以外）
または
・s2.classとs.classがどちらもNULL
(s2.class = s.class OR (s2.class IS NULL AND s.class IS NULL))
実行計画
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1 | PRIMARY | s | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | Using where; Using filesort | | 2 | DEPENDENT SUBQUERY | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 11.11 | Using where | +----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
インデックスの効果
ALTER TABLE students ADD INDEX idx_class_score(class, score);
このように class と score の複合インデックスを設定し、実行計画の変化を見てみました。
COALESCE() 使用版の実行計画
+----+--------------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+ | 1 | PRIMARY | s | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | Using where; Using filesort | | 2 | DEPENDENT SUBQUERY | s2 | NULL | index | NULL | idx_class_score | 40 | NULL | 9 | 33.33 | Using where; Using index | +----+--------------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+
相関サブクエリのほうでインデックスが使われるようになりましたが、フルインデックススキャンです。
rows、filtered などは変化がありません。
インデックスがないより少しだけマシといったところでしょうか。
COALESCE() 不使用版の実行計画
+----+--------------------+-------+------------+-------------+-----------------+-----------------+---------+---------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+-------------+-----------------+-----------------+---------+---------+------+----------+-----------------------------+ | 1 | PRIMARY | s | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | Using where; Using filesort | | 2 | DEPENDENT SUBQUERY | s2 | NULL | ref_or_null | idx_class_score | idx_class_score | 35 | s.class | 6 | 33.33 | Using where; Using index | +----+--------------------+-------+------------+-------------+-----------------+-----------------+---------+---------+------+----------+-----------------------------+
こちらは効果が出ていますが、効率が良いとは言えないと思います。
とは言え、MySQL の Window 関数でも非効率な実行計画になっていたことを考えると、強引にやっている割には良い結果なのかもしれません。
