Amostras aleatórias simples de uma base de Dados Sql
a resposta" óbvia " é para:
SELECT * FROM table ORDER BY RAND() LIMIT 10000
para mesas grandes, isso é muito lento: ele chama RAND() para cada linha (que já coloca em O(n)), e ordená-los, tornando-o o(n lg n) no melhor dos casos. Há alguma maneira de fazer isto mais rápido do que O(n)?
nota : como salienta Andrew Mao nos comentários, se estiver a usar esta abordagem no servidor SQL, deverá usar a função T-SQL NEWID (), porque o RAND () poderá devolver o mesmo valor para todas as linhas .
EDITAR: 5 ANOS DEPOIS
encontrei este problema novamente com uma mesa maior, e acabei usando uma versão da solução de @ignorant, com dois ajustes:
- amostrar as linhas para 2-5x o meu tamanho de amostra desejado, para uma ordem barata por RAND ()
- Guarde o resultado da RAND () para uma indexação coluna em cada inserção / actualização. (Se seu conjunto de dados não é muito update-heavy, você pode precisar encontrar outra maneira de manter esta coluna fresca.)
para obter uma amostra de 1000 itens de uma tabela, conto as linhas e a amostra do resultado até, em média, 10 000 linhas com a coluna frozen_rand:
SELECT COUNT(*) FROM table; -- Use this to determine rand_low and rand_high
SELECT *
FROM table
WHERE frozen_rand BETWEEN %(rand_low)s AND %(rand_high)s
ORDER BY RAND() LIMIT 1000
(Minha implementação real envolve mais trabalho para garantir que eu não subestime, e para enrolar manualmente o rand_high, mas a idéia básica é " aleatoriamente cortar seu N para baixo para alguns mil.")
Embora isto faça alguns sacrifícios, permite-me analisar a base de dados usando uma pesquisa de índice, até que seja pequena o suficiente para pedir pela RAND() novamente.
9 answers
Há uma chave única, indexada, primária na tabela
O número de linhas aleatórias que deseja seleccionar (m) é muito menor do que o número de linhas na tabela (n)
A chave primária única é um inteiro que varia de 1 A n, sem aberturas
Com apenas suposições 1 e 2 eu acho que isso pode ser feito em O(n), Embora você vai precisar escrever um índice inteiro para a tabela para igualar a suposição 3, por isso não é necessário um rápido O(n). Se nós pudermos adicionalmente assumir algo agradável sobre a mesa, nós podemos fazer a tarefa em O(M log m). Suposição 3 seria uma fácil agradável propriedade adicional para trabalhar. Com um bom gerador de números aleatórios que garante nenhuma duplicação ao gerar números m em uma linha, uma solução O(m) seria possível.
Dadas as três hipóteses, a ideia básica é gerar m números aleatórios únicos entre 1 e n, e depois seleccionar as linhas com essas chaves da tabela. Não tenho mysql nem nada à minha frente neste momento, por isso, em pseudocódigo ligeiramente, isto pareceria algo como:
create table RandomKeys (RandomKey int)
create table RandomKeysAttempt (RandomKey int)
-- generate m random keys between 1 and n
for i = 1 to m
insert RandomKeysAttempt select rand()*n + 1
-- eliminate duplicates
insert RandomKeys select distinct RandomKey from RandomKeysAttempt
-- as long as we don't have enough, keep generating new keys,
-- with luck (and m much less than n), this won't be necessary
while count(RandomKeys) < m
NextAttempt = rand()*n + 1
if not exists (select * from RandomKeys where RandomKey = NextAttempt)
insert RandomKeys select NextAttempt
-- get our random rows
select *
from RandomKeys r
join table t ON r.RandomKey = t.UniqueKey
Se você estava realmente preocupado com a eficiência, você poderia considerar fazer a geração chave aleatória em algum tipo de linguagem processual e inserir os resultados no banco de dados, como quase qualquer outra coisa além de SQL provavelmente seria melhor no tipo de looping e geração aleatória de números necessários.
Acho que a solução mais rápida é
select * from table where rand() <= .3
Eis a razão pela qual acho que isto deve servir.
- criará um número aleatório para cada linha. O número está entre 0 e 1
- ele avalia se deve mostrar essa linha se o número gerado está entre 0 e .3 (30%).
- Isto é O(n) mas não é necessária nenhuma ordenação por isso é mais rápido que o o (n lg n)
-
O Mysql é muito capaz de gerar números aleatórios para cada linha. Tenta isto.
Selecione rand () de INFORMATION_SCHEMA.Limite das tabelas 10;
Mais rápido que a ordem de RAND ()
Eu testei este método para ser muito mais rápido do que {[[2]}, por isso ele funciona em o(n) tempo, e faz isso impressionantemente rápido.
De http://technet.microsoft.com/en-us/library/ms189108%28v=sql.105%29.aspx:
Non-MSSQL version -- I did not test this
SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= RAND()
Versão MSSQL:
SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)
Isto irá seleccionar ~1% dos registos. Por isso, se precisar de um número exacto de percentagens ou registos a seleccionar, estimar a sua percentagem com alguma margem de segurança e, em seguida, arrancar aleatoriamente os registos de excesso do conjunto resultante, usando o método mais caro ORDER BY RAND()
.
Ainda Mais Rápido
Fui capaz de melhorar este método ainda mais porque tinha um conhecido intervalo de valores indexados.Por exemplo, se tiver uma coluna indexada com inteiros uniformemente distribuídos [0..max], você pode usar isso para selecionar aleatoriamente N pequenos intervalos. Faça isso dinamicamente em seu programa para obter um diferente definir para cada pesquisa executada. Esta seleção de subconjuntos será o (n) , que pode muitas ordens de magnitude menor do que o seu conjunto completo de dados.
No meu teste reduzi o tempo necessário para obter 20 (20 mil) registos de amostras de 3 minutos usando a ordem de RAND () para 0, 0 segundos!
Aparentemente em algumas versões do SQL há um comando {[[0]}, mas não está em todas as implementações do SQL (notavelmente, Redshift).
Http://technet.microsoft.com/en-us/library/ms189108 (V=sql.105).aspx
Usa apenas
WHERE RAND() < 0.1
Para obter 10% dos registos ou
WHERE RAND() < 0.01
Para obter 1% dos registos, etc.
Começando com a observação de que podemos recuperar os ids de uma tabela (por exemplo. Contagem 5) com base num conjunto:
select *
from table_name
where _id in (4, 1, 2, 5, 3)
Podemos chegar ao resultado de que se pudéssemos gerar a cadeia "(4, 1, 2, 5, 3)"
, então teríamos uma maneira mais eficiente do que RAND()
.
Por exemplo, em Java:
ArrayList<Integer> indices = new ArrayList<Integer>(rowsCount);
for (int i = 0; i < rowsCount; i++) {
indices.add(i);
}
Collections.shuffle(indices);
String inClause = indices.toString().replace('[', '(').replace(']', ')');
Se os ids têm lacunas, então o arraylist inicial indices
é o resultado de uma consulta sql sobre ids.
Se precisar exactamente m
linhas, realisticamente irá gerar o seu subconjunto de IDs fora do SQL. A maioria dos métodos requerem em algum ponto para selecionar a entrada "nth", e tabelas SQL realmente não são arrays em tudo. A suposição de que as chaves são consecutivas, a fim de apenas juntar ints aleatórios entre 1 e a contagem também é difícil de satisfazer - MySQL por exemplo não suporta nativamente, e as condições de bloqueio são... Complicado.
- obtenha todos os valores da coluna-chave da tabela de dados em qualquer ordem para uma lista na sua linguagem de programação favorita em
O(n)
- execute um baralhar Fisher-Yates , parando depois de swaps
m
, e extraia o subarray[0:m-1]
emϴ(m)
- "juntar" o subarray com o conjunto de dados original (por exemplo
SELECT ... WHERE id IN (<subarray>)
) emO(m lg n)
Qualquer método que gera o subconjunto Aleatório fora da SQL deve ter pelo menos isto complexidade. A junção não pode ser mais rápida do que O(m lg n)
com o BTREE (então O(m)
reivindicações são Fantasia para a maioria dos motores) e a baralha é limitada abaixo de n
e m lg n
e não afeta o comportamento assintótico.
Em pseudocódigo Pythónico:
ids = sql.query('SELECT id FROM t')
for i in range(m):
r = int(random() * (len(ids) - i))
ids[i], ids[i + r] = ids[i + r], ids[i]
results = sql.query('SELECT * FROM t WHERE id IN (%s)' % ', '.join(ids[0:m-1])
SELECT * FROM table LIMIT 10000 OFFSET FLOOR(RAND() * 190000)