Comment générer un numéro aléatoire pour chaque ligne dans une sélection T-SQL ?

Question

J'ai besoin d'un numéro aléatoire différent pour chaque ligne de mon tableau. Le code suivant, qui semble évident, utilise la même valeur aléatoire pour chaque ligne.

SELECT table_name, RAND() magic_number 
FROM information_schema.tables 

J'aimerais obtenir un INT ou un FLOAT. La suite de l'histoire est que je vais utiliser ce nombre aléatoire pour créer une date aléatoire décalée par rapport à une date connue, par exemple un décalage de 1 à 14 jours par rapport à une date de départ.

Ceci est pour Microsoft SQL Server 2000.

Answer 1

Jetez un coup d'œil à SQL Server - Numéros aléatoires basés sur un ensemble qui contient une explication très détaillée.

Pour résumer, le code suivant génère un nombre aléatoire compris entre 0 et 13 inclus avec une distribution uniforme :

ABS(CHECKSUM(NewId())) % 14

Pour modifier votre plage, il suffit de changer le nombre à la fin de l'expression. Faites très attention si vous avez besoin d'une plage qui inclut à la fois des nombres positifs et négatifs. Si vous vous y prenez mal, il est possible de compter deux fois le nombre 0.

Un petit avertissement pour les fous de maths dans la salle : il y a un très léger biais dans ce code. CHECKSUM() donne des résultats uniformes sur toute la plage du type de données sql Int, ou du moins aussi proches que mes tests (ceux de l'éditeur) peuvent le montrer. Cependant, il y aura un certain biais lorsque CHECKSUM() produit un nombre à l'extrémité supérieure de cette plage. Chaque fois que vous obtenez un nombre entre le nombre entier maximum possible et le dernier multiple exact de la taille de votre plage souhaitée (14 dans ce cas) avant ce nombre entier maximum, ces résultats sont favorisés par rapport à la partie restante de votre plage qui ne peut pas être produite à partir de ce dernier multiple de 14.

Par exemple, imaginez que la plage entière du type Int est seulement 19. 19 est le plus grand nombre entier possible que vous pouvez détenir. Lorsque CHECKSUM() donne un résultat de 14 à 19, cela correspond aux résultats 0 à 5. Ces nombres seraient lourdement favorisé par rapport à 6-13, car CHECKSUM() a deux fois plus de chances de les générer. Il est plus facile de démontrer cela visuellement. Vous trouverez ci-dessous l'ensemble des résultats possibles pour notre gamme d'entiers imaginaires :

Checksum Integer: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Range Result:     0 1 2 3 4 5 6 7 8 9 10 11 12 13  0  1  2  3  4  5

Vous pouvez voir ici qu'il y a plus de chances de produire certains nombres que d'autres : le biais. Heureusement, l'étendue réelle du type Int est de beaucoup plus large... à tel point que dans la plupart des cas, le biais est presque indétectable. Cependant, c'est quelque chose dont il faut être conscient si vous vous retrouvez un jour à faire cela pour un code de sécurité sérieux.

Answer 2

Lorsqu'il est appelé plusieurs fois dans un même lot, rand() renvoie le même nombre.

Je suggère d'utiliser convert( varbinary , newid() ) comme argument de départ :

SELECT table_name, 1.0 + floor(14 * RAND(convert(varbinary, newid()))) magic_number 
FROM information_schema.tables

newid() est garanti de retourner une valeur différente à chaque fois qu'il est appelé, même dans le même lot, donc l'utiliser comme une graine incitera rand() à donner une valeur différente à chaque fois.

Modifié pour obtenir un nombre entier aléatoire de 1 à 14.

Answer 3

RAND(CHECKSUM(NEWID()))

L'opération ci-dessus générera un nombre (pseudo-)aléatoire compris entre 0 et 1, exclusif. Si elle est utilisée dans un select, comme la valeur de la graine change pour chaque ligne, elle générera un nouveau nombre aléatoire pour chaque ligne (il n'est cependant pas garanti qu'elle génère un nombre unique par ligne).

Exemple lorsqu'il est combiné avec une limite supérieure de 10 (produit les nombres 1 - 10) :

CAST(RAND(CHECKSUM(NEWID())) * 10 as INT) + 1

Documentation Transact-SQL :

1. CAST() : https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
2. RAND() : http://msdn.microsoft.com/en-us/library/ms177610.aspx
3. CHECKSUM() : http://msdn.microsoft.com/en-us/library/ms189788.aspx
4. NEWID() : https://docs.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql

Answer 4

La fonction Rand() générera le même nombre aléatoire, si elle est utilisée dans une requête SELECT de table. Il en va de même si vous utilisez une graine pour la fonction Rand. Une autre façon de procéder est d'utiliser ceci :

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]

J'ai obtenu l'information de aquí qui explique très bien le problème.

Answer 5

Essayez d'utiliser une valeur de semence dans le RAND(seedInt). RAND() ne s'exécute qu'une fois par instruction, c'est pourquoi vous obtenez le même nombre à chaque fois.