RAND() retourne 0,0 à la première exécution

[allweneed]

Bonjour,

Un pro peut-il m'expliquer pq la première exécution de cette requête retourne la valeur 0,0 ?

Code :

SELECT RAND() FROM SYSIBM.SYSDUMMY1

C'est seulement à la seconde exécution de la requête que j'obtiens un nombre aléatoire compris entre 0 et 1.

Sur la doc DB2, ils parlent d'une seed value (qui serait en FR une valeur initiale) qu'on peut faire passer en argument. Je ne sais pas si cet argument pourrait solutionner le problème.

http://publib.boulder.ibm.com/infoce...lref/frand.htm

Si vous avez une solution simple et efficace, je suis preneur.

Merci pour votre aide.

[bernard59139]

Bonjour

je ne sais pas si le lien sera utile, mais je l'ai trouvé au moins interessant SQL RAND Function Not So Random

[allweneed]

Merci pour l'article.

J'en ai trouvé un autre tout aussi intéressant :

http://www.wisdomjobs.com/e-universi...functions.html

Il faut chercher après la fonction RAND() dans le texte :

If the seed value is zero, the initial result will also be zero. All other seed values return initial values that are not the same as the seed.

Autre part, j'avais lu que si on n'indique pas de seed, il prend la valeur 0 par défaut.

Un peu plus haut, ils parlent aussi de la fonction GENERATE_UNIQUE() mais ça m'a l'air fort compliqué pour pas grand chose.

Bref je pense que je vais placer l'heure courante avec les micro-secondes dans le seed de la fonction RAND. Je ne vois pas trop ce que je peux faire d'autre.

[allweneed]

Je n'aime pas du tout cette solution, mais quand il faut bricoler et qu'on ne s'appelle pas Mc Guyver :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
WITH TABLE_TEMP AS
(
  SELECT
    RAND() AS RAND_TEMP
  FROM
    SYSIBM.SYSDUMMY1
)
 
SELECT 
  COALESCE(NULLIF(RAND_TEMP, 0), RAND()) 
FROM 
  TABLE_TEMP

[punkoff]

vu que le 1er rand() est toujours identique et que pour un même chiffre la fonction rand() renvoie toujours le même résultat, le problème se mort la queue là non?

l'idée du timestamp était assez bonne je trouve.

[bernard59139]

Bonjour

Il est bien noté dans la doc (mainframe dans mon cas) que RAND() retourne une série pseudo aléatoire. Et que plusieurs taches peuvent retourner le même résultat.

A plusieurs endroits (sites IBM, forum, ...), il est indiqué qu'en passant des MicroSecondes en paramètre de la fonction, le résultat est un beaucoup plus aléatoire.

GENERATE_UNIQUE() permet de générer une valeur unique quelque soit le cas. la chaine fait 31c de long. Mais ce n'est plus un nombre aléatoire qui est fourni. La base est un timestamp compléte de trucs (sysplex, ...).

[allweneed]

Envoyé par bernard59139

A plusieurs endroits (sites IBM, forum, ...), il est indiqué qu'en passant des MicroSecondes en paramètre de la fonction, le résultat est un beaucoup plus aléatoire.

Lorsque j'ai fait des essais avec un SEED, je me suis rendu compte que le résultat n'était pas très aléatoire justement.

Code :

1
2
3
4
5
6
7
8
9
10
select rand(1000) as result, 1 as ind from sysibm.sysdummy1 --retourne  0.10083315530869473
union all
select rand(2000) as result, 2 as ind from sysibm.sysdummy1 --retourne  0.20047608874782555
union all
select rand(3000) as result, 3 as ind from sysibm.sysdummy1 --retourne  0.3001495406964324
union all
select rand(4000) as result, 4 as ind from sysibm.sysdummy1 --retourne  0.39979247413556324
union all
select rand(5000) as result, 5 as ind from sysibm.sysdummy1 --retourne  0.49946592608417006
order by ind

Donc en gros l'idée est de mettre une valeur aléatoire dans l'argument SEED (microsecondes) pour obtenir une autre valeur aléatoire. Je trouve ça bizarre.

Alors que

Code :

1
2
3
4
5
6
7
8
9
10
SELECT rand() AS result, 1 AS ind FROM sysibm.sysdummy1 --retourne 0.8135319071016571
union ALL
SELECT rand() AS result, 2 AS ind FROM sysibm.sysdummy1 --retourne 0.7876522110660116
union ALL
SELECT rand() AS result, 3 AS ind FROM sysibm.sysdummy1 --retourne 0.3185827204199347
union ALL
SELECT rand() AS result, 4 AS ind FROM sysibm.sysdummy1 --retourne 0.17761772515030366
union ALL
SELECT rand() AS result, 5 AS ind FROM sysibm.sysdummy1 --retourne 0.6099429303872799
ORDER BY ind

Dans ce dernier cas, on voit qu'il n'y a pas de logique dans l'attribution des valeurs retournées, ce qui est plus aléatoire que le code précédent.

De plus, je trouve dommage de devoir se limiter pour la valeur du SEED. Normalement on peut aller jusque 2 milliard de valeurs différentes, alors que les microsecondes limitent énormément le nombre de valeurs différentes pour le SEED. Même en ajoutant des concat (expl microsecondes + jj + mm + aaaa etc.) on restreint fort le nombre en sortie.

Enfin ce n'est que mon avis ...

[allweneed]

Je pense que le seed est plus là pour pouvoir lancer plusieurs fois la même requête et récupérer à nouveau le même résultat.

Code :

1
2
3
4
5
SELECT rand(1000) AS result, 1 AS ind FROM sysibm.sysdummy1 --retourne 0.10083315530869473
union ALL
SELECT rand(1000) AS result, 2 AS ind FROM sysibm.sysdummy1 --retourne 0.10083315530869473
union ALL
SELECT rand(1000) AS result, 3 AS ind FROM sysibm.sysdummy1 --retourne 0.10083315530869473

[bernard59139]

tu as re-découvert ce qui se peut se passer quand on parle de nombres "pseudo aléatoires".

[allweneed]

Voici finalement le code que j'ai utilisé et dont je vous fait part, ça permet de générer une chaîne aléatoire avec les caractères que l'on veut. La longueur de cette chaîne est aussi "paramétrable". Je me sers du TIMESTAMP comme argument pour la fonction RAND().

Si qqn a une idée pour retirer le '20' à côté de mon commentaire "Modifier ce paramètre (2/2) si la clé générée doit avoir une autre longueur", je suis preneur. J'ai essayé d'utiliser des cast en tout genre, d'utiliser un CHAR etc. sans succès. Ca permettrait de simplifier légèrement la paramétrisation, mais ça me convient déjà très bien comme ça

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
WITH
  RAND_STRING_INIT (
    RSI_FIGURES, -- Caractères qui servent de base pour générer la chaîne de caractères aléatoire
    RSI_FIGURES_LENGTH, -- Longueur de RSI_FIGURES
    RSI_KEY_LENGTH -- Longueur souhaitée de la chaîne de caractères à générer
  ) AS (
    SELECT
      '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' AS RSI_FIGURES,
      62 AS RSI_FIGURES_LENGTH,
      20 AS RSI_KEY_LENGTH -- Modifier ce paramètre (1/2) si la clé générée doit avoir une autre longueur
    FROM
      SYSIBM.SYSDUMMY1
  ),
 
  RAND_STRING_GENERATOR (
    RSG_KEY_LENGTH, -- Longueur actuelle de la chaîne de caractères générée
    RSG_KEY -- Chaîne de caractères générée
  ) AS (
    SELECT
      0 AS RSG_KEY_LENGTH,
      CAST ('' AS VARCHAR(20)) AS RSG_KEY -- Modifier ce paramètre (2/2) si la clé générée doit avoir une autre longueur
    FROM
      SYSIBM.SYSDUMMY1
 
    UNION ALL
 
    SELECT
      RSG_KEY_LENGTH + 1 AS RSG_KEY_LENGTH,
      CONCAT(
        RSG_KEY,
        SUBSTR(
          RSI_FIGURES,
          MOD( -- MOD car le RAND peut retourner la valeur 1
            INTEGER(
              RAND( -- La valeur (seed) de l'argument envoyé à la fonction RAND(seed) doit être comprise entre 1 et 2147483646.
                INTEGER(
                  CEILING(
                    (MINUTE(CURRENT TIMESTAMP) + 1) *
                    (SECOND(CURRENT TIMESTAMP) + 1) *
                    (
                      CAST((MICROSECOND(CURRENT TIMESTAMP) + 1) AS DOUBLE)
                      / 1000000.0
                      * 2147483646.0
                      / 3600.0 -- 3600 est la valeur maximale du nombre de minutes fois le nombre de secondes.
                    )
                  )
                )
              ) * RSI_FIGURES_LENGTH
            ),
            RSI_FIGURES_LENGTH
          ) + 1,
          1
        )
      ) AS RSG_KEY
    FROM
      RAND_STRING_GENERATOR RSG,
      RAND_STRING_INIT RSI
    WHERE
      RSG_KEY_LENGTH + 1 <= RSI_KEY_LENGTH
  )
 
SELECT
  RSG_KEY
FROM
  RAND_STRING_GENERATOR RSG,
  RAND_STRING_INIT RSI
WHERE
  RSG_KEY_LENGTH = RSI_KEY_LENGTH

[K2R400]

Bravo, belle démonstration de récursivité.
Par contre, une petite question me taraude.
Pourquoi ne pas avoir fait tout simplement une fonction ?

[allweneed]

Simplement pcq là où je travaille je n'ai pas la main sur toutes les parties du SGBD. Si je veux créer des fonctions etc je dois passer par d'autres équipes. Je me vois mal créer ma fonction, demander de me l'"encoder", voir que ça ne va pas, debuguer à l'aveugle et renvoyer mes demandes jusqu'à ce que ça marche. Je préfère aussi que les autres développeurs voient la fonction directement dans le code et ne pas avoir des parties "cachées".
C'est bizarre comme raisonnement je sais, mais c'est comme ça
Et avec tout ça, je dois aussi dire que je ne connais pas trop cette partie de DB2, et idem avec le PL/SQL sous Oracle même si je me doute que cela apporte de nombreuses fonctionnalités.