Enregistrement aléatoire à partir d'une table de base de données (T-SQL)

Question

Est-il une manière succincte pour récupérer un enregistrement aléatoire à partir d'une table sql server?

Je tiens à rendre aléatoire de mon unité de données de test, donc suis à la recherche d'un moyen simple de sélectionner un id aléatoire à partir d'une table. En anglais, le sélectionner serait "Sélectionnez un id de la table où l'id est un nombre aléatoire entre le plus bas de l'id dans la table et les id les plus élevés dans la table".

Je ne peux pas trouver un moyen de le faire sans avoir à exécuter la requête, test pour une valeur nulle, alors re-exécuter si la valeur est null.

Des idées?

Answer 1

Est-il une manière succincte pour récupérer un enregistrement aléatoire à partir d'une table sql server?

Oui

SELECT TOP 1 * FROM table ORDER BY NEWID()

Explication

Un NEWID() est généré pour chaque ligne et la table est triée par elle. Le premier enregistrement est retourné (c'est à dire l'enregistrement avec le "plus" GUID).

Notes

1. Les guid sont générés en tant que de nombres pseudo-aléatoires à partir de la version quatre:

   La version 4 de l'UUID est conçu pour générer des Uuid de vraiment aléatoire ou de nombres pseudo-aléatoires.

   L'algorithme est le suivant:

   • Ensemble, les deux bits les plus significatifs (bits 6 et 7) de la clock_seq_hi_and_reserved à zéro et un, respectivement.
   • Définissez les quatre bits les plus significatifs (les bits 12 à 15) de la time_hi_and_version champ de 4 bits numéro de version de La Section 4.1.3.
   • Ensemble de tous les autres bits de façon aléatoire (ou pseudo-aléatoire) choisi des valeurs.

   —Un Identificateur Unique universel (UUID) URN Namespace - RFC 4122

2. L'alternative SELECT TOP 1 * FROM table ORDER BY RAND() ne fonctionne pas comme on pourrait le penser. RAND() renvoie une valeur unique par requête, ainsi que toutes les lignes aient la même valeur.

3. Alors que les GUID des valeurs pseudo-aléatoires, vous aurez besoin d'une meilleure PRNG pour les applications les plus exigeantes.

4. La performance typique est de moins en moins de 10 secondes environ 1 000 000 de lignes de cours en fonction du système. Notez qu'il est impossible de toucher un indice, donc les performances seront relativement limitées.

Answer 2

Sur les plus grandes tables vous pouvez également utiliser TABLESAMPLE pour ceci afin d'éviter la numérisation de l'ensemble de la table.

SELECT  TOP 1 *
FROM YourTable
TABLESAMPLE (1000 ROWS)
ORDER BY NEWID()

L' ORDER BY NEWID est toujours nécessaire pour éviter tout juste de retour des lignes qui apparaissent en premier sur la page de données.

Le numéro à utiliser doit être choisi avec soin pour la définition et la taille de la table et vous pourriez envisager logique de nouvelle tentative si aucune ligne n'est renvoyée. Les maths derrière cela et pourquoi la technique n'est pas adaptée à de petites tables est discuté ici

Answer 3

Essayez également votre méthode pour obtenir un identifiant aléatoire entre MIN (Id) et MAX (Id), puis

 SELECT TOP 1 * FROM table WHERE Id >= @yourrandomid
 

Il vous aura toujours une rangée.

Answer 4

si vous voulez sélectionner des données volumineuses, le meilleur moyen que j'ai trouvé est SELECT * FROM Table1 WHERE (ABS(CAST( (BINARY_CHECKSUM (keycol1, NEWID())) as int)) % 100) < 10

MSDN

Answer 5

Je cherchais à améliorer les méthodes que j'avais essayées et rencontrées dans ce post. Je réalise que c'est vieux mais cette méthode n'est pas listée. Je crée et applique des données de test; cela montre la méthode pour "adresse" dans un SP appelé avec @st (état à deux caractères)

 Create Table ##TmpAddress (id Int Identity(1,1), street VarChar(50), city VarChar(50), st VarChar(2), zip VarChar(5))
Insert Into ##TmpAddress(street, city, st, zip)
Select street, city, st, zip 
From tbl_Address (NOLOCK)
Where st = @st


-- unseeded RAND() will return the same number when called in rapid succession so
-- here, I seed it with a guaranteed different number each time. @@ROWCOUNT is the count from the most recent table operation.

Set @csr = Ceiling(RAND(convert(varbinary, newid())) * @@ROWCOUNT)

Select street, city, st, Right(('00000' + ltrim(zip)),5) As zip
From ##tmpAddress (NOLOCK)
Where id = @csr