Seleziona n righe casuali dalla tabella di SQL Server

Ho una tabella SQL Server con circa 50.000 righe al suo interno. Voglio selezionare circa 5.000 di quelle file a caso. Ho pensato a un modo complicato, creando una tabella temporanea con una colonna “numero casuale”, copiando la mia tabella in quella, scorrendo la tabella temporanea e aggiornando ogni riga con RAND() , quindi selezionando da quella tabella in cui il casuale colonna numero <0,1. Sto cercando un modo più semplice per farlo, in una singola dichiarazione, se possibile.

Questo articolo suggerisce di utilizzare la funzione NEWID() . Sembra promettente, ma non riesco a vedere come posso selezionare attendibilmente una certa percentuale di righe.

Qualcuno lo fa mai prima? Qualche idea?

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

In risposta al commento “Pure trash” relativo alle tabelle di grandi dimensioni: è ansible farlo in questo modo per migliorare le prestazioni.

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

Il costo di questo sarà la scansione chiave dei valori più il costo di join, che su una grande tabella con una piccola percentuale di selezione dovrebbe essere ragionevole.

A seconda delle tue esigenze, TABLESAMPLE ti offre prestazioni quasi casuali e migliori. questo è disponibile su MS SQL Server 2005 e versioni successive.

TABLESAMPLE restituirà i dati da pagine casuali invece di righe casuali e quindi non verrà nemmeno recuperato il dato che non verrà restituito.

Su un tavolo molto grande ho provato

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

ha impiegato più di 20 minuti.

 select * from [tablename] tablesample(1 percent) 

ha impiegato 2 minuti.

Le prestazioni miglioreranno anche su campioni più piccoli in TABLESAMPLE mentre non con newid() .

Tieni presente che questo metodo non è casuale come il metodo newid() , ma ti fornirà un campionamento decente.

Vedi la pagina MSDN .

newid () / order by funzionerà, ma sarà molto costoso per i set di risultati di grandi dimensioni perché deve generare un id per ogni riga e quindi ordinarli.

TABLESAMPLE () è buono dal punto di vista delle prestazioni, ma si otterranno grumi di risultati (verranno restituite tutte le righe su una pagina).

Per un campione casuale reale con prestazioni migliori, il modo migliore è filtrare le righe casualmente. Ho trovato il seguente codice di esempio nell’articolo della documentazione in linea di SQL Server Limitando i set di risultati utilizzando TABLESAMPLE :

Se vuoi veramente un campione casuale di singole righe, modifica la query per filtrare le righe casualmente, invece di usare TABLESAMPLE. Ad esempio, la seguente query utilizza la funzione NEWID per restituire circa l’uno percento delle righe della tabella Sales.SalesOrderDetail:

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

La colonna SalesOrderID è inclusa nell’espressione CHECKSUM in modo che NEWID () valuti una volta per riga per ottenere il campionamento per riga. L’espressione CAST (CHECKSUM (NEWID (), SalesOrderID) e 0x7fffffff AS float / CAST (0x7fffffff AS int) restituisce un valore float casuale compreso tra 0 e 1.

Quando corri contro un tavolo con 1.000.000 di file, ecco i miei risultati:

 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 riesci a farla franca con TABLESAMPLE, ti darà le migliori prestazioni. Altrimenti usa il metodo newid () / filter. newid () / order by dovrebbe essere l’ultima risorsa se si dispone di un ampio set di risultati.

La selezione casuale di righe da una tabella grande su MSDN presenta una soluzione semplice e ben articasting che risolve i problemi di prestazioni su larga scala.

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

Basta ordinare la tabella con un numero casuale e ottenere le prime 5.000 righe usando TOP .

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

AGGIORNARE

Ho appena provato e una chiamata newid() è sufficiente – non è necessario per tutti i cast e tutti i calcoli matematici.

Se tu (a differenza dell’OP) hai bisogno di un numero specifico di record (che rende l’approccio CHECKSUM difficile) e desideri un campione più casuale di TABLESAMPLE che fornisce da solo, e vuoi anche una velocità migliore di CHECKSUM, potresti accontentarti di una fusione del I metodi TABLESAMPLE e NEWID (), come questo:

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

Nel mio caso questo è il compromesso più diretto tra casualità (non lo è, lo so) e velocità. Variare la percentuale (o le righe) di TABLESAMPLE come appropriato: maggiore è la percentuale, più casuale è il campione, ma si prevede una diminuzione lineare della velocità. (Nota che TABLESAMPLE non accetta una variabile)

Questo collegamento presenta un interessante confronto tra Orderby (NEWID ()) e altri metodi per tabelle con 1, 7 e 13 milioni di righe.

Spesso, quando vengono poste domande su come selezionare righe casuali in gruppi di discussione, viene proposta la query NEWID; è semplice e funziona molto bene per i tavoli di piccole dimensioni.

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

Tuttavia, la query NEWID ha un grosso svantaggio quando la si utilizza per tabelle di grandi dimensioni. La clausola ORDER BY fa sì che tutte le righe della tabella vengano copiate nel database tempdb, dove vengono ordinate. Ciò causa due problemi:

  1. L’operazione di ordinamento di solito ha un costo elevato associato ad essa. L’ordinamento può utilizzare molti I / O del disco e può essere eseguito per un lungo periodo.
  2. Nella peggiore delle ipotesi, tempdb può esaurire lo spazio. Nel migliore dei casi, tempdb può occupare una grande quantità di spazio su disco che non verrà mai recuperato senza un comando di restringimento manuale.

Quello di cui hai bisogno è un modo per selezionare casualmente le righe che non usano tempdb e non diventeranno molto più lente man mano che la tabella si ingrandisce. Ecco una nuova idea su come farlo:

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

L'idea alla base di questa query è che vogliamo generare un numero casuale compreso tra 0 e 99 per ogni riga della tabella, quindi scegliere tutte quelle righe il cui numero casuale è inferiore al valore della percentuale specificata. In questo esempio, vogliamo circa il 10 percento delle righe selezionate casualmente; pertanto, scegliamo tutte le righe il cui numero casuale è inferiore a 10.

Si prega di leggere l'articolo completo in MSDN .

In MySQL puoi fare questo:

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

Questa è una combinazione dell’idea iniziale di seme e un checksum, che mi sembra dare risultati casuali senza il costo di NEWID ():

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

Prova questo:

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

Non ho ancora visto questa variazione nelle risposte. Avevo un ulteriore vincolo in cui avevo bisogno, dato un seed iniziale, di selezionare lo stesso set di righe ogni volta.

Per MS SQL:

Esempio minimo:

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

Tempo di esecuzione normalizzato: 1.00

NewId () esempio:

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

Tempo di esecuzione normalizzato: 1.02

NewId() è insignificativamente più lento di rand(checksum(*)) , quindi potresti non volerlo usare contro i set di record più grandi.

Selezione con seme iniziale:

 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 è necessario selezionare lo stesso set dato un seme, questo sembra funzionare.

Questo funziona per me:

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

Sembra che newid () non possa essere usato nella clausola where, quindi questa soluzione richiede una query interna:

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

Lo stavo usando in sottoquery e mi ha restituito le stesse righe in subquery

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

poi ho risolto con l’inclusione della variabile della tabella padre in dove

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

Nota il punto in cui condrizione

Il linguaggio di elaborazione lato server in uso (es. PHP, .net, ecc.) Non è specificato, ma se è PHP, prendi il numero richiesto (o tutti i record) e invece di randomizzare nella query usa la funzione shuffle di PHP. Non so se .net abbia una funzione equivalente, ma se lo fa usa questo se stai usando .net

ORDINA DA RAND () può avere una penalità di prestazioni piuttosto, a seconda di quanti record sono coinvolti.