[Snippets] Génération de nombres pseudo-aléatoires

Publié le par

Un participant au forum SQL Server de ce site a demandé comment on peut générer 10000 nombres entiers positifs aléatoires ne contenant pas de zéros, par une requête T-SQL.
Je connaissais la fonction RAND(), qui suffit dans bien des situations car on n’a pas souvent besoin de générer une série de nombres aléatoires, pourtant celle-ci ne s’est pas prêtée au jeu …

Avec une expression de table commune, j’écrivais la requête suivante, dont je pensais qu’elle allait générer 10 nombres aléatoires :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
------------------------------  
- Nicolas SOUQUET - 06/12/2008  
------------------------------  
WITH
  CTE_Random AS
  (
    SELECT 1 indice, RAND() nombre
  UNION ALL
    SELECT indice + 1, RAND() nombre
    FROM CTE_Random
    WHERE indice < 10
  )
SELECT *
FROM CTE_Random

Problème : 

indice      nombre
----------- ----------------------
1           0,948787066403352
2           0,810137410843534
3           0,810137410843534
4           0,810137410843534
5           0,810137410843534
6           0,810137410843534
7           0,810137410843534
8           0,810137410843534
9           0,810137410843534
10          0,810137410843534

Seuls le premier nombre généré est différent des autres !

Je me suis alors souvenu de la fonction NEWID(), qui génère un GUID, et qui peut être utilisée pour identifier un ticket par exemple …
L’avantage de celle-ci par rapport à RAND(), c’est qu’elle génère des valeurs exclusivement différentes :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
------------------------------  
- Nicolas SOUQUET - 06/12/2008  
------------------------------  
WITH
  CTE_Random AS
  (
    SELECT 1 indice, NEWID() nombre
  UNION ALL
    SELECT indice + 1, NEWID() nombre
    FROM CTE_Random
    WHERE indice < 10
  )
SELECT *
FROM CTE_Random
indice      nombre
----------- ------------------------------------
1           4BDE02EB-1535-4309-8FF4-9BE0FC9890FC
2           74A8CD0E-5158-42FC-8CB4-0965147FE41F
3           AF760F20-DEFA-4B25-ADB4-745B15C9BE5D
4           0200CCBE-454B-4D34-A755-52AC98384DD0
5           C9D8EA8F-64A7-47B4-A3DB-626EBA3C967B
6           3216696C-3316-46B7-BDD5-7F4EBEAF4F20
7           40F3B9A6-C6EE-4677-9455-2437C09F4930
8           28F01AB5-5924-4163-98A5-593A662D26AC
9           80F1C811-C929-4A86-B1BE-4726E27B43FD
10          8D794438-0A13-4658-9C20-6F546F7E41C8

Dès lors, comment passer d’un GUID à une valeur numérique, ou au moins une représentation numérique de celui-ci ?
Le tableau des transtypages autorisés dans la documentation des fonctions CAST et CONVERT précise que l’on ne peut pas transtyper une donnée de type UNIQUEIDENTIFIER en un type de donnée numérique.
Qu’à cela ne tienne, on peut transtyper le type de données UNIQUEIDENTIFIER, de notre GUID, au type VARBINARY, et le type VARBINARY en type numérique :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
------------------------------  
- Nicolas SOUQUET - 06/12/2008  
------------------------------  
WITH
  CTE_Random AS
  (
    SELECT 1 indice, CAST(CAST(NEWID() AS VARBINARY) AS BIGINT) nombre
  UNION ALL
    SELECT indice + 1, CAST(CAST(NEWID() AS VARBINARY) AS BIGINT) nombre
    FROM CTE_Random
    WHERE indice < 10
  )
SELECT *
FROM CTE_Random
indice      nombre
----------- --------------------
1           -5798444018420898861
2           -8387580781428009187
3           -8290221482251344193
4           -5517060763531941547
5           -5222611935137695036
6           -8084341965211989766
7           -6365110813593533953
8           -4855414462827669452
9           -6272538355892614409
10          -5045499018529999454

Le requis du participant au forum était d’obtenir des nombres entiers positifs, or nous n’avons là que des entiers négatifs.
Utilisons la fonction ABS(), qui retourne la valeur absolue d’un nombre :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
------------------------------  
- Nicolas SOUQUET - 06/12/2008  
------------------------------  
WITH
  CTE_Random AS
  (
      SELECT 1 indice, ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) nombre
    UNION ALL
      SELECT indice + 1, ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) nombre
      FROM CTE_Random
      WHERE indice < 10
  )
SELECT *
FROM CTE_Random
indice      nombre
----------- --------------------
1           7906050049288686091
2           6497845688783947995
3           7055925192035141370
4           7818470519893116179
5           6240781906318427782
6           6648187948410848759
7           7992501322185322905
8           5480780527582611816
9           9050306324847691808
10          5335687916251733319

Comme nous avons transtypé en BIGINT, nous avons des entiers sur 19 chiffres.

Après avoir extrait de façon aléatoire 10 chiffres de chacun des 19 chiffres générés avec l’aide de la fonction SUBSTRING(), et remplacé les zéros par des entiers générés avec la fonction RAND(), on obtient la requête suivante :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
------------------------------  
- Nicolas SOUQUET - 06/12/2008  
------------------------------  
WITH  
  CTE_RANDOM (Random, Indice) AS
  (
    SELECT REVERSE(REPLACE(SUBSTRING(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) AS VARCHAR), CAST(RAND() * 10 AS INT), 10), '0', CAST(CAST(RAND() * 10 AS TINYINT) AS CHAR(1)))), 1
  UNION ALL
    SELECT REVERSE(REPLACE(SUBSTRING(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) AS VARCHAR), CAST(RAND() * 10 AS INT), 10), '0', CAST(CAST(RAND() * 10 AS TINYINT) AS CHAR(1)))), Indice + 1
    FROM CTE_RANDOM
    WHERE Indice < 10000
  )
SELECT Random
FROM CTE_RANDOM
WHERE Random NOT LIKE '0%'
OPTION (MAXRECURSION 0)

la clause OPTION avec l’indicateur de requête MAXRECURSION est nécessaire pour outrepasser la récursivité maximale de 100 autorisée par SQL Server, et obtenir ainsi 10000 nombres aléatoires ne commençant pas par zéro.
En effet comme l’ensemble de nombres est retourné sous forme de chaîne, il peut commencer par zéro.
Or lorsqu’on transtype une chaîne de chiffres commençant par zéro en un type entier, ce(s) zéro est « supprimé », et on obtiendra donc des nombres entiers de moins de 10 chiffres.

ElSuket

Laisser un commentaire