Selecione n linhas aleatórias da tabela do SQL Server

Eu tenho uma tabela do SQL Server com cerca de 50.000 linhas nele. Eu quero selecionar cerca de 5.000 dessas linhas aleatoriamente. Pensei em uma maneira complicada, criando uma tabela temporária com uma coluna “número random“, copiando minha tabela para ela, percorrendo a tabela temporária e atualizando cada linha com RAND() e, em seguida, selecionando da tabela onde o random número da coluna <0,1. Eu estou procurando uma maneira mais simples de fazê-lo, em uma única declaração, se possível.

Este artigo sugere usando a function NEWID() . Isso parece promissor, mas não consigo ver como eu poderia selecionar com segurança uma determinada porcentagem de linhas.

Alguém já fez isso antes? Alguma ideia?

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

Em resposta ao comentário “lixo puro” referente a tabelas grandes: você pode fazer isso para melhorar o desempenho.

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

O custo disso será a varredura-chave dos valores mais o custo de associação, que em uma tabela grande com uma pequena porcentagem de seleção deve ser razoável.

Dependendo de suas necessidades, o TABLESAMPLE fará com que você tenha um desempenho quase random e melhor. isso está disponível no MS SQL server 2005 e posterior.

TABLESAMPLE retornará dados de páginas aleatórias em vez de linhas aleatórias e, portanto, nem mesmo recuperará dados que não serão retornados.

Em uma mesa muito grande eu testei

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

demorou mais de 20 minutos.

 select * from [tablename] tablesample(1 percent) 

demorou 2 minutos.

O desempenho também melhorará em amostras menores em TABLESAMPLE mas não em newid() .

Por favor, tenha em mente que isto não é tão random quanto o método newid() , mas lhe dará uma amostragem decente.

Veja a página do MSDN .

newid () / order by irá funcionar, mas será muito caro para grandes conjuntos de resultados porque ele tem que gerar um id para cada linha e depois classificá-los.

TABLESAMPLE () é bom do ponto de vista de desempenho, mas você obterá agrupamento de resultados (todas as linhas em uma página serão retornadas).

Para uma amostra aleatória verdadeira com melhor desempenho, a melhor maneira é filtrar as linhas aleatoriamente. Eu encontrei o exemplo de código a seguir no artigo SQL Server Books Online limitando conjuntos de resultados usando TABLESAMPLE :

Se você realmente quiser uma amostra aleatória de linhas individuais, modifique sua consulta para filtrar as linhas aleatoriamente, em vez de usar TABLESAMPLE. Por exemplo, a consulta a seguir usa a function NEWID para retornar aproximadamente um por cento das linhas da tabela Sales.SalesOrderDetail:

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

A coluna SalesOrderID é incluída na expressão CHECKSUM para que NEWID () seja avaliado uma vez por linha para obter a amostragem por linha. A expressão CAST (CHECKSUM (NEWID (), SalesOrderID) e 0x7fffffff como float / CAST (0x7fffffff AS int) é avaliada como um valor flutuante random entre 0 e 1.

Quando correr contra uma tabela com 1.000.000 linhas, aqui estão os meus resultados:

 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 

Se você conseguir usar o TABLESAMPLE, ele terá o melhor desempenho. Caso contrário, use o método newid () / filter. newid () / order by deve ser o último recurso se você tiver um grande conjunto de resultados.

Selecionar aleatoriamente linhas de uma tabela grande no MSDN tem uma solução simples e bem articulada que aborda as preocupações de desempenho em larga escala.

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

Basta pedir a tabela por um número random e obter as primeiras 5.000 linhas usando TOP .

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

ATUALIZAR

Apenas tentei e uma chamada newid() é suficiente – não há necessidade de todos os castings e toda a matemática.

Se você (ao contrário do OP) precisa de um número específico de registros (o que torna a abordagem CHECKSUM difícil) e deseja uma amostra mais aleatória do que TABLESAMPLE fornece por si só, e também quer uma velocidade melhor do que CHECKSUM, você pode se contentar com uma fusão do Métodos TABLESAMPLE e NEWID (), assim:

 DECLARE @sampleCount int = 50 SET STATISTICS TIME ON SELECT TOP (@sampleCount) * FROM [yourtable] TABLESAMPLE(10 PERCENT) ORDER BY NEWID() SET STATISTICS TIME OFF 

No meu caso, este é o compromisso mais simples entre aleatoriedade (não é realmente, eu sei) e velocidade. Varie a porcentagem TABLESAMPLE (ou linhas) conforme apropriado – quanto maior a porcentagem, mais aleatória será a amostra, mas espere uma queda linear na velocidade. (Note que TABLESAMPLE não aceita uma variável)

Este link tem uma comparação interessante entre Orderby (NEWID ()) e outros methods para tabelas com 1, 7 e 13 milhões de linhas.

Muitas vezes, quando perguntas sobre como selecionar linhas aleatórias são feitas em grupos de discussão, a consulta NEWID é proposta; é simples e funciona muito bem para pequenas tabelas.

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

No entanto, a consulta NEWID tem uma grande desvantagem quando você usá-lo para tabelas grandes. A cláusula ORDER BY faz com que todas as linhas da tabela sejam copiadas para o database tempdb, onde são classificadas. Isso causa dois problemas:

  1. A operação de sorting geralmente tem um alto custo associado a ela. A sorting pode usar muita E / S de disco e pode ser executada por um longo tempo.
  2. Na pior das hipóteses, o tempdb pode ficar sem espaço. Na melhor das hipóteses, o tempdb pode ocupar uma grande quantidade de espaço em disco que nunca será recuperado sem um comando de redução manual.

O que você precisa é uma maneira de selecionar linhas aleatoriamente que não usem tempdb e não fiquem muito mais lentas à medida que a tabela for maior. Aqui está uma nova ideia sobre como fazer isso:

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

A ideia básica por trás dessa consulta é que queremos gerar um número random entre 0 e 99 para cada linha da tabela e, em seguida, escolher todas as linhas cujo número random seja menor que o valor da porcentagem especificada. Neste exemplo, queremos aproximadamente 10 por cento das linhas selecionadas aleatoriamente; portanto, escolhemos todas as linhas cujo número random é menor que 10.

Por favor, leia o artigo completo no MSDN .

No MySQL você pode fazer isso:

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

Essa é uma combinação da ideia inicial de semente e uma sum de verificação, que me parece dar resultados randoms adequadamente sem o custo de NEWID ():

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

Tente isto:

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

Ainda não vi essa variação nas respostas. Eu tinha uma restrição adicional de onde precisava, dada uma semente inicial, para selecionar o mesmo conjunto de linhas a cada vez.

Para MS SQL:

Exemplo Mínimo:

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

Tempo de execução normalizado: 1,00

Exemplo NewId ():

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

Tempo de execução normalizado: 1,02

NewId() é insignificantemente mais lento que rand(checksum(*)) , portanto, talvez você não queira usá-lo em grandes conjuntos de registros.

Seleção com Semente Inicial:

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

Se você precisar selecionar o mesmo conjunto dado uma semente, isso parece funcionar.

Isso funciona para mim:

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

Parece que o newid () não pode ser usado na cláusula where, portanto, esta solução requer uma consulta interna:

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

Eu estava usando na subconsulta e me retornou as mesmas linhas na subconsulta

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

então eu resolvi com incluindo variável tabela pai em onde

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

Observe a condiçăo onde

A linguagem de processamento do lado do servidor em uso (por exemplo, PHP, .net, etc) não é especificada, mas se for PHP, pegue o número requerido (ou todos os registros) e em vez de randomizar na consulta use a function shuffle do PHP. Eu não sei se .net tem uma function equivalente, mas se isso acontecer, use se você estiver usando .net

ORDER BY RAND () pode ter uma penalidade de desempenho bastante, dependendo de quantos registros estão envolvidos.