Подтвердить что ты не робот

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

Как я могу запросить случайную строку (или как можно ближе к действительно случайной, как это возможно) в чистом SQL?

4b9b3361

ОТВЕТЫ

Ответ 1

Смотрите это сообщение: 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

Ответ 2

Решения, подобные Джереми:

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

Ответ 3

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

SELECT TOP 1 * FROM MyTable ORDER BY newid()

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

Ответ 4

ORDER BY NEWID()

принимает 7.4 milliseconds

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

принимает 0.0065 milliseconds!

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

Ответ 5

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

select top 1 * from mytable order by newid()

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

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

Ответ 6

Для SQL Server

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

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

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

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

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

Ответ 7

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

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

Ответ 8

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

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

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

Для решения

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

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

Ответ 9

Для SQL Server 2005 и 2008, если нам нужна произвольная выборка отдельных строк (из Books Online):

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

Ответ 10

При помощи с помощью RAND(), поскольку это не рекомендуется, вы можете просто получить max ID (= Max):

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

Обратите внимание, что он будет проверять любые строки, идентификаторы которых EQUAL или HIGHER, чем выбранное значение. Также можно охотиться за строкой вниз в таблице и получить равный или более низкий идентификатор, чем My_Generated_Random, а затем изменить запрос следующим образом:

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

Ответ 11

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

При объединении с 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, который имеет определенную оптимизацию сортировки для целых и одиночных прецизионных плавающих типов.

Ответ 12

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

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

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

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

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;

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

Ответ 13

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

Другой подход - дважды использовать TOP, с чередующимися порядками. Я не знаю, является ли это "чистым SQL", потому что он использует переменную в TOP, но работает в 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() в той же таблице. YYMV.

Ответ 14

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

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

Ответ 15

Для 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/

Ответ 16

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

Для 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 */

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

Ответ 17

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

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

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

SELECT TOP 100 * FROM employee ORDER BY NEWID()

Ответ 18

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

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

Ответ 19

 SELECT * FROM table ORDER BY RAND() LIMIT 1

Ответ 20

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

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

Ответ 21

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

Смотрите: http://www.mssqltips.com/tip.asp?tip=1308

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

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

Ответ 22

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

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

Ответ 23

Простой и эффективный способ из 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;

Ответ 24

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

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

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

Ответ 25

Для Firebird:

Select FIRST 1 column from table ORDER BY RAND()

Ответ 26

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

Ответ 27

Для 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)
)

Ответ 28

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

SELECT column FROM table
ORDER BY RAND()
LIMIT 1