Sélectionner n lignes aléatoires dans une table SQL Server

Question

J'ai une table SQL Server qui contient environ 50 000 lignes. Je veux sélectionner environ 5 000 de ces lignes de manière aléatoire. J'ai pensé à une méthode compliquée, en créant une table temporaire avec une colonne "nombre aléatoire", en copiant ma table dans cette dernière, en parcourant en boucle la table temporaire et en mettant à jour chaque ligne avec la commande RAND() puis de sélectionner dans ce tableau les cas où la colonne de nombres aléatoires est < 0,1. Je cherche un moyen plus simple de le faire, en une seule instruction si possible.

Cet article suggère d'utiliser le NEWID() fonction. Cela semble prometteur, mais je ne vois pas comment je pourrais sélectionner de manière fiable un certain pourcentage de lignes.

Quelqu'un a déjà fait ça avant ? Des idées ?

Answer 1

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

En réponse au commentaire "pure poubelle" concernant les grandes tables : vous pouvez procéder de cette manière pour améliorer les performances.

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

Le coût de cette opération correspondra au balayage clé des valeurs plus le coût de la jointure, ce qui, sur une grande table avec un petit pourcentage de sélection, devrait être raisonnable.

Answer 2

En fonction de vos besoins, TABLESAMPLE vous obtiendrez des résultats presque aussi aléatoires et de meilleures performances. Ceci est disponible sur MS SQL server 2005 et plus.

TABLESAMPLE renverra des données provenant de pages aléatoires plutôt que de lignes aléatoires et ne récupérera donc même pas les données qu'il ne retournera pas.

Sur une très grande table, j'ai testé

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

a pris plus de 20 minutes.

select * from [tablename] tablesample(1 percent)

a pris 2 minutes.

Les performances s'amélioreront également sur des échantillons plus petits dans TABLESAMPLE alors qu'il ne le fera pas avec newid() .

Veuillez garder à l'esprit que ce n'est pas aussi aléatoire que le newid() mais vous obtiendrez un bon échantillon.

Voir le Page MSDN .

Answer 3

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

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

Pour obtenir un échantillon aléatoire réel 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 SQL Server Books Online Limiter les ensembles de résultats en utilisant TABLESAMPLE :

Si vous voulez vraiment un échantillon aléatoire de lignes individuelles, modifiez votre requête pour filtrer les lignes de façon aléatoire, au lieu utiliser TABLESAMPLE. Par exemple, la requête requête suivante utilise la fonction NEWID pour renvoyer 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 l'expression CHECKSUM afin que NEWID() est évaluée une fois par ligne à afin de réaliser un échantillonnage par ligne. L'expression CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) donne comme résultat une valeur flottante aléatoire comprise entre 0 et 1.

Lorsque je l'exécute sur une table de 1 000 000 de 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 pouvez vous en sortir en utilisant TABLESAMPLE, vous obtiendrez les meilleures performances. Sinon, utilisez la méthode newid()/filter. newid()/order by devrait être le dernier recours si vous avez un grand ensemble de résultats.

Answer 4

Sélection aléatoire de rangs dans un grand tableau sur MSDN propose une solution simple et bien articulée qui répond aux problèmes de performance à grande échelle.

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

Answer 5

Si (contrairement à l'OP) vous avez besoin d'un nombre spécifique d'enregistrements (ce qui rend l'approche CHECKSUM difficile) et que vous désirez un échantillon plus aléatoire que ce que TABLESAMPLE fournit par lui-même, et que vous voulez aussi une meilleure vitesse que CHECKSUM, vous pouvez vous contenter d'une fusion des méthodes TABLESAMPLE et NEWID(), comme ceci :

DECLARE @sampleCount int = 50
SET STATISTICS TIME ON

SELECT TOP (@sampleCount) * 
FROM [yourtable] TABLESAMPLE(10 PERCENT)
ORDER BY NEWID()

SET STATISTICS TIME OFF

Dans mon cas, c'est le compromis le plus simple entre le caractère aléatoire (ce n'est pas vraiment le cas, je sais) et la rapidité. Faites varier le pourcentage (ou les lignes) de TABLESAMPLE comme il convient - plus le pourcentage est élevé, plus l'échantillon est aléatoire, mais attendez-vous à une baisse linéaire de la vitesse. (Notez que TABLESAMPLE n'acceptera pas de variable)