My<cod/>.net
Как запросить строку random в SQL?
Как я могу запросить строку random (или как можно ближе к истинно random) в чистом SQL?
Смотрите эту запись: SQL, чтобы выбрать строку random из таблицы базы данных . Он проходит через методы для этого в MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 и Oracle (из этой ссылки копируется следующее):
Выберите строку random с MySQL:
SELECT column FROM table
ORDER BY RAND()
LIMIT 1
Выберите строку random с PostgreSQL:
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1
Выберите строку random с сервером Microsoft SQL:
SELECT TOP 1 column FROM table
ORDER BY NEWID()
Выберите строку random с помощью IBM DB2
SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Выберите запись random с Oracle:
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1
Решения, как Джереми:
SELECT * FROM table ORDER BY RAND() LIMIT 1
работа, но они нуждаются в последовательном сканировании всей таблицы (потому что значение random, связанное с каждой строкой, должно быть вычислено - так, чтобы можно было определить самую маленькую), что может быть довольно медленным даже для средних таблиц. Моя рекомендация состояла бы в том, чтобы использовать какой-то индексированный числовой столбец (многие таблицы имеют их в качестве своих первичных ключей), а затем написать что-то вроде:
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 вы можете использовать это:
select top 1 * from mytable order by newid()
В SQL Server 2005 и выше можно использовать TABLESAMPLE для получения повторяющегося образца random:
SELECT FirstName, LastName
FROM Contact
TABLESAMPLE (1 ROWS) ;
Для Сервера SQL
newid() / order by будет работать, но будет очень дорогим для больших результирующих наборов, потому что он должен генерировать идентификатор для каждой строки, а затем сортировать их.
TABLESAMPLE() хорош с точки зрения производительности, но вы получите сгущение результатов (все строки на странице будут возвращены).
Для лучшего выполнении настоящий образец random, лучший способ, чтобы отфильтровать строки случайным образом. Я нашел следующий пример кода в статье SQL Server Books Online, ограничивающей наборы результатов с помощью TABLESAMPLE :
Если вы действительно хотите образец random отдельные строки, измените свой запрос на отфильтровать строки случайным образом, а не используя 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 поплавок / CAST (0x7fffffff AS int) вычисляется как a random плавающее значение между 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 должен быть последним средством, если у вас есть большой результирующий набор.
Лучший способ-это поместить значение random в новый столбец только для этой цели и использовать что-то вроде этого (код псевдонима + SQL):
randomNo = random()
execSql("SELECT TOP 1 * FROM MyTable WHERE MyTable.Randomness > $randomNo")
Это решение используется кодом MediaWiki. Конечно, есть некоторый уклон против меньших значений, но они обнаружили, что достаточно обернуть значение random вокруг нуля, когда никакие строки не извлекаются.
Решение newid() может потребовать полного сканирования таблицы, чтобы каждой строке можно было назначить новый идентификатор guid, который будет гораздо менее производительным.
rand() решение может вообще не работать (т. е. с MSSQL), потому что функция будет вычислена только один раз, и каждой строке будет присвоен один и тот же номер "random".
Для SQL Server 2005 и 2008, если мы хотим получить образец random отдельных строк (из книг онлайн ):
SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float)
/ CAST (0x7fffffff AS int)
Вместо использования RAND(), поскольку это не рекомендуется , вы можете просто получить max ID (=Max):
SELECT MAX(ID) FROM TABLE;
получить random между 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, чем выбранное значение. Кроме того, можно найти строку внизу таблицы и получить равный или меньший ID, чем My_Generated_Random, а затем изменить запрос следующим образом:
SELECT ID FROM TABLE WHERE ID <= My_Generated_Random ORDER BY ID DESC LIMIT 1
Как указано в комментарии @BillKarwin's к ответу @cnu's...
При объединении с LIMIT я обнаружил, что он работает намного лучше (по крайней мере, с PostgreSQL 9.1) для JOIN с упорядочиванием random, а не для непосредственного упорядочения фактических строк: например
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 как целое число особенно полезно для PostgreSQL 9.2, который имеет определенную оптимизацию сортировки для целых и одиночных типов точности с плавающей запятой.
В конце концов, но попал сюда через Google, так что ради потомства я добавлю альтернативное решение.
Другой подход заключается в использовании TOP дважды, с чередованием порядков. Я не знаю, является ли он "чистым SQL", потому что он использует переменную в TOP, но он работает в SQL Server 2008. Вот пример, который я использую против таблицы словарных слов, если мне нужно слово random.
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.
Большинство решений здесь направлены на то, чтобы избежать сортировки, но им все равно нужно выполнить последовательное сканирование по таблице.
Существует также способ избежать последовательного сканирования, переключившись на индексное сканирование. Если вы знаете значение индекса вашей строки random, вы можете получить результат почти мгновенно. Проблема в том, как угадать значение индекса.
Следующее решение работает на 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;
I выше решение вы угадываете 10 различных значений индекса random из диапазона 0 .. [последнее значение идентификатора].
Число 10 произвольно - вы можете использовать 100 или 1000, поскольку оно (удивительно) не оказывает большого влияния на время отклика.
Существует также одна проблема - если у вас есть редкие идентификаторы, вы можете пропустить их . Решение состоит в том, чтобы иметь план резервного копирования :) в этом случае чистый старый заказ по запросу random(). При объединении id выглядит примерно так:
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;
Только не оговорка о союзе ALL . В этом случае, если первая часть возвращает какие-либо данные, то вторая часть NEVER выполняется!
Для MySQL получить random запись
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/
Я еще не видел такого разнообразия в ответах. У меня было дополнительное ограничение, когда мне нужно было, учитывая начальное семя, каждый раз выбирать один и тот же набор строк.
Для 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 */
Если вам нужно выбрать один и тот же набор, заданный семенем, это, кажется, работает.
Похоже, что многие из перечисленных идей все еще используют упорядочивание
Однако, если вы используете временную таблицу, вы можете назначить индекс random (как и многие из предложенных решений), а затем захватить первый, который больше, чем произвольное число между 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
Я должен согласиться с CD-MaN: использование "ORDER BY RAND()" будет хорошо работать для небольших столов или когда вы делаете свой SELECT только несколько раз.
Я также использую "num_value >= RAND() * ..."техника, и если я действительно хочу иметь результаты random, у меня есть специальный столбец "random" в таблице, который я обновляю примерно раз в день. Этот одиночный запуск UPDATE займет некоторое время (особенно потому, что вы должны будете иметь индекс для этого столбца), но это гораздо быстрее, чем создавать номера random для каждой строки при каждом запуске select.
Будьте осторожны, потому что TableSample на самом деле не возвращает образец строк random. Он направляет ваш запрос на просмотр образца random из 8kb страниц, которые составляют вашу строку. Затем ваш запрос выполняется по данным, содержащимся на этих страницах. Из-за того, как данные могут быть сгруппированы на этих страницах (порядок вставки и т. д.), Это может привести к тому, что данные на самом деле не являются образцом random.
Смотрите: http://www.mssqltips.com/tip.asp?совет=1308
Эта страница MSDN для TableSample содержит пример того, как создать фактический образец данных random.
http://msdn.microsoft.com/en-us/library/ms189108.aspx
Простой и эффективный способ от 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;