Sélectionnez n lignes aléatoires à partir de SQL Server le tableau
J'ai une table SQL Server avec environ 50 000 lignes. Je veux choisir d'environ 5 000 de ces lignes au hasard. J'ai pensé à une manière compliquée, la création d'une table temporaire avec un "nombre aléatoire" colonne, la copie de mon tableau dans le, une boucle dans la table temporaire et la mise à jour de chaque ligne avec RAND()
, puis en sélectionnant cette table où le nombre aléatoire colonne < 0.1. Je suis à la recherche d'un moyen plus simple de le faire dans un seul état si possible.
Cet article vous suggérons d'utiliser le NEWID()
fonction. Qui semble prometteur, mais je ne vois pas comment je pourrais fiable de sélectionner un certain pourcentage de lignes.
Personne n'a jamais le faire avant? Des idées?
- MSDN a un bon article qui couvre un grand nombre de ces questions: la Sélection de Lignes au Hasard à partir d'un Tableau de Grande taille
- Double Possible de Comment demander une ligne au hasard dans SQL?
Vous devez vous connecter pour publier un commentaire.
En réponse à la "pure trash" commentaires au sujet des grandes tables: vous pouvez le faire comme ceci pour améliorer les performances.
Le coût de ce sera la clé d'analyse des valeurs plus la jointure des coûts, qui, sur une grande table avec un petit pourcentage de sélection devrait être raisonnable.
[yourPk]
référence? EDIT: Nvm, pensé à elle... Clé Primaire. Durrrnewid()
Sorte Estimation des I/O du coût sera très élevé et aura un effet sur les performances.En fonction de vos besoins,
TABLESAMPLE
, vous obtiendrez presque aussi aléatoire et de meilleures performances.il est disponible sur MS SQL server 2005 et versions ultérieures.
TABLESAMPLE
renverra les données à partir de pages au hasard au lieu de lignes aléatoires et donc aid même pas récupérer les données qu'il ne reviendra pas.Sur une très grande table, j'ai testé
a fallu plus de 20 minutes.
a pris 2 minutes.
Performance permettra également d'améliorer sur de plus petits échantillons dans
TABLESAMPLE
considérant qu'il ne sera pas avecnewid()
.Veuillez garder à l'esprit que ce n'est pas aussi aléatoire que le
newid()
méthode, mais vous donnera un décent de l'échantillonnage.Voir le Page MSDN.
newid()/de la commande par fonctionner, mais il sera très coûteux pour les grands ensembles de résultats, car il doit générer un id pour chaque ligne, puis de les trier.
TABLESAMPLE() est bonne d'un point de vue des performances, mais vous aurez l'agrégation de résultats (toutes les lignes sur une page sera retourné).
Pour l'amélioration des performances vrai aléatoire de l'échantillon, le meilleur moyen est de filtrer les lignes au hasard. J'ai trouvé l'exemple de code suivant dans la documentation en Ligne SQL Server de l'article Limiter les Ensembles de Résultats en Utilisant TABLESAMPLE:
Lorsqu'il est exécuté sur une table avec 1 000 000 de lignes, voici mes résultats:
Si vous pouvez vous en sortir avec l'aide de TABLESAMPLE, il vous donnera les meilleures performances. Sinon, utiliser la fonction newid()/méthode de filtrage. newid()/de la commande par devrait être le dernier recours, si vous avez un grand ensemble de résultats.
NewID()
est évaluée qu'une seule fois, au lieu de par ligne, que je n'aime pas...La sélection de Lignes au Hasard à partir d'une Grande Table sur MSDN est simple, bien articulé solution qui traite de la performance à grande échelle des préoccupations.
RAND()
ne retourne pas la même valeur pour chaque ligne (qui irait à l'encontre de laBINARY_CHECKSUM()
logique). Est-ce parce que c'est d'être appelée à l'intérieur d'une autre fonction, plutôt que de faire partie de la clause SELECT?rand()
ou une combinaison de ceux-ci - mais je me suis détourné de cette solution pour cette raison. Aussi le nombre de résultats varient de 1 à 5, donc cela pourrait également ne pas être acceptable dans certains scénarios.RAND()
renvoie la même valeur pour chaque ligne (c'est pourquoi cette solution est rapide). Cependant, les lignes avec les binaires de sommes qui sont très proches sont à haut risque de générer similaires de la somme de contrôle des résultats, provoquant l'agglutination quandRAND()
est petit. E. g.,(ABS(CAST((BINARY_CHECKSUM(111,null,null) * 0.1) as int))) % 100
==SELECT (ABS(CAST((BINARY_CHECKSUM(113,null,null) * 0.1) as int))) % 100
. Si vos données souffre de ce problème, multiplierBINARY_CHECKSUM
par 9923.Si vous (à la différence de l'OP) besoin d'un certain nombre de dossiers (ce qui fait que la somme de contrôle d'approche difficile) et le désir d'un échantillon plus aléatoire que TABLESAMPLE fournit par lui-même, et qui souhaitent également améliorer la vitesse de somme de contrôle, vous pouvez le faire avec une fusion de la TABLESAMPLE et NEWID() méthodes, comme ceci:
Dans mon cas, c'est le plus simple compromis entre l'aléatoire (ce n'est pas vraiment, je sais) et de la vitesse. Varier les TABLESAMPLE pourcentage (ou lignes) le cas échéant, plus le pourcentage est élevé, plus aléatoire de l'échantillon, mais s'attendre à un linéaire de la baisse de la vitesse. (Notez que TABLESAMPLE n'acceptera pas une variable)
Ce lien avoir une comparaison intéressante entre Orderby(NEWID()) et d'autres méthodes pour les tableaux 1, 7 et 13 millions de lignes.
Souvent, lorsque des questions sur la façon de sélectionner des lignes aléatoires, il est demandé aux groupes de discussion, la fonction NEWID requête est proposé; il est simple et fonctionne très bien pour de petites tables.
Cependant, la fonction NEWID requête a un gros inconvénient lorsque vous l'utilisez pour les grandes tables. La clause ORDER BY causes de toutes les lignes de la table pour être copiés dans la base de données tempdb, où ils sont triés. Cela pose deux problèmes:
Le tri peut utiliser beaucoup d'I/O disque et peut fonctionner pendant une longue période.
meilleur des cas, la base de données tempdb peut prendre jusqu'à une grande quantité d'espace disque
qui jamais ne se fera sans un manuel de commande de réduction.
Ce que vous avez besoin est un moyen de sélectionner des lignes au hasard que de ne pas utiliser la base de données tempdb et ne sera pas obtenir beaucoup plus lent que le tableau devient plus grand. Voici une nouvelle idée sur la façon de le faire:
L'idée de base de cette requête, c'est que nous voulons générer un nombre aléatoire entre 0 et 99 pour chaque ligne du tableau, puis sélectionnez toutes les lignes dont le nombre aléatoire est inférieur à la valeur spécifiée pour cent. Dans cet exemple, nous voulons environ 10 pour cent des lignes sélectionnées au hasard; c'est pourquoi, nous avons choisi toutes les lignes dont le nombre aléatoire est inférieur à 10.
Veuillez lire l'intégralité de l'article dans MSDN.
Il suffit de commander la table par un nombre aléatoire et obtenir les premiers 5 000 lignes à l'aide de
TOP
.Mise à JOUR
L'ai essayé et un
newid()
appel est suffisante, pas besoin de tous les moulages et les maths.C'est une combinaison de la graine initiale idée et une somme de contrôle, qui ressemble pour moi à donner correctement les résultats aléatoires sans le coût de la fonction NEWID():
Dans MySQL, vous pouvez faire ceci:
N'arrive pas à voir cette variation dans les réponses à ce jour. J'avais une contrainte supplémentaire où j'ai besoin, étant donné une graine initiale, pour sélectionner le même ensemble de lignes à chaque fois.
Pour MS SQL:
Minimum exemple:
Normalisé temps d'exécution: 1.00
NewId() exemple:
Normalisé temps d'exécution: 1.02
NewId()
est pas significativement plus lent querand(checksum(*))
, de sorte que vous pouvez ne pas vouloir l'utiliser contre les grands ensembles d'enregistrements.Sélection avec la Graine Initiale:
Si vous devez sélectionner le même ensemble donné une graine, cela semble fonctionner.
Essayez ceci:
Il semble newid() ne peut pas être utilisée dans la clause where, de sorte que cette solution nécessite une requête interne:
Je l'utilise dans la sous-requête et il me fit même des lignes de sous-requête
puis j'ai résolu avec notamment parent variable de table où
Note de l'endroit où condtition
Du côté serveur, le traitement de la langue en cours d'utilisation (par exemple PHP, .net, etc) n'est pas spécifié, mais si c'est du PHP, saisir le nombre requis (ou tous les enregistrements) et à la place de l'aléatoire dans l'utilisation de la requête PHP la fonction de lecture aléatoire. Je ne sais pas si .net a une fonction équivalente, mais si c'est le cas alors l'utiliser si vous êtes en utilisant .net
ORDER BY RAND() pourra avoir une perte de performance, en fonction du nombre de dossiers sont impliqués.
Cela fonctionne pour moi:
select top 10 percent from table_name order by rand()
, mais qui ne fonctionne pas parce que rand() renvoie la même valeur sur toutes les lignes.