Случайная запись из таблицы базы данных (T-SQL)

Ответ 1

Есть ли краткий способ получения случайной записи из таблицы sql-сервера?

Да

SELECT TOP 1 * FROM table ORDER BY NEWID()

Описание

A NEWID() создается для каждой строки, и таблица затем сортируется им. Возвращается первая запись (т.е. Запись с "наименьшим" GUID).

Примечания

• GUID генерируются как псевдослучайные числа с четвертой версии:

  UUID версии 4 предназначен для генерации UUID от по-настоящему случайных или псевдослучайные числа.

  Алгоритм выглядит следующим образом:

  • Установите два наиболее значимых бита (бит 6 и 7) clock_seq_hi_and_reserved на ноль и один, соответственно.
  • Установите четыре наиболее значимых бита (биты с 12 по 15) time_hi_and_version в 4-разрядный номер версии из Раздел 4.1.3.
  • Установить все остальные биты произвольно (или псевдослучайно) значения.

  — Универсальное уникальное идентификационное имя (UUID) - пространство имен URN - RFC 4122

• Альтернатива SELECT TOP 1 * FROM table ORDER BY RAND() не будет работать так, как можно было бы подумать. RAND() возвращает одно значение для каждого запроса, поэтому все строки будут иметь одно и то же значение.

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

• Типичная производительность составляет менее 10 секунд для примерно 1 000 000 строк; конечно, в зависимости от системы. Обратите внимание, что невозможно попасть в индекс, поэтому производительность будет относительно ограничена.

Ответ 2

В больших таблицах также можно использовать TABLESAMPLE, чтобы избежать сканирования всей таблицы.

SELECT  TOP 1 *
FROM YourTable
TABLESAMPLE (1000 ROWS)
ORDER BY NEWID()

ORDER BY NEWID по-прежнему требуется, чтобы избежать просто возвращения строк, которые появляются сначала на странице данных.

Количество, которое нужно использовать, нужно тщательно выбирать для размера и определения таблицы, и вы можете рассмотреть логику повтора, если строка не возвращается. Математика позади этого и почему техника не подходит для небольших таблиц обсуждается здесь

Ответ 3

Также попробуйте ваш метод, чтобы получить случайный идентификатор между MIN (Id) и MAX (Id), а затем

SELECT TOP 1 * FROM table WHERE Id >= @yourrandomid

Он всегда будет иметь одну строку.

Ответ 4

Если вы хотите выбрать большие данные, лучший способ, который я знаю, это:

SELECT * FROM Table1
WHERE (ABS(CAST(
    (BINARY_CHECKSUM
    (keycol1, NEWID())) as int))
    % 100) < 10

Источник: MSDN

Ответ 5

Я пытался улучшить методы, которые я пробовал, и наткнулся на этот пост. Я понимаю это старый, но этот метод не указан. Я создаю и применяю тестовые данные; это показывает метод для "адреса" в SP, вызванный с помощью @st (два состояния char)

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

Ответ 6

  Если вам действительно нужна случайная выборка отдельных строк, измените запрос, чтобы отфильтровать строки случайным образом, вместо использования 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. "

Source: http://technet.microsoft.com/en-us/library/ms189108(v=sql.105).aspx

Это дополнительно объясняется ниже:

Как это работает? Давайте разделим предложение WHERE и объясним его.

Функция CHECKSUM вычисляет контрольную сумму по элементам в список. Есть основания полагать, более ли даже требуется SalesOrderID, т.к. NEWID() - это функция, которая возвращает новый случайный GUID, таким образом, умножая случайная цифра по константе в любом случае должна приводить к случайности. Действительно, исключая SalesOrderID, похоже, не имеет значения. Если ты увлеченный статистик и может обосновать включение этого, пожалуйста, используйте раздел комментариев ниже и дайте мне знать, почему я не прав!

Функция CHECKSUM возвращает VARBINARY. Выполнение побитового И операция с 0x7fffffff, что эквивалентно (111111111...) в двоичном коде, дает десятичное значение, которое является эффективным представлением случайной строки 0 и 1. Деление на коэффициент 0x7fffffff эффективно нормализует эту десятичную цифру к фигуре между 0 и 1. Затем решить, заслуживает ли каждая строка включения в в окончательном наборе результатов используется порог 1/x (в данном случае 0,01) где x - это процент данных для извлечения в качестве образца.

Source: https://www.mssqltips.com/sqlservertip/3157/different-ways-to-get-random-data-for-sql-server-data-sampling