Рандомизация набора строк

Задача
Вы хотите рандомизировать набор строк или значений.

Решение
Используйте инструкцию ORDER BY RAND().

Обсуждение
Функцию RAND() MySQL можно использовать для внесения элемента случайности в порядок возвращаемых запросом строк результирующего множества. Как это ни парадоксально, расположение в случайном порядке обеспечивается путем добавления в запрос инструкции ORDER BY. Метод имеет сходство с методом, применяемым в табличной процедуре рандомизации. Предположим, что в электронной таблице есть такой набор значений:

Patrick
Penelope
Pertinax
Polly

Чтобы расположить их в произвольном порядке, сначала добавим еще один столбец, содержащий произвольно выбранные числа:

Patrick .73
Penelope .37
Pertinax .16
Polly .48

Затем отсортируем строки по значениям случайных чисел:

Pertinax .16
Penelope .37
Polly .48
Patrick .73

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


Чтобы повторно рандомизировать множество, выберите другой набор случайных чисел и еще раз отсортируйте строки.

В MySQL аналогичный эффект достигается путем сопоставления набора случайных чисел результатам запроса и сортировки результата по этим числам. Начиная с версии MySQL 3.23.2 операцию выполняет инструкция ORDER BY RAND():

mysql> SELECT name FROM t ORDER BY RAND();

+----------+
| name |
+----------+
| Pertinax |
| Penelope |
| Patrick |
| Polly |
+----------+

mysql> SELECT name FROM t ORDER BY RAND();

+----------+
| name |
+----------+
| Patrick |
| Pertinax |
| Penelope |
| Polly |
+----------+

Для MySQL версий более ранних, чем 3.23.2, инструкция ORDER BY не может ссылаться на выражения, поэтому в ней нельзя использовать RAND(). Чтобы разрешить проблему, добавьте в список столбцов вывода столбец случайных чисел, присвойте ему псевдоним и ссылайтесь на псевдоним при сортировке:

mysql> SELECT name, name*0+RAND() AS rand_num FROM t ORDER BY rand_num;

+----------+----------------------+
| name | rand_num |
+----------+----------------------+
| Penelope | 0.372227413926485 |
| Patrick | 0.431537678867148 |
| Pertinax | 0.566524063764628 |
| Polly | 0.715938107777329 |
+----------+----------------------+

Обратите внимание, что выражение для столбца случайных чисел выглядит как name*0+RAND(), а не просто как RAND().


Если попытаться использовать второе, то оптимизатор версий MySQL до 3.23 заметит, что столбец содержит только функцию, предположит, что функция для каждой строки возвращает постоянное значение и с целью оптимизации исключит инструкцию ORDER BY.

В результате сортировка не будет выполняться. Необходимо ввести оптимизатор в заблуждение, добавив в выражение дополнительные члены, не изменяющие его значение, но делающие столбец похожим на непостоянный.

В представленном выше запросе приводится простой способ сделать это: берем имя любого столбца, умножаем на ноль и прибавляем результат к RAND(). Конечно, использование name в арифметическом выражении может показаться странным, так как значения этого столбца не числовые. Но это неважно; MySQL видит оператор умножения * и выполняет преобразование строки в число для значений name непосредственно перед умножением. Важно то, что результатом этого умножения является ноль, то есть выражение name*0+RAND() имеет то же значение, что и RAND().

Рандомизацию набора строк можно применять в любых сценариях, использующих выбор без возвращения в множество (выбор каждого элемента множества до тех пор, пока не останется ни одного элемента).


Рассмотрим несколько примеров:

• Определение начального порядка участников некоторого мероприятия. Перечислите участников в таблице и выберите в случайном порядке.

• Назначение беговых дорожек или входов на скаковом поле для участников забега. Приведите список дорожек в таблице и выберите в случайном порядке.

• Выбор порядка представления вопросов викторины.

• Перемешивание колоды карт. Представьте каждую карту строкой таблицы и тасуйте колоду, выбирайте строки в случайном порядке. Сдавайте их по одной до тех пор, пока не закончится колода.

Чтобы использовать последний пример в качестве иллюстрации, давайте реализуем алгоритм перемешивания колоды карт. Перемешивание и сдача карт – это рандомизация и выбор без возвращения в множество: каждая карта выбирается единожды, прежде чем какая-то будет выбрана повторно; когда колода заканчивается, она заново перемешивается с целью повторной рандомизации для нового порядка сдачи. В программе задачу можно решить с помощью таблицы deck, содержащей 52 строки (набор карт 4 мастей, по 13 карт каждой масти):

• Выберите всю таблицу и сохраните ее в массиве.

• Каждый раз, когда нужна карта, берите следующий элемент массива.

• Когда массив исчерпан, все карты розданы. Заново «перемешайте» таблицу для генерирования нового порядка чисел.

Создание таблицы deck было бы весьма утомительным, если бы пришлось вставлять 52 записи о картах, написав все предложения INSERT вручную. Более простым способом получения содержимого deck будет комбинаторный – генерируем пары масть-достоинство карты. Приведем код PHP, который создает таблицу deck со столбцами face и suit, а затем заполняет ее даннымипри помощи вложенных циклов, формирующих пары для предложений INSERT:

mysql_query ("
CREATE TABLE deck
(
face ENUM('A', 'K', 'Q', 'J', '10', '9', '8',
'7', '6', '5', '4', '3', '2') NOT NULL,
suit ENUM('hearts', 'diamonds', 'clubs', 'spades') NOT NULL
)", $conn_id)
or die ("Cannot issue CREATE TABLE statement\n");
$face_array = array ("A", "K", "Q", "J", "10", "9", "8",
"7", "6", "5", "4", "3", "2");
$suit_array = array ("hearts", "diamonds", "clubs", "spades");
# вставить "карту" в колоду для каждой комбинации масти и достоинства
reset ($face_array);
while (list ($index, $face) = each ($face_array))
{
reset ($suit_array);
while (list ($index2, $suit) = each ($suit_array))
{
mysql_query ("INSERT INTO deck (face,suit) VALUES('$face','$suit')",
$conn_id)
or die ("Cannot insert card into deck\n");
}
}

Для перемешивания карт создаем такое предложение:

SELECT face, suit FROM deck ORDER BY RAND();

Чтобы выполнить его и сохранить результаты в массиве, напишем функцию shuffle_deck(), которая запускает запрос и возвращает результирующие значения в массив (опять-таки на PHP):

function shuffle_deck ($conn_id)
{
$query = "SELECT face, suit FROM deck ORDER BY RAND()";
$result_id = mysql_query ($query, $conn_id)
or die ("Cannot retrieve cards from deck\n");
$card = array ();
while ($obj = mysql_fetch_object ($result_id))
$card[] = $obj; # добавить запись о карте в конец массива $card
mysql_free_result ($result_id);
return ($card);
}

Раздаем карты, храня счетчик, принимающий значения от 0 до 51 и указывающий, какая карта выбирается. Когда счетчик достигает 52, это означает, что колода исчерпана, и необходимо перемешать ее заново.

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Вычисление линейной регрессии и коэффициентов корреляции
Генерация случайных чисел
Групповые описательные статистические показатели
Подсчет отсутствующих значений
Получение описательных статистических показателей

Вернуться в раздел: MySQL / 13. Статистические методы