Как получить случайные n строк для каждой группы с помощью MySQL

Один из моих коллег спросил меня, можно ли получить 2 случайные строки из сгруппированной таблицы, используя всего один запрос. Я принял этот вопрос как вызов. После нескольких попыток я смог закончить запрос.

В следующем примере мы используем две таблицы, определенные следующим образом.

Предположим, у нас есть структура таблицы, как показано ниже.

+----+-------+----------+
| id | name  | group_id |
+----+-------+----------+
|  1 | Alice |        1 |
|  2 | Bob   |        2 |
|  3 | Mark  |        2 |
|  4 | Fred  |        1 |
|  5 | John  |        1 |
|  6 | Jack  |        2 |
|  7 | Amy   |        2 |
|  8 | Ozgur |        1 |
+----+-------+----------+

Запрос, который мы напишем, должен каждый раз получать случайные имена из группы 1, группы 2. Первые 2 результата этого запроса показаны ниже в качестве примера.

#1
+----+-------+-----------+
| id | name  | group_id  |
+----+-------+-----------+
| 5  | John  | 1         |
| 8  | Ozgur | 1         |
| 6  | Jack  | 2         |
| 3  | Mark  | 2         |
+----+-------+-----------+
#2
+----+-------+-----------+
| id | name  | group_id  |
+----+-------+-----------+
| 4  | Fred  | 1         |
| 1  | Alice | 1         |
| 3  | Mark  | 2         |
| 7  | Amy   | 2         |
+----+-------+-----------+

Сначала я пошагово напишу sql-запрос, а затем попытаюсь его объяснить.

Моя основная идея заключалась в сортировке записей по group_id, но в случайном порядке. Чтобы реализовать эту идею, я создал случайный ранг с помощью group_id + ‘-’ + случайное число и назвал его rand_rank в подзапросе. Я использую hypen, поэтому ранг rand становится строковым, а сортировка всегда упорядочивается по group_id.

select 
      id,
      name, 
      group_id, 
      CONCAT(group_id, '-', round(rand() * 100)) as rand_rank
from names 
order by rand_rank

Вот результат запроса. Каждый раз результаты будут разными из-за rand_rank.

+----+-------+-----------+-----------+
| id | name  | group_id  | rand_rank |
+----+-------+-----------+-----------+
| 4  | Fred  | 1         | 1-15      |
| 1  | Alice | 1         | 1-20      |
| 8  | Ozgur | 1         | 1-31      |
| 5  | John  | 1         | 1-73      |
| 6  | Jack  | 2         | 2-19      |
| 3  | Bob   | 2         | 2-19      |
| 3  | Mark  | 2         | 2-34      |
| 7  | Amy   | 2         | 2-78      |
+----+-------+-----------+------------

Теперь я могу присвоить каждой записи новый рейтинг.

select   
   id,
   name,
   group_id,   
   @group_rank := IF(@current_group=group_id, @group_rank + 1, 1) as group_rank,
   @current_group := group_id
from (
   select 
     id, 
     name, 
     group_id, 
     CONCAT(group_id, '-', round(rand() * 100)) as rand_rank
   from names order by rand_rank
) tmp

И, наконец, достаточно использовать условие where, применяя вновь созданный group_rank с количеством записей, которые нужно отобразить.

select id, name, group_id 
from ( 
  select   
   id,
   name,
   group_id,   
   @group_rank := IF(@current_group=group_id, @group_rank + 1, 1) as  group_rank,
   @current_group := group_id
from (
     select 
       id, 
       name, 
       group_id, 
       CONCAT(group_id, '-', round(rand() * 100)) as rand_rank
     from names order by rand_rank
   ) tmp
) ranked
where group_rank <= 2 order by group_id;

Я пробовал этот запрос с 500 группами x 50000 именами и не обнаружил никаких проблем с производительностью. Хотя это может создать некоторые проблемы с производительностью при работе с большими таблицами.

Как получить случайные n строк для каждой группы с помощью MySQL

Вопросы по теме