Случайная запись из таблицы базы данных (T-SQL)
Есть ли краткий способ получить случайную запись из таблицы сервера SQL?
Я хотел бы рандомизировать данные моего модульного теста, поэтому я ищу простой способ выбрать случайный идентификатор из таблицы. На английском языке выбор будет "Выбрать один идентификатор из таблицы, где идентификатор - это случайное число между самым низким идентификатором в таблице и самым высоким идентификатором в таблице".
Я не могу найти способ сделать это без необходимости выполнять запрос, проверить нулевое значение, а затем повторно запустить, если ноль.
Идеи?
5 ответов
Есть ли краткий способ получить случайную запись из таблицы сервера SQL?
да
SELECT TOP 1 * FROM table ORDER BY NEWID()
объяснение
NEWID() генерируется для каждой строки, а затем таблица сортируется по ней. Возвращается первая запись (т.е. запись с "самым низким" GUID).
Заметки
Начиная с четвертой версии GUID генерируются как псевдослучайные числа:
UUID версии 4 предназначен для генерации UUID из действительно случайных или псевдослучайных чисел.
Алгоритм выглядит следующим образом:
- Установите два старших значащих бита (биты 6 и 7) clock_seq_hi_and_reserved в ноль и один, соответственно.
- Установите четыре старших значащих бита (биты с 12 по 15) поля time_hi_and_version на 4-битный номер версии из Раздела 4.1.3.
- Установите все остальные биты в случайно (или псевдослучайно) выбранные значения.
- Пространство имен URN универсального уникального идентификатора (UUID) - RFC 4122
Альтернатива
SELECT TOP 1 * FROM table ORDER BY RAND()не будет работать, как можно подумать.RAND()возвращает одно значение для каждого запроса, поэтому все строки будут иметь одинаковое значение.Несмотря на то, что значения GUID являются псевдослучайными, вам понадобится лучший PRNG для более требовательных приложений.
Типичная производительность составляет менее 10 секунд для примерно 1 000 000 строк - конечно, в зависимости от системы. Обратите внимание, что попасть в индекс невозможно, поэтому производительность будет относительно ограниченной.
На больших столах вы также можете использовать TABLESAMPLE для этого, чтобы избежать сканирования всей таблицы.
SELECT TOP 1 *
FROM YourTable
TABLESAMPLE (1000 ROWS)
ORDER BY NEWID()
ORDER BY NEWID все еще требуется, чтобы избежать просто возврата строк, которые появляются первыми на странице данных.
Используемое число необходимо тщательно выбирать для размера и определения таблицы, и вы можете рассмотреть логику повторных попыток, если строка не возвращается. Математика, стоящая за этим, и почему техника не подходит для небольших столов, обсуждается здесь.
Также попробуйте свой метод, чтобы получить случайный идентификатор между MIN(Id) и MAX(Id), а затем
SELECT TOP 1 * FROM table WHERE Id >= @yourrandomid
Это всегда даст вам один ряд.
Если вы хотите выбрать большие данные, лучший способ, который я знаю, это:
SELECT * FROM Table1
WHERE (ABS(CAST(
(BINARY_CHECKSUM
(keycol1, NEWID())) as int))
% 100) < 10
Источник: MSDN
Я пытался улучшить методы, которые я пробовал, и наткнулся на этот пост. Я понимаю, что это старый, но этот метод не указан. Я создаю и применяю тестовые данные; это показывает метод для "адреса" в SP, вызванном с @st (состояние с двумя символами)
Create Table ##TmpAddress (id Int Identity(1,1), street VarChar(50), city VarChar(50), st VarChar(2), zip VarChar(5))
Insert Into ##TmpAddress(street, city, st, zip)
Select street, city, st, zip
From tbl_Address (NOLOCK)
Where st = @st
-- unseeded RAND() will return the same number when called in rapid succession so
-- here, I seed it with a guaranteed different number each time. @@ROWCOUNT is the count from the most recent table operation.
Set @csr = Ceiling(RAND(convert(varbinary, newid())) * @@ROWCOUNT)
Select street, city, st, Right(('00000' + ltrim(zip)),5) As zip
From ##tmpAddress (NOLOCK)
Where id = @csr
Если вам действительно нужна случайная выборка отдельных строк, измените свой запрос, чтобы отфильтровать строки случайным образом, вместо использования TABLESAMPLE. Например, следующий запрос использует функцию NEWID для возврата примерно одного процента строк таблицы Sales.SalesOrderDetail:
SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float)
/ CAST (0x7fffffff AS int)
Столбец SalesOrderID включен в выражение CHECKSUM, так что NEWID() выполняет оценку один раз для каждой строки для достижения выборки для каждой строки. Выражение CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) оценивается как случайное значение с плавающей запятой от 0 до 1."
Источник: http://technet.microsoft.com/en-us/library/ms189108(v=sql.105).aspx
Это дополнительно объясняется ниже:
Как это работает? Давайте выделим предложение WHERE и объясним его.
Функция КОНТРОЛЬНАЯ СУММА вычисляет контрольную сумму по элементам в списке. Спорный вопрос о том, требуется ли вообще SalesOrderID, поскольку NEWID() - это функция, которая возвращает новый случайный GUID, поэтому умножение случайного числа на константу в любом случае должно приводить к случайному результату. Действительно, исключение SalesOrderID, похоже, не имеет значения. Если вы увлеченный статистик и можете оправдать включение этого, пожалуйста, используйте раздел комментариев ниже и дайте мне знать, почему я ошибаюсь!
Функция КОНТРОЛЬНАЯ СУММА возвращает ПЕРЕМЕННУЮ. Выполнение побитовой операции И с 0x7fffffff, который является эквивалентом (111111111...) в двоичном формате, дает десятичное значение, которое фактически является представлением случайной строки из нулей и единиц. Деление на коэффициент 0x7fffffff эффективно нормализует это десятичное число до числа от 0 до 1. Затем, чтобы решить, заслуживает ли каждая строка включения в окончательный набор результатов, используется порог 1/x (в данном случае 0, 01), где x - процент данных, извлекаемых в качестве выборки.