Question Comment demander une ligne aléatoire en SQL?


Comment puis-je demander une ligne aléatoire (ou aussi aléatoire que possible) en SQL pur?


438
2017-08-21 06:28


origine


Réponses:


Voir ce post: SQL pour sélectionner une ligne aléatoire à partir d'une table de base de données. Cela passe par des méthodes pour cela dans MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 et Oracle (ce qui suit est copié à partir de ce lien):

Sélectionnez une rangée aléatoire avec MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Sélectionnez une ligne aléatoire avec PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Sélectionnez une ligne aléatoire avec Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Sélectionnez une ligne aléatoire avec IBM DB2

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

Sélectionnez un enregistrement aléatoire avec Oracle:

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

621
2017-08-21 06:32



Des solutions comme Jeremies:

SELECT * FROM table ORDER BY RAND() LIMIT 1

fonctionnent, mais ils ont besoin d'un balayage séquentiel de toute la table (car la valeur aléatoire associée à chaque ligne doit être calculée - de sorte que la plus petite puisse être déterminée), ce qui peut être assez lent pour les tables de taille moyenne. Ma recommandation serait d'utiliser une sorte de colonne numérique indexée (de nombreuses tables ont ces clés primaires), puis d'écrire quelque chose comme:

SELECT * FROM table WHERE num_value >= RAND() * 
    ( SELECT MAX (num_value ) FROM table ) 
ORDER BY num_value LIMIT 1

Cela fonctionne en temps logarithmique, indépendamment de la taille de la table, si num_value est indexé. Une mise en garde: cela suppose que num_value est également distribué dans la gamme 0..MAX(num_value). Si votre jeu de données s'écarte fortement de cette hypothèse, vous obtiendrez des résultats faussés (certaines lignes apparaîtront plus souvent que d'autres).


169
2017-08-21 06:37



Je ne sais pas si c'est efficace, mais je l'ai déjà utilisé:

SELECT TOP 1 * FROM MyTable ORDER BY newid()

Parce que les GUID sont assez aléatoires, la commande signifie que vous obtenez une ligne aléatoire.


59
2017-08-21 06:30



ORDER BY NEWID()

prend 7.4 milliseconds

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

prend 0.0065 milliseconds!

Je vais certainement aller avec la dernière méthode.


22
2017-12-21 07:57



Vous n'avez pas dit quel serveur vous utilisez. Dans les anciennes versions de SQL Server, vous pouvez utiliser ceci:

select top 1 * from mytable order by newid()

Dans SQL Server 2005 et versions ultérieures, vous pouvez utiliser TABLESAMPLE pour obtenir un échantillon aléatoire répétable:

SELECT FirstName, LastName
FROM Contact 
TABLESAMPLE (1 ROWS) ;

13
2017-08-21 06:30



Pour SQL Server

newid () / order by fonctionnera, mais sera très coûteux pour les grands ensembles de résultats car il doit générer un identifiant pour chaque ligne, puis les trier.

TABLESAMPLE () est bien du point de vue des performances, mais vous obtiendrez des résultats groupés (toutes les lignes d'une page seront renvoyées).

Pour un échantillon aléatoire plus performant, le meilleur moyen est de filtrer les lignes de manière aléatoire. J'ai trouvé l'exemple de code suivant dans l'article de la documentation en ligne de SQL Server Limitation des ensembles de résultats à l'aide de TABLESAMPLE:

Si vous voulez vraiment un échantillon aléatoire de   lignes individuelles, modifiez votre requête pour   filtrer les lignes aléatoirement, au lieu de   en utilisant TABLESAMPLE. Par exemple, le   requête suivante utilise le NEWID   fonction pour retourner environ un   pour cent des lignes de la   Table Sales.SalesOrderDetail:

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

La colonne SalesOrderID est incluse dans   l'expression CHECKSUM pour que   NEWID () évalue une fois par ligne   réaliser un échantillonnage par rangée.   L’expression CAST (CHECKSUM (NEWID (),   SalesOrderID) & 0x7fffffff AS float /   CAST (0x7fffffff AS int) évalue à   une valeur flottante aléatoire entre 0 et 1.

Lorsqu'il est exécuté sur une table contenant 1 000 000 lignes, voici mes résultats:

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

Si vous parvenez à utiliser TABLESAMPLE, cela vous donnera les meilleures performances. Sinon, utilisez la méthode newid () / filter. newid () / order by devrait être en dernier recours si vous avez un grand ensemble de résultats.


10
2018-05-28 18:23



Si possible, utilisez des instructions stockées pour éviter l'inefficacité des deux index sur RND () et créer un champ de numéro d'enregistrement.

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

4
2018-01-09 06:49



Le meilleur moyen consiste à placer une valeur aléatoire dans une nouvelle colonne uniquement à cette fin, et à utiliser quelque chose comme ceci (code pseudo + SQL):

randomNo = random()
execSql("SELECT TOP 1 * FROM MyTable WHERE MyTable.Randomness > $randomNo")

C'est la solution employée par le code MediaWiki. Bien sûr, il y a un certain parti pris contre les valeurs plus petites, mais ils ont trouvé qu'il suffisait d'envelopper la valeur aléatoire autour de zéro lorsqu'aucune ligne n'est extraite.

La solution newid () peut nécessiter une analyse complète de la table afin que chaque ligne puisse se voir attribuer un nouveau guid, qui sera beaucoup moins performant.

La solution rand () peut ne pas fonctionner du tout (c'est-à-dire avec MSSQL) car la fonction ne sera évaluée qu'une seule fois, et chaque rangée sera attribué le même nombre "aléatoire".


3
2017-08-21 06:36



Pour SQL Server 2005 et 2008, si nous voulons un échantillon aléatoire de lignes individuelles (à partir de Livres en ligne):

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

3
2017-10-08 12:56



Au lieu de en utilisant RAND (), car il n'est pas encouragé, vous pouvez simplement obtenir max ID (= Max):

SELECT MAX(ID) FROM TABLE;

obtenir un aléatoire entre 1..Max (= My_Generated_Random)

My_Generated_Random = rand_in_your_programming_lang_function(1..Max);

puis exécutez ce SQL:

SELECT ID FROM TABLE WHERE ID >= My_Generated_Random ORDER BY ID LIMIT 1

Notez qu'il vérifiera les lignes dont les identifiants sont égaux ou supérieurs à la valeur choisie. Il est également possible de rechercher la ligne dans la table et d'obtenir un identifiant égal ou inférieur à My_Generated_Random, puis de modifier la requête comme suit:

SELECT ID FROM TABLE WHERE ID <= My_Generated_Random ORDER BY ID DESC LIMIT 1

3
2018-03-10 16:19