MariaDB で ORDER BY してから GROUP BY

まえがき

「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