group by してからランダムにひとつずつ抽出する、PostgreSQL & MySQL

ある問題を考えているときに、とても学びといいますか気づきがあったので書きます。

(※) パフォーマンスについては、しらべていないので、使用する際は自己責任でお願いします。
(※) 間違っている、もっと良い方法があるなど、ご指摘いただけますと幸いです。

問題

mysql> select * from users;
+----+------+------+
| id | name | team |
+----+------+------+
|  1 | taro |    1 |
|  2 | yuki |    1 |
|  3 | kent |    1 |
|  4 | hoge |    2 |
|  5 | piyo |    2 |
|  6 | fuga |    2 |
|  7 | aaaa |    3 |
|  8 | bbbb |    3 |
+----+------+------+

チームからランダムに一人を選出するクエリを教えてください。

クエリを叩くたびに、ランダムに入れ替わるようにお願いします。

クエリイメージ

hoge=# -- query for random choice.
 id | name | team
----+------+------
  7 | yuki |    1
  9 | hoge |    2
 12 | aaaa |    3
(3 rows)

hoge=# -- query for random choice.
 id | name | team
----+------+------
  6 | taro |    1
  9 | hoge |    2
 12 | aaaa |    3

解答

PostgreSQL と MySQL では方言の違いがありますので、解答も異なります。

PostgreSQL では、distinct on が使えるので、比較的簡単です。

MySQL は難しすぎて、結局 stackoverflow さんに教えていただきたました。そして、 このクエリの書き方に、とても学びがありました。

PostgreSQL

PostgreSQL解答

hoge=# select distinct on (team) * from users order by team, random();
 id | name | team
----+------+------
  7 | yuki |    1
  9 | hoge |    2
 12 | aaaa |    3

order by の team はなくてもイケそうな気がしますが、無いとだめです。

MySQL

MySQL解答

mysql> select u1.team, (
    ->   select u2.name
    ->   from users u2
    ->   where u2.team = u1.team
    ->   order by rand()
    ->   limit 1
    -> ) as name
    -> from users u1
    -> group by u1.team
    -> ;
+------+------+
| team | name |
+------+------+
|    1 | taro |
|    2 | hoge |
|    3 | aaaa |
+------+------+

ちょっと長いので、クエリだけ書いておきます。

クエリ

select u1.team, (
  select u2.name
  from users u2
  where u2.team = u1.team
  order by rand()
  limit 1
) as name
from users u1
group by u1.team
;

(※) これは PostgreSQL でも動きます。

学んだこと

僕がこれまで使ってこなかったことと知らなかったことです。

いままでつかってこなかったこと

• select 句での、相関サブクエリ

知らなかったこと

• select 句で相関サブクエリをすると、group by 句で指定していないカラムも取得できる。（相関を持たせるカラムは group 指定しているカラムと一致している必要があります。）

ふつう、下のように

mysql> select team, name
    -> from users
    -> group by team
    -> ;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hoge.users.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

group で指定してないカラムを書くと、エラーになります。でも、解答のように相関サブクエリだと通りました。すごいです。

追記:
コメントで捕捉説明をいただきました！ご確認ください。

以上。ありがとうございました。