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




Выберите n случайных строк из таблицы SQL Server (10)

У меня есть таблица SQL Server с около 50 000 строк. Я хочу выбрать около 5000 из этих строк наугад. Я подумал о сложном способе создания временной таблицы со столбцом «случайное число», скопировав в нее таблицу, перейдя по временной таблице и обновив каждую строку с помощью RAND() , а затем выбрав из этой таблицы случайную номер столбца <0,1. Я ищу более простой способ сделать это, если возможно, в одном заявлении.

В этой статье предлагается использовать NEWID() . Это выглядит многообещающе, но я не вижу, как я могу надежно выбрать определенный процент строк.

Кто-нибудь когда-либо делал это раньше? Есть идеи?


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


В MySQL вы можете сделать это:

SELECT `PRIMARY_KEY`, rand() FROM table ORDER BY rand() LIMIT 5000;

Если вам (в отличие от OP) требуется определенное количество записей (что затрудняет подход CHECKSUM) и желание более случайной выборки, чем TABLESAMPLE, предоставляет сам по себе, а также хочет более высокую скорость, чем CHECKSUM, вы можете обойтись с слиянием TABLESAMPLE и NEWID (), например:

DECLARE @sampleCount int = 50
SET STATISTICS TIME ON

SELECT TOP (@sampleCount) * 
FROM [yourtable] TABLESAMPLE(10 PERCENT)
ORDER BY NEWID()

SET STATISTICS TIME OFF

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


Используемый язык обработки на стороне сервера (например, PHP, .net и т. Д.) Не указан, но если это PHP, возьмите требуемое число (или все записи) и вместо случайности в запросе используйте функцию Shuffle PHP. Я не знаю, имеет ли .net эквивалентную функцию, но если это так, то используйте это, если вы используете .net

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 */

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


Попробуй это:

SELECT TOP 10 Field1, ..., FieldN
FROM Table1
ORDER BY NEWID()

Эта ссылка имеет интересное сравнение между Orderby (NEWID ()) и другими методами для таблиц с 1, 7 и 13 миллионами строк.

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

SELECT TOP 10 PERCENT *
  FROM Table1
  ORDER BY NEWID()

Однако запрос NEWID имеет большой недостаток, когда вы используете его для больших таблиц. Предложение ORDER BY заставляет все строки в таблице копироваться в базу данных tempdb, где они сортируются. Это вызывает две проблемы:

  1. Операция сортировки обычно связана с высокой стоимостью. Сортировка может использовать много дискового ввода-вывода и может работать в течение длительного времени.
  2. В худшем случае tempdb может закончиться. В лучшем случае tempdb может занимать большое количество дискового пространства, которое никогда не будет восстановлено без команды ручной усадки.

То, что вам нужно, это способ случайного выбора строк, которые не будут использовать tempdb, и не будут становиться намного медленнее, поскольку таблица становится больше. Вот новая идея о том, как это сделать:

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

Основная идея этого запроса заключается в том, что мы хотим создать случайное число от 0 до 99 для каждой строки в таблице, а затем выбрать все те строки, случайное число которых меньше значения указанного процента. В этом примере мы хотим, чтобы примерно 10 процентов строк были выбраны случайным образом; поэтому мы выбираем все строки, случайное число которых меньше 10.

Пожалуйста, прочитайте полную статью в MSDN .


Это комбинация начальной идеи семени и контрольной суммы, которая позволяет мне давать правильные случайные результаты без стоимости NEWID ():

SELECT TOP [number] 
FROM table_name
ORDER BY RAND(CHECKSUM(*) * RAND())

Я использовал его в подзапросе, и он возвратил мне те же строки в подзапросе

 SELECT  ID ,
            ( SELECT TOP 1
                        ImageURL
              FROM      SubTable 
              ORDER BY  NEWID()
            ) AS ImageURL,
            GETUTCDATE() ,
            1
    FROM    Mytable

то я решил с включением переменной родительской таблицы в

SELECT  ID ,
            ( SELECT TOP 1
                        ImageURL
              FROM      SubTable 
              Where Mytable.ID>0
              ORDER BY  NEWID()
            ) AS ImageURL,
            GETUTCDATE() ,
            1
    FROM    Mytable

Обратите внимание на место






random