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

Не знаю, насколько это эффективно, но я уже использовал это раньше:

SELECT TOP 1 * FROM MyTable ORDER BY newid()

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

 SELECT * FROM table ORDER BY RAND() LIMIT 1

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

select top 1 * from mytable order by newid()

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

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

Смотрите этот пост: SQL для выбора случайной строки из таблицы базы данных. Он использует методы для этого в MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 и Oracle (следующее скопировано из этой ссылки):

Выберите случайную строку с MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Выберите случайную строку с PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Выберите случайную строку с Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Выберите случайную строку с IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Выберите случайную запись с Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

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

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

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

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

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

Решения, подобные 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). Если ваш набор данных сильно отклоняется от этого предположения, вы получите искаженные результаты (одни строки будут появляться чаще, чем другие).

Я должен согласиться с CD-MaN: использование «ORDER BY RAND ()» будет хорошо работать для небольших таблиц или когда вы выполняете SELECT всего несколько раз.

Я также использую технику «num_value> = RAND () * ...», и если мне действительно нужны случайные результаты, у меня есть специальный «случайный» столбец в таблице, который я обновляю примерно раз в день. Этот единственный запуск UPDATE займет некоторое время (особенно потому, что у вас должен быть индекс для этого столбца), но это намного быстрее, чем создание случайных чисел для каждой строки при каждом запуске select.

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

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

Будьте осторожны, потому что TableSample на самом деле не возвращает случайную выборку строк. Он направляет ваш запрос на поиск случайной выборки страниц размером 8 КБ, составляющих вашу строку. Затем ваш запрос выполняется в отношении данных, содержащихся на этих страницах. Из-за того, как данные могут быть сгруппированы на этих страницах (порядок вставки и т. д.), Это может привести к получению данных, которые на самом деле не являются случайной выборкой.

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

Эта страница MSDN для TableSample включает пример того, как сгенерировать действительно случайную выборку данных.

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

Для SQL Server

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

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

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

If you really want a random sample of individual rows, modify your query to filter out rows randomly, instead of using TABLESAMPLE. For example, the following query uses the NEWID function to return approximately one percent of the rows of the Sales.SalesOrderDetail table:

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

The SalesOrderID column is included in the CHECKSUM expression so that NEWID() evaluates once per row to achieve sampling on a per-row basis. The expression CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) evaluates to a random float value between 0 and 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 .. [последнее значение идентификатора].

Число 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;

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

Поздно, но попал сюда через 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 () в той же таблице. ГГМВ.

ORDER BY NEWID()

принимает 7.4 milliseconds

WHERE num_value >= RAND() * (SELECT MAX(num_value) FROM table)

берет 0.0065 milliseconds!

Я обязательно выберу последний метод.

Если возможно, используйте хранимые операторы, чтобы избежать неэффективности обоих индексов в RND () и создания поля номера записи.

PREPARE RandomRecord FROM "SELECT * FROM table LIMIT ?,1";
SET @n=FLOOR(RAND()*(SELECT COUNT(*) FROM table));
EXECUTE RandomRecord USING @n;

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

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

Например (для DB2):

WITH TEMP AS (
SELECT COMLUMN, RAND() AS IDX FROM TABLE)
SELECT COLUMN FROM TABLE WHERE IDX > .5
FETCH FIRST 1 ROW ONLY

Вы также можете попробовать использовать функцию new id().

Просто напишите свой запрос и используйте функцию new id(). Это довольно случайно.

Как указано в комментарии @BillKarwin к ответу @cnu ...

При объединении с LIMIT я обнаружил, что он работает намного лучше (по крайней мере, с PostgreSQL 9.1) для JOIN со случайным порядком, а не с прямым порядком фактических строк: например,

SELECT * FROM tbl_post AS t
JOIN ...
JOIN ( SELECT id, CAST(-2147483648 * RANDOM() AS integer) AS rand
       FROM tbl_post
       WHERE create_time >= 1349928000
     ) r ON r.id = t.id
WHERE create_time >= 1349928000 AND ...
ORDER BY r.rand
LIMIT 100

Просто убедитесь, что 'r' генерирует значение 'rand' для каждого возможного значения ключа в сложном запросе, который присоединяется к нему, но все же ограничивает количество строк 'r', где это возможно.

CAST as Integer особенно полезен для PostgreSQL 9.2, в котором есть специальная оптимизация сортировки для целочисленных типов и типов с плавающей точкой одинарной точности.

Чтобы MySQL получил случайную запись

 SELECT name
  FROM random AS r1 JOIN
       (SELECT (RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

Подробнее http://jan.kneschke.de/projects/mysql/order-by-rand/

Простой и действенный способ от http://akinas.com/pages/en/blog/mysql_random_row/

SET @i = (SELECT FLOOR(RAND() * COUNT(*)) FROM table); PREPARE get_stmt FROM 'SELECT * FROM table LIMIT ?, 1'; EXECUTE get_stmt USING @i;

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

Для MS SQL:

Минимальный пример:

select top 10 percent *
from table_name
order by rand(checksum(*))

Нормализованное время выполнения: 1.00

Пример NewId ():

select top 10 percent *
from table_name
order by newid()

Нормализованное время выполнения: 1.02

NewId() незначительно медленнее, чем rand(checksum(*)), поэтому вы можете не захотеть использовать его для больших наборов записей.

Селекция с начальным семенем:

declare @seed int
set @seed = Year(getdate()) * month(getdate()) /* any other initial seed here */

select top 10 percent *
from table_name
order by rand(checksum(*) % seed) /* any other math function here */

Если вам нужно выбрать тот же набор с заданным семенем, это, похоже, сработает.

Для Oracle есть лучшее решение вместо использования dbms_random.value, хотя для этого требуется полное сканирование для упорядочивания строк по dbms_random.value, а для больших таблиц это довольно медленно.

Используйте вместо этого:

SELECT *
FROM employee sample(1)
WHERE rownum=1

В MSSQL (проверено 11.0.5569) с использованием

SELECT TOP 100 * FROM employee ORDER BY CRYPT_GEN_RANDOM(10)

значительно быстрее, чем

SELECT TOP 100 * FROM employee ORDER BY NEWID()

Для Firebird:

Select FIRST 1 column from table ORDER BY RAND()

После используя RAND (), так как это не рекомендуется вы можете просто получить максимальный ID (= Макс):

SELECT MAX(ID) FROM TABLE;

получить случайное значение между 1..Max (= My_Generated_Random)

My_Generated_Random = rand_in_your_programming_lang_function(1..Max);

а затем запустите этот SQL:

SELECT ID FROM TABLE WHERE ID >= My_Generated_Random ORDER BY ID LIMIT 1

Обратите внимание, что он проверит любые строки, идентификаторы которых РАВНЫ или ВЫШЕ, чем выбранное значение. Также можно найти строку ниже в таблице и получить идентификатор, равный или меньший, чем My_Generated_Random, а затем изменить запрос следующим образом:

SELECT ID FROM TABLE WHERE ID <= My_Generated_Random ORDER BY ID DESC LIMIT 1

В SQL Server вы можете комбинировать TABLESAMPLE с NEWID (), чтобы получить довольно хорошую случайность при сохранении скорости. Это особенно полезно, если вам действительно нужна только 1 или небольшое количество строк.

SELECT TOP 1 * FROM [table] 
TABLESAMPLE (500 ROWS) 
ORDER BY NEWID()

В SQL Server 2012+ вы можете использовать OFFSET FETCH запрос, чтобы сделать это для одной случайной строки.

select  * from MyTable ORDER BY id OFFSET n ROW FETCH NEXT 1 ROWS ONLY

где id - столбец идентификаторов, а n - нужная строка, вычисляемая как случайное число от 0 до count () - 1 таблицы (смещение 0 - это первая строка в конце концов)

Это работает с дырами в данных таблицы, если у вас есть индекс для работы с предложением ORDER BY. Это также очень хорошо для случайности - поскольку вы сами решаете, что нужно передать, но мелочей в других методах нет. Вдобавок производительность довольно хороша, на меньшем наборе данных она хорошо держится, хотя я не пробовал серьезных тестов производительности с несколькими миллионами строк.

Для SQL Server 2005 и выше расширение ответа @ GreyPanther для случаев, когда num_value не имеет непрерывных значений. Это также работает в тех случаях, когда у нас нет равномерно распределенных наборов данных и когда num_value - это не число, а уникальный идентификатор.

WITH CTE_Table (SelRow, num_value) 
AS 
(
    SELECT ROW_NUMBER() OVER(ORDER BY ID) AS SelRow, num_value FROM table
) 

SELECT * FROM table Where num_value = ( 
    SELECT TOP 1 num_value FROM CTE_Table  WHERE SelRow >= RAND() * (SELECT MAX(SelRow) FROM CTE_Table)
)

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

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

select r.id, r.name from table AS r
INNER JOIN(select CEIL(RAND() * (select MAX(id) from table)) as id) as r1
ON r.id >= r1.id ORDER BY r.id ASC LIMIT 1

Это потребует меньшего времени вычислений.

Для SQL Server и требуется "одна случайная строка" ..

Если истинная выборка не требуется, сгенерируйте случайное значение [0, max_rows) и используйте ORDER BY..OFFSET..FETCH из SQL Server 2012+.

Это очень быстро, если COUNT и ORDER BY превышают соответствующие индексы - так что данные «уже отсортированы» по строкам запроса. Если эти операции покрыты, это быстрый запрос и не страдает от ужасная масштабируемость использования ORDER BY NEWID() или аналогичного. Очевидно, что этот подход не будет хорошо масштабироваться для неиндексированной таблицы HEAP.

declare @rows int
select @rows = count(1) from t

-- Other issues if row counts in the bigint range..
-- This is also not 'true random', although such is likely not required.
declare @skip int = convert(int, @rows * rand())

select t.*
from t
order by t.id -- Make sure this is clustered PK or IX/UCL axis!
offset (@skip) rows
fetch first 1 row only

Убедитесь, что используются соответствующие уровни изоляции транзакций и / или учитываются нулевые результаты.

Для SQL Server и необходим подход "общего образца строки".

Примечание: это адаптация найденного ответа по конкретному вопросу SQL Server об извлечении образца строк. Он был адаптирован для контекста.

Хотя общий подход к выборке здесь следует использовать с осторожностью, он все же потенциально полезная информация в контексте других ответов (и повторяющихся предложений немасштабируемых и / или сомнительных реализаций). Такой подход к выборке менее эффективен, чем показанный первый код, и подвержен ошибкам, если цель состоит в том, чтобы найти «единственную случайную строку».

Вот обновленная и улучшенная форма выборка процента строк. Он основан на той же концепции, что и некоторые другие ответы, в которых используется CHECKSUM / BINARY_CHECKSUM и модуль.

• Это относительно быстро по огромным наборам данных и может эффективно использоваться в / с производными запросами. Миллионы предварительно отфильтрованных строк могут быть выбраны за секунды без использования tempdb, и, если они выровнены с остальной частью запроса, накладные расходы часто минимальны.

• Не страдает от проблем CHECKSUM(*) / BINARY_CHECKSUM(*) с сериями данных. При использовании подхода CHECKSUM(*) строки можно выбирать «порциями», а не «случайным образом»! Это потому, что CHECKSUM предпочитает скорость распределению.

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

• Не использует ORDER BY NEWID()всего входного набора, как заказ может стать серьезным узким местом с большими входными наборами. Исключение сортировки ненужный также уменьшает использование памяти и tempdb.

• Не использует TABLESAMPLE и, следовательно, работает с предварительным фильтром WHERE.

Вот суть. См. Этот ответ для получения дополнительных сведений и примечаний..

Наивная попытка:

declare @sample_percent decimal(7, 4)
-- Looking at this value should be an indicator of why a
-- general sampling approach can be error-prone to select 1 row.
select @sample_percent = 100.0 / count(1) from t

-- BAD!
-- When choosing appropriate sample percent of "approximately 1 row"
-- it is very reasonable to expect 0 rows, which definitely fails the ask!
-- If choosing a larger sample size the distribution is heavily skewed forward,
-- and is very much NOT 'true random'.
select top 1
    t.*
from t
where 1=1
    and ( -- sample
        @sample_percent = 100
        or abs(
            convert(bigint, hashbytes('SHA1', convert(varbinary(32), t.rowguid)))
        ) % (1000 * 100) < (1000 * @sample_percent)
    )

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

-- Sample "approximately 1000 rows" from the table,
-- dealing with some edge-cases.
declare @rows int
select @rows = count(1) from t

declare @sample_size int = 1000
declare @sample_percent decimal(7, 4) = case
    when @rows <= 1000 then 100                              -- not enough rows
    when (100.0 * @sample_size / @rows) < 0.0001 then 0.0001 -- min sample percent
    else 100.0 * @sample_size / @rows                        -- everything else
    end

-- There is a statistical "guarantee" of having sampled a limited-yet-non-zero number of rows.
-- The limited rows are then sorted randomly before the first is selected.
select top 1
    t.*
from t
where 1=1
    and ( -- sample
        @sample_percent = 100
        or abs(
            convert(bigint, hashbytes('SHA1', convert(varbinary(32), t.rowguid)))
        ) % (1000 * 100) < (1000 * @sample_percent)
    )
-- ONLY the sampled rows are ordered, which improves scalability.
order by newid()