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

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

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

Кто-нибудь когда-нибудь это делал раньше? Любые идеи?

Ответы

Ответ 1

select top 10 percent * from [yourtable] order by newid()

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

select  * from [yourtable] where [yourPk] in 
(select top 10 percent [yourPk] from [yourtable] order by newid())

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

Ответ 2

В зависимости от ваших потребностей TABLESAMPLE вы получите почти как случайную и лучшую производительность. это доступно на сервере MS SQL 2005 и более поздних версиях.

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

На очень большой таблице я протестировал

select top 1 percent * from [tablename] order by newid()

заняло более 20 минут.

select * from [tablename] tablesample(1 percent)

заняло 2 минуты.

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

Пожалуйста, имейте в виду, что это не так случайно, как метод newid(), но даст вам приличную выборку.

См. страницу

Ответ 3

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

Ответ 5

Просто закажите таблицу случайным числом и получите первые 5000 строк, используя TOP.

SELECT TOP 5000 * FROM [Table] ORDER BY newid();

UPDATE

Просто попробовал, а вызов newid() достаточно - не нужно для всех прикладов и всей математики.

Ответ 6

Если вам (в отличие от 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 не принимает переменную)

Ответ 7

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

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

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

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

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

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

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

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

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

Ответ 8

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

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

Ответ 9

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

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

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

Ответ 10

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

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

Ответ 11

Попробуйте следующее:

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

Ответ 12

Это работает для меня:

SELECT * FROM table_name
ORDER BY RANDOM()
LIMIT [number]

Ответ 13

Похоже, что newoid() не может использоваться в условии where, поэтому для этого решения требуется внутренний запрос:

SELECT *
FROM (
    SELECT *, ABS(CHECKSUM(NEWID())) AS Rnd
    FROM MyTable
) vw
WHERE Rnd % 100 < 10        --10%

Ответ 14

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

 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

Обратите внимание, где условие