Как запросить случайную строку в SQL?

Я должен согласиться с ЧЕЛОВЕКОМ CD: Используя "РЭНД ORDER BY ()" будет работать приятно на маленькие таблицы или когда Вы сделаете свой ВЫБОР только несколько раз.

я также использую "num_value> = РЭНД () *..." техника, и если я действительно хочу иметь случайные результаты, у меня есть специальный "случайный" столбец в таблице, которую я обновляю один раз в день или около этого. То единственное выполненное ОБНОВЛЕНИЕ займет время (особенно, потому что у Вас должен будет быть индекс на том столбце), но это намного быстрее, чем создание случайных чисел для каждой строки каждый раз, когда выбор выполняется.

 SELECT * FROM table ORDER BY RAND() LIMIT 1

Лучший способ помещает случайное значение в новый столбец просто с этой целью и использует что-то вроде этого (pseude код + SQL):

randomNo = random()
execSql("SELECT TOP 1 * FROM MyTable WHERE MyTable.Randomness > $randomNo")

Это - решение, используемое кодом MediaWiki. Конечно, существует некоторая предвзятость против меньших значений, но они нашли, что было достаточно перенести случайное значение для обнуления, когда никакие строки не выбираются.

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

рэнд () решение не может работать вообще (т.е. с MSSQL), потому что функция будет оценена только однажды, и каждый , строке присвоят то же "случайное" число.

Вы не сказали, какой сервер Вы используете. В более старых версиях SQL Server можно использовать это:

select top 1 * from mytable order by newid()

В SQL Server 2005 и, можно использовать TABLESAMPLE для получения случайной выборки, это повторяемо:

SELECT FirstName, LastName
FROM Contact 
TABLESAMPLE (1 ROWS) ;

Я не знаю, насколько эффективный это, но я использовал его прежде:

SELECT TOP 1 * FROM MyTable ORDER BY newid()

, поскольку GUID довольно случайны, упорядочивание означает, что Вы получаете случайную строку.

Решения как Jeremies:

SELECT * FROM table ORDER BY RAND() LIMIT 1

работа, но им нужно последовательное сканирование всей таблицы (потому что случайное значение, связанное с каждой строкой, должно быть вычислено - так, чтобы самый маленький мог быть определен), который может быть довольно медленным для даже таблиц среднего размера. Моя рекомендация состояла бы в том, чтобы использовать некоторый индексируемый числовой столбец (много таблиц имеют их как свои первичные ключи), и затем запишите что-то как:

SELECT * FROM table WHERE num_value >= RAND() * 
    ( SELECT MAX (num_value ) FROM table ) 
ORDER BY num_value LIMIT 1

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

Для SQL Server 2005 и 2008, если мы хотим случайную выборку отдельных строк (от Книги Онлайн ):

SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float)
/ CAST (0x7fffffff AS int)

Be careful because TableSample doesn't actually return a random sample of rows. It directs your query to look at a random sample of the 8KB pages that make up your row. Then, your query is executed against the data contained in these pages. Because of how data may be grouped on these pages (insertion order, etc), this could lead to data that isn't actually a random sample.

See: http://www.mssqltips.com/tip.asp?tip=1308

This MSDN page for TableSample includes an example of how to generate an actualy random sample of data.

http://msdn.microsoft.com/en-us/library/ms189108.aspx

Для SQL Server

newid () / order by будет работать, но будет очень дорого для больших наборов результатов, потому что он должен генерировать идентификатор для каждой строки, а затем сортировать их.

TABLESAMPLE () хорош с точки зрения производительности, но вы получите группировку результатов (будут возвращены все строки на странице).

Для более точной истинной случайной выборки лучший способ - отфильтровать строки случайно. Я нашел следующий образец кода в статье электронной документации по SQL Server Ограничение наборов результатов с помощью TABLESAMPLE :

Если вам действительно нужна случайная выборка отдельные строки, измените свой запрос на отфильтровывать строки случайным образом вместо используя TABLESAMPLE. Например, в следующем запросе используется NEWID функция для возврата примерно одного процентов строк Таблица Sales.SalesOrderDetail:

 ВЫБРАТЬ * ИЗ Sales.SalesOrderDetail
WHERE 0.01> = CAST (КОНТРОЛЬНАЯ СУММА (NEWID (), SalesOrderID) & 0x7fffffff AS с плавающей запятой)
 / CAST (0x7fffffff как целое)

Столбец SalesOrderID включен в выражение CHECKSUM так, чтобы NEWID () вычисляет один раз для каждой строки добиться выборки для каждой строки. Выражение CAST (CHECKSUM (NEWID (), SalesOrderID) & 0x7fffffff AS с плавающей запятой / CAST (0x7fffffff AS int) оценивается как случайное значение с плавающей запятой от 0 до 1.

При запуске для таблицы с 1 000 000 строк, вот мои результаты:

SET STATISTICS TIME ON
SET STATISTICS IO ON

/* newid()
   rows returned: 10000
   logical reads: 3359
   CPU time: 3312 ms
   elapsed time = 3359 ms
*/
SELECT TOP 1 PERCENT Number
FROM Numbers
ORDER BY newid()

/* TABLESAMPLE
   rows returned: 9269 (varies)
   logical reads: 32
   CPU time: 0 ms
   elapsed time: 5 ms
*/
SELECT Number
FROM Numbers
TABLESAMPLE (1 PERCENT)

/* Filter
   rows returned: 9994 (varies)
   logical reads: 3359
   CPU time: 641 ms
   elapsed time: 627 ms
*/    
SELECT Number
FROM Numbers
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), Number) & 0x7fffffff AS float) 
              / CAST (0x7fffffff AS int)

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Если вам удастся использовать TABLESAMPLE, это даст вам лучшую производительность. В противном случае используйте метод newid () / filter. newid () / order by следует использовать в крайнем случае, если у вас большой набор результатов.

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

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

Следующее решение работает с PostgreSQL 8.4:

explain analyze select * from cms_refs where rec_id in 
  (select (random()*(select last_value from cms_refs_rec_id_seq))::bigint 
   from generate_series(1,10))
  limit 1;

В приведенном выше решении вы угадываете 10 различных случайных значений индекса из диапазона 0 .. [последнее значение id].

Число 10 произвольно - вы можете использовать 100 или 1000, поскольку это (что удивительно) не оказывает большого влияния на время отклика.

Есть еще одна проблема - если у вас редкие идентификаторы , вы можете пропустить . Решение состоит в том, чтобы иметь план резервного копирования :) В этом случае чистый старый порядок с помощью запроса random (). Когда объединенный идентификатор выглядит так:

explain analyze select * from cms_refs where rec_id in 
    (select (random()*(select last_value from cms_refs_rec_id_seq))::bigint 
     from generate_series(1,10))
    union all (select * from cms_refs order by random() limit 1)
    limit 1;

Не предложение union ALL . В этом случае, если первая часть возвращает какие-либо данные, вторая НИКОГДА не выполняется!

Поздно, но попал сюда через Google, поэтому для потомков я добавлю альтернативное решение.

Другой подход - использовать TOP дважды, с чередованием порядка. Я не знаю, является ли это «чистым SQL», потому что он использует переменную в ТОПе, но он работает в SQL Server 2008. Вот пример, который я использую для таблицы слов словаря, если мне нужно случайное слово.

SELECT TOP 1
  word
FROM (
  SELECT TOP(@idx)
    word 
  FROM
    dbo.DictionaryAbridged WITH(NOLOCK)
  ORDER BY
    word DESC
) AS D
ORDER BY
  word ASC

Конечно, @idx - это некоторое случайно сгенерированное целое число в диапазоне от 1 до COUNT (*) в целевой таблице включительно. Если ваш столбец проиндексирован, вам это тоже будет выгодно. Еще одно преимущество состоит в том, что вы можете использовать его в функции, поскольку NEWID () запрещен.

Наконец, указанный выше запрос выполняется примерно за 1/10 времени выполнения запроса типа NEWID () в той же таблице. ГГМВ.