まえがき
「MySQL で ORDER BY してから GROUP BY するっぽいことをする - はてダ」のようなことをやりたい。
検証環境に MariaDB が入っていたためタイトルには MariaDB と書いたが、 MySQL でもたぶん同じである。
MariaDB [(none)]> select version(); +---------------------+ | version() | +---------------------+ | 10.0.16-MariaDB-log | +---------------------+
本題
さて、
MariaDB [test]> SELECT * FROM hoge; +------+------+------+ | id | gid | time | +------+------+------+ | 1 | 1 | 1 | | 2 | 1 | 3 | | 3 | 1 | 2 | | 4 | 2 | 3 | | 5 | 2 | 2 | | 6 | 2 | 1 | | 7 | 3 | 3 | | 8 | 3 | 1 | | 9 | 3 | 2 | | 10 | 3 | 1 | +------+------+------+
というテーブル(id は一意、time は gid に対して一意ではない)から
+------+------+------+ | id | gid | time | +------+------+------+ | 1 | 1 | 1 | | 6 | 2 | 1 | | 8 | 3 | 1 | +------+------+------+
というのを取り出したい。つまり、gid が同じ行のうち time が最小の行を取り出したい。
任意の gid に対して time が最小の行が複数あるときは、いずれか一行、どれでもよいものとする。
以前の MySQL ではサブクエリで ORDER BY してから GROUP BY、すなわち
SELECT * FROM (SELECT * FROM hoge ORDER BY time) GROUP BY gid;
とするとできたのだけど、どうやらできなくなっているようだ。
冒頭で紹介した記事の方法で確かにできるのだけど、 JOIN がどうも遅いようなので、少しでも速くしたい。 そのためには右側の行数を減らしてやればよいと考えた。 そこで JOIN する前にあらかじめ GROUP BY して time で選択してやろう。
MariaDB [test]> SELECT gid, MIN(time) time FROM hoge GROUP BY gid; +------+------+ | gid | time | +------+------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +------+------+ MariaDB [test]> SELECT * FROM hoge l LEFT JOIN (SELECT gid, MIN(time) time FROM hoge GROUP BY gid) r ON l.gid=r.gid AND l.time=r.time; +------+------+------+------+------+ | id | gid | time | gid | time | +------+------+------+------+------+ | 1 | 1 | 1 | 1 | 1 | | 2 | 1 | 3 | NULL | NULL | | 3 | 1 | 2 | NULL | NULL | | 4 | 2 | 3 | NULL | NULL | | 5 | 2 | 2 | NULL | NULL | | 6 | 2 | 1 | 2 | 1 | | 7 | 3 | 3 | NULL | NULL | | 8 | 3 | 1 | 3 | 1 | | 9 | 3 | 2 | NULL | NULL | | 10 | 3 | 1 | 3 | 1 | +------+------+------+------+------+ MariaDB [test]> SELECT * FROM hoge l INNER JOIN (SELECT gid, MIN(time) time FROM hoge GROUP BY gid) r ON l.gid=r.gid AND l.time=r.time; +------+------+------+------+------+ | id | gid | time | gid | time | +------+------+------+------+------+ | 1 | 1 | 1 | 1 | 1 | | 6 | 2 | 1 | 2 | 1 | | 8 | 3 | 1 | 3 | 1 | | 10 | 3 | 1 | 3 | 1 | +------+------+------+------+------+
必要なら GROUP BY gid しよう。
SELECT MIN(l.id) id, l.gid, l.time FROM hoge l INNER JOIN (SELECT gid, MIN(time) time FROM hoge GROUP BY gid) r ON l.gid=r.gid AND l.time=r.time GROUP BY gid; +------+------+------+ | id | gid | time | +------+------+------+ | 1 | 1 | 1 | | 6 | 2 | 1 | | 8 | 3 | 1 | +------+------+------+
あとがき
SQL まじわからんしやめたい。
— wowo (@crckyl) 2015, 2月 1