Случайная выборка строк из таблицы в SQL Server

В свое время мы использовали случайную выборку записей для формирования списка вопросов теста. Мы делали это на клиенте, используя функции базового языка, генерирующие псевдослучайное число в заданном диапазоне (например, функция RND в Visual Basic).

Однако оказалось, что достаточно просто это можно организовать и на сервере. Причем сделать это можно как аналогичными средствами (с помощью функции RAND в T-SQL) так и на основе типа данных uniqueidentifier, который называется глобальным уникальным идентификатором и имеет вид:

xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx,

где каждое x является шестнадцатеричной цифрой в диапазоне 0–9 или a–f. Например, EEA8BF3A-7633-477A-B8C1-8C60DC9AD20C.

Для решения нашей задачи важно то, что этот уникальный идентификатор может генерироваться автоматически при использовании функции NEWID. Просто напишите в QA (Query Analyzer) или в MS (Management Studio)

SELECT NEWID();

SELECT TOP 1 NEWID() FROM PC;

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

Итак, пусть нам нужно выбрать две произвольные модели из таблицы Product:

Решение 8.8.1

SELECT model
FROM (SELECT TOP 2 model, NEWID() [id]
FROM Product
ORDER BY [id]
) x;

Или более просто

Решение 8.8.2

SELECT TOP 2 model
FROM Product
ORDER BY NEWID();

Выполним этот запрос несколько раз:

Если хотите, можете продолжить.

Теперь вернемся к «традиционному» способу. Функция RAND генерирует псевдослучайное число с плавающей точкой в диапазоне от 0 до 1.

Термин «псевдослучайное» означает, что такое число вычисляется с помощью некоторого арифметического алгоритма. То есть при одинаковых начальных (входных) условиях получаемое число будет одним и тем же. Эти начальные условия могут быть заданы явно с помощью аргумента функции, которым может быть любое число типа tinyint, int или smallint, или неявно. В последнем случае аргумент опускается, в результате чего начальное значение будет выбрано SQL Server.

Попробуем выполнить следующий запрос:

SELECT TOP 1 RAND(), RAND(350)
FROM Product;

Выполним этот запрос в QA. У меня получилось: 0.0485421339242268 и 0.72009490018203537. Можно с уверенностью утверждать, что первое число у вас другое, однако второе может быть тем же самым, так как во втором случае мы задали начальное значение (350).

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

SELECT TOP 2 model
FROM Product
ORDER BY RAND(model);

Получаем первые две модели в порядке возрастания их номеров. Вот где проявилась псевдослучайность. Посмотрим, что за случайные числа мы имеем:

SELECT model, RAND(model) rnd
FROM Product
ORDER BY rnd;

Для краткости приведу их не все:

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

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

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE())
);

Однако в таком виде запрос может быть задействован только в медленно выполняющихся пакетах, чтобы неоднократное выполнение запроса происходило не чаще, чем минимальная единица времени, применяемая в запросе (миллисекунды). Очевидно, что использование этой функции в выборке модели не будет отвечать этому условию. Однако если умножить аргумент на некоторый уникальный идентификатор, то мы можем добиться успеха (это решение было предложено Гершовичем В.И.):

SELECT model, RAND(model*(DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE())
)
FROM Product
ORDER BY model;

Однако тут есть одна проблема, связанная с тем, что аргументом функции RAND является целое число. Поэтому если мы превысим максимально допустимое значение для целого числа (для SQL Server оно составляет 2^31-1 или 2 147 483 647), то получим следующее сообщение об ошибке:

(«Ошибка переполнения при преобразовании выражения к типу данных int».)

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

CASE
WHEN model < 2000
THEN model
ELSE model/10+model % 10
END

Здесь добавление model % 10 (остаток от деления на 10) делается для того, чтобы не потерять значащие цифры; в противном случае мы можем получить одинаковые значения для моделей, номера которых отличаются на единицы.

В окончательном виде решение будет выглядеть так (естественно, сортировку нужно делать по rnd, а не по model, которую мы оставили для наглядности результата).

Решение 8.8.3

SELECT model,
RAND(CASE
WHEN model < 2000
THEN model
ELSE model/10+model % 10
END *
(DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE())
) rnd
FROM Product
ORDER BY model;

А теперь сравните результаты:

Вывод. Для решения рассматриваемой задачи проще и надежней использовать функцию NEWID(), которая гарантирует уникальность значений. Однако эти значения не являются числовыми. Поэтому там, где нужно получить именно число, следует обратить внимание на функцию RAND().