number - sql server set random value




Come richiedere una riga casuale in SQL? (19)

Come posso richiedere una riga casuale (o il più vicino possibile al caso reale) in puro SQL?


C'è una soluzione migliore per Oracle invece di usare dbms_random.value, mentre richiede la scansione completa per ordinare le righe di dbms_random.value ed è piuttosto lento per le tabelle di grandi dimensioni.

Usa questo invece:

SELECT *
FROM employee sample(1)
WHERE rownum=1

Come sottolineato nel commento di @ BillKarwin sulla risposta di @ cnu ...

Quando combino con un LIMIT, ho trovato che funziona molto meglio (almeno con PostgreSQL 9.1) per JOIN con un ordinamento casuale piuttosto che per ordinare direttamente le righe effettive: ad es.

SELECT * FROM tbl_post AS t
JOIN ...
JOIN ( SELECT id, CAST(-2147483648 * RANDOM() AS integer) AS rand
       FROM tbl_post
       WHERE create_time >= 1349928000
     ) r ON r.id = t.id
WHERE create_time >= 1349928000 AND ...
ORDER BY r.rand
LIMIT 100

Assicurati che la 'r' generi un valore 'rand' per ogni possibile valore chiave nella query complessa che è unita ad esso, ma continua a limitare il numero di righe di 'r', ove possibile.

CAST come Integer è particolarmente utile per PostgreSQL 9.2 che ha un'ottimizzazione di ordinamento specifica per tipi interi e di precisione a virgola mobile.


Devo concordare con CD-MAN: l'uso di "ORDER BY RAND ()" funzionerà bene per i tavoli di piccole dimensioni o quando si esegue solo la SELECT alcune volte.

Io uso anche la tecnica "num_value> = RAND () * ...", e se voglio davvero avere risultati casuali ho una speciale colonna "random" nella tabella che aggiorno una volta al giorno o giù di lì. Quella singola esecuzione di UPDATE richiederà del tempo (specialmente perché sarà necessario avere un indice su quella colonna), ma è molto più veloce della creazione di numeri casuali per ogni riga ogni volta che viene eseguita la selezione.


Fai attenzione perché TableSample non restituisce effettivamente un campione casuale di righe. Indirizza la tua query per esaminare un campione casuale delle pagine da 8 KB che compongono la tua riga. Quindi, la query viene eseguita rispetto ai dati contenuti in queste pagine. A causa di come i dati possono essere raggruppati su queste pagine (ordine di inserimento, ecc.), Ciò potrebbe portare a dati che in realtà non sono un campione casuale.

Vedi: http://www.mssqltips.com/tip.asp?tip=1308

Questa pagina MSDN per TableSample include un esempio di come generare un effettivo campione casuale di dati.

msdn.microsoft.com/en-us/library/ms189108.aspx


In MSSQL (testato su 11.0.5569) utilizzando

SELECT TOP 100 * FROM employee ORDER BY CRYPT_GEN_RANDOM(10)

è significativamente più veloce di

SELECT TOP 100 * FROM employee ORDER BY NEWID()

In SQL Server puoi combinare TABLESAMPLE con NEWID () per ottenere casualità e avere comunque velocità. Ciò è particolarmente utile se si desidera solo 1 o un numero limitato di righe.

SELECT TOP 1 * FROM [table] 
TABLESAMPLE (500 ROWS) 
ORDER BY NEWID()

La funzione casuale di sql potrebbe aiutare. Inoltre, se vuoi limitare a una sola riga, aggiungila alla fine.

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

La maggior parte delle soluzioni qui mira a evitare l'ordinamento, ma devono comunque eseguire una scansione sequenziale su un tavolo.

C'è anche un modo per evitare la scansione sequenziale passando alla scansione indice. Se conosci il valore dell'indice della tua riga casuale, puoi ottenere il risultato quasi istantaneamente. Il problema è - come indovinare un valore di indice.

La seguente soluzione funziona su PostgreSQL 8.4:

explain analyze select * from cms_refs where rec_id in 
  (select (random()*(select last_value from cms_refs_rec_id_seq))::bigint 
   from generate_series(1,10))
  limit 1;

Io sopra la soluzione indichiamo 10 diversi valori di indice casuali dall'intervallo 0 .. [ultimo valore di id].

Il numero 10 è arbitrario: puoi usare 100 o 1000 poiché (incredibilmente) non ha un grande impatto sul tempo di risposta.

C'è anche un problema: se hai degli ID sparsi potresti perdere . La soluzione consiste nell'avere un piano di backup :) In questo caso, una query di tipo old order by random () pura. Quando l'ID combinato appare in questo modo:

explain analyze select * from cms_refs where rec_id in 
    (select (random()*(select last_value from cms_refs_rec_id_seq))::bigint 
     from generate_series(1,10))
    union all (select * from cms_refs order by random() limit 1)
    limit 1;

Non la clausola union ALL . In questo caso se la prima parte restituisce dati, la seconda non viene MAI eseguita!


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.


Non so quanto sia efficiente, ma l'ho usato prima:

SELECT TOP 1 * FROM MyTable ORDER BY newid()

Poiché i GUID sono piuttosto casuali, l'ordinamento significa che ottieni una riga casuale.



Per SQL Server 2005 e 2008, se si desidera un campione casuale di singole righe (dalla documentazione in msdn.microsoft.com/en-us/library/ms189108.aspx ):

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

Per SQL Server

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 msdn.microsoft.com/en-us/library/ms189108.aspx :

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.


Puoi anche provare a utilizzare la new id() .

Basta scrivere una query e utilizzare l'ordine con la new id() . E 'abbastanza casuale.


Se possibile, utilizzare le istruzioni memorizzate per evitare l'inefficienza di entrambi gli indici su RND () e la creazione di un campo del numero di record.

PREPARE RandomRecord FROM "SELECT * FROM table LIMIT ?,1";
SET @n=FLOOR(RAND()*(SELECT COUNT(*) FROM table));
EXECUTE RandomRecord USING @n;

Sembra che molte delle idee elencate continuino ad essere ordinate

Tuttavia, se si utilizza una tabella temporanea, è possibile assegnare un indice casuale (come suggerito da molte soluzioni), quindi afferrare il primo che è maggiore di un numero arbitrario compreso tra 0 e 1.

Ad esempio (per DB2):

WITH TEMP AS (
SELECT COMLUMN, RAND() AS IDX FROM TABLE)
SELECT COLUMN FROM TABLE WHERE IDX > .5
FETCH FIRST 1 ROW ONLY


Vedi questo post: SQL per selezionare una riga casuale da una tabella di database . Passa attraverso i metodi per farlo in MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 e Oracle (il seguente è copiato da quel collegamento):

Seleziona una riga casuale con MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Seleziona una riga casuale con PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Seleziona una riga casuale con Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Selezionare una riga casuale con IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Seleziona un record casuale con Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

ORDER BY NEWID()

richiede 7.4 milliseconds

WHERE num_value >= RAND() * (SELECT MAX(num_value) FROM table)

prende 0.0065 milliseconds !

Sicuramente andrò con il secondo metodo.





random