Discussion :

[momo_m]

Bonjour à Tous !

Je suis nouveau ici dans ce Forum, et malheureusement ma première discussion sera pour poster une question et ne pas pour y répondre.

En fait, j'ai un problème au niveau d'une procédure PLSQL.

J'ai une question pour construire une Base de donnée et de la remplir avec
des proportions données . alors la, y'a pas mal de solutions pour y faire,
moi j'ai choisi de travailler avec une procédure PLSQL et tester pour chaque
champ

Voilà la question déjà :

Construire la base de données suivante :

Etudiants (No-etu, Nom-etu, Pnom-etu, Ville, Promotion, Date-nais, Commentaire)
Promotions (No-promo, Libelle-promo, Responsable, Niveau, Commentaire)

Remplir cette base de données à l’aide d’un programme PL/SQL, avec une proportion « contrôlée »
d’étudiants dans différentes villes et différentes promotions.
Les proportions visées sont :
- Pour Ville : Dijon (40%), Chenôve ( 25%), Talant (20%), Quetigny (10%), Chevigny (4%),
Bressey (1%)
- Pour Promotion : L1 MIE (27%), L1 PC (20%), L2 MIE (15%), L2 PC (10%), L2 MP (1%), L3
Chimie (5%), L3 Info (8%), L3 Elec (2%), L3 Méca (2%), L3 Phys (3%), L3 Math (7%)

et voilà la procédure que j'ai crée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
set serveroutput on;
CREATE OR REPLACE PROCEDURE Insertion
AS
BEGIN
v_ville varchar(30)
v_promotion varchar(30)

FOR i IN 1..500
LOOP
pct := DBMS_RANDOM.VALUE(0, 100);
IF pct < 40 then v_ville := 'Dijon';
ELSIF pct < 25 then v_ville := 'Chenôve';
ELSIF pct < 20 then v_ville := 'Talant';
ELSIF pct < 10 then v_ville := 'Quetigny';
ELSIF pct < 4 then v_ville := 'Chevigny';
ELSE v_ville := 'Bressey'; 
END IF;
 
pct := DBMS_RANDOM.VALUE(0, 100);
IF pct < 27 then v_promotion := 'L1 MIE';
ELSIF pct < 20 then v_promotion := 'L1 PC';
ELSIF pct < 15 then v_promotion := 'L2 MI2';
ELSIF pct < 10 then v_promotion := 'L2 PC';
ELSIF pct < 1 then v_promotion := 'L2 MP';
ELSIF pct < 5 then v_promotion := 'L3 Chimie';
ELSIF pct < 8 then v_promotion := 'L3 Info';
ELSIF pct < 2 then v_promotion := 'L3 Elec';
ELSIF pct < 2 then v_promotion := 'L3 Méca';
ELSIF pct < 3 then v_promotion := 'L3 Phys';
ELSE v_promotion := 'L3 Math'; 
END IF;

 
INSERT INTO ETUDIANT(No_etu,Nom_etu,Pnom_etu,Ville,Promotion,Date_nais,Commentaire)
VALUES (i,Nom_etu,Pnom_etu,v_ville,v_promotion,Date_nais,Commentaire);
 
  END LOOP;
--Validation
COMMIT;
END;

alors j'ai crée cette procédure qui teste pour les champs promotion et ville
les proportions données.

Alors le problèmes c'est que je ne c'est pas si c'est correcte ma solution ou
non ? ainsi que je ne sais pas si je dois entrer tout les variables dans l'entête
de la procédure ou juste les variables v_ville et v_promotion que je vais les
tester après ? aussi , est ce que je dois faire une INSERT INTO PROMOTION
en dessous de INSERT INTO ETUDIANT ?

Merci de me donner une solution , je suis vraiment coincé .

Merci d'avance.

[ojo77]

Trois choses pour réfléchir :

1°) Ce qui peut être fait directement en SQL ne devrait pas être ait en PL/SQL (c'est mon avis et il me semble que c'est aussi celui de S. Feuerstein)

2°) Si x n'es pas inférieur à 40 il ne peut pas être inférieur à 27

3°) dbms_random.value ne reflète pas très bien la loi uniforme discrète sur un petit nombre de valeurs.

[momo_m]

Merci pour votre réponse ojo77.

Vu que je suis un débutant en PL-SQL , j'ai pas bien compris

qu'est ce que vous voulez dire par votre réponse et par vos trois choses

pour réfléchir. J'ai essayé de comprendre votre réponse mais j'ai pas arrivé.

Merci quand même pour votre intérêt.

S'il y'a SVP quelqu'un qui peut me donner des solutions de codes.

Merci d'avance.

[ojo77]

Un exemple avec des stats (les stats que vous souhaitez)

La problématique est de générer deux variables aléatoires discrètes et indépendantes qui suivent une loi uniforme sur un petit nombre de valeurs

La table tA génère une variable, la table tB une autre.

La variable aléatoire générée par tA sera utilisée pour déterminer la ville
La variable aléatoire générée par tB sera utilisée pour déterminer la classe

La requête suivante (c'est du SQL pur, pas de code PL là dedans) se content d'agréger les résultats et de vérifier que d'une part toutes les proportions demandées sont bien générées et que les étudiants sont répartis dans toutes les classes quelle que soit leur provenance

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
with tA as ( select rownum id, va1
             from (select mod(rownum,100) va1, dbms_random.value() ord
                   from dual
                   connect by rownum <= 500
                   order by 2) ),
     tb as ( select rownum id, va1
             from (select mod(rownum,100) va1, dbms_random.value() ord
                   from dual
                   connect by rownum <= 500
                   order by 2) ),
     u as ( select case when ta.va1 < 40 then 'Dijon'
                        when ta.va1 < 65 then 'Chenôve'
                        when ta.va1 < 95 then 'Talant'
                        when ta.va1 < 95 then 'Quetigny'
                        when ta.va1 < 99 then 'Chevigny'
                        else                    'Bressey' end VILLE
                 , case when tb.va1 < 27 then 'L1 MIE'
                        when tb.va1 < 47 then 'L1 PC'
                        when tb.va1 < 62 then 'L2 MI2'
                        when tb.va1 < 72 then 'L2 PC'
                        when tb.va1 < 73 then 'L2 MP'
                        when tb.va1 < 78 then 'L3 Chimie'
                        when tb.va1 < 86 then 'L3 Info'
                        when tb.va1 < 88 then 'L3 Méca'
                        when tb.va1 < 91 then 'L3 Phys'
                        else                     'L3 Math' end ANN_MAT
            from ta, tb where ta.id=tb.id )
select 1 c0, ville c1, count(*) nb, trunc((count(*)/500)*100,2) PCT
from u
group by ville
union all
select 2, ANN_MAT, count(*), trunc((count(*)/500)*100,2)
from u
group by ann_mat
union all
select 3, ANN_MAT||' '||VILLE, count(*), trunc((count(*)/500)*100,2)
from u
group by ANN_MAT||' '||VILLE
order by 1
/

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
        C0 C1                         NB        PCT
---------- ------------------ ---------- ----------
         1 Dijon                     200         40
         1 Talant                    150         30
         1 Chevigny                   20          4
         1 Bressey                     5          1
         1 Chenôve                   125         25
         2 L3 Méca                    10          2
         2 L3 Chimie                  25          5
         2 L1 PC                     100         20
         2 L2 PC                      50         10
         2 L2 MP                       5          1
         2 L2 MI2                     75         15
         2 L1 MIE                    135         27
         2 L3 Info                    40          8
         2 L3 Math                    45          9
         2 L3 Phys                    15          3
         3 L3 Phys Chenôve             4         ,8
         3 L3 Phys Chevigny            2         ,4
         3 L1 PC Bressey               1         ,2
         3 L2 PC Dijon                20          4
         3 L1 MIE Dijon               56       11,2
         3 L3 Méca Dijon               5          1
         3 L3 Chimie Chenôve           7        1,4
         3 L3 Math Talant             12        2,4
         3 L2 MP Dijon                 3         ,6
         3 L1 PC Dijon                43        8,6
         3 L1 MIE Talant              42        8,4
         3 L3 Math Chenôve            13        2,6
         3 L3 Méca Chenôve             1         ,2
         3 L3 Info Chevigny            3         ,6
         3 L1 PC Chevigny              3         ,6
         3 L3 Chimie Dijon            10          2
         3 L3 Math Chevigny            1         ,2
         3 L1 MIE Bressey              2         ,4
         3 L2 PC Chevigny              1         ,2
         3 L2 PC Talant               16        3,2
         3 L3 Phys Talant              2         ,4
         3 L3 Chimie Bressey           1         ,2
         3 L2 MI2 Dijon               26        5,2
         3 L3 Info Dijon              12        2,4
         3 L2 PC Chenôve              13        2,6
         3 L3 Phys Dijon               7        1,4
         3 L3 Méca Talant              3         ,6
         3 L3 Math Bressey             1         ,2
         3 L2 MI2 Chevigny             5          1
         3 L3 Chimie Chevigny          1         ,2
         3 L3 Math Dijon              18        3,6
         3 L1 PC Chenôve              22        4,4
         3 L1 MIE Chenôve             32        6,4
         3 L3 Méca Chevigny            1         ,2
         3 L3 Info Talant             14        2,8
         3 L2 MI2 Talant              22        4,4
         3 L3 Info Chenôve            11        2,2
         3 L2 MP Talant                2         ,4
         3 L1 PC Talant               31        6,2
         3 L2 MI2 Chenôve             22        4,4
         3 L3 Chimie Talant            6        1,2
         3 L1 MIE Chevigny             3         ,6
 
57 ligne(s) sélectionnée(s).

Réutilisation avec la génération de vos deux champs

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
with tA as ( select rownum id, va1
             from (select mod(rownum,100) va1, dbms_random.value() ord
                   from dual
                   connect by rownum <= 500
                   order by 2) ),
     tb as ( select rownum id, va1
             from (select mod(rownum,100) va1, dbms_random.value() ord
                   from dual
                   connect by rownum <= 500
                   order by 2) )
select case when ta.va1 < 40 then 'Dijon'
            when ta.va1 < 65 then 'Chenôve'
            when ta.va1 < 95 then 'Talant'
            when ta.va1 < 95 then 'Quetigny'
            when ta.va1 < 99 then 'Chevigny'
            else                    'Bressey' end VILLE
     , case when tb.va1 < 27 then 'L1 MIE'
            when tb.va1 < 47 then 'L1 PC'
            when tb.va1 < 62 then 'L2 MI2'
            when tb.va1 < 72 then 'L2 PC'
            when tb.va1 < 73 then 'L2 MP'
            when tb.va1 < 78 then 'L3 Chimie'
            when tb.va1 < 86 then 'L3 Info'
            when tb.va1 < 88 then 'L3 Méca'
            when tb.va1 < 91 then 'L3 Phys'
            else                     'L3 Math' end ANN_MAT
from ta, tb where ta.id=tb.id

Ce sont donc VILLE et ANN_MAT qui serviront à alimenter la table finale

Qui donne (30 premières lignes)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
VILLE     ANN_MAT
--------- ---------
Dijon     L2 PC
Chenôve   L1 MIE
Dijon     L2 MI2
Dijon     L2 MI2
Dijon     L3 Méca
Talant    L3 Math
Talant    L1 PC
Talant    L3 Info
Talant    L1 PC
Dijon     L2 PC
Dijon     L1 MIE
Talant    L1 MIE
Chenôve   L1 PC
Talant    L2 MI2
Dijon     L3 Math
Dijon     L2 MI2
Dijon     L1 PC
Chenôve   L3 Chimie
Talant    L3 Chimie
Talant    L1 MIE
Chenôve   L2 PC
Dijon     L1 PC
Chenôve   L1 MIE
Talant    L2 PC
Chenôve   L2 PC
Chenôve   L2 PC
Dijon     L3 Math
Chenôve   L2 PC
Dijon     L2 PC
Dijon     L1 MIE
 
30 ligne(s) sélectionnée(s).

EDIT :

C'est plus simple comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
with tA as ( select mod(rownum,100) va2, va1
             from (select mod(rownum,100) va1, dbms_random.value() ord
                   from dual
                   connect by rownum <= 500
                   order by 2) )
select case when va1 < 40 then 'Dijon'
            when va1 < 65 then 'Chenôve'
            when va1 < 95 then 'Talant'
            when va1 < 95 then 'Quetigny'
            when va1 < 99 then 'Chevigny'
            else                 'Bressey' end VILLE
     , case when va2 < 27 then 'L1 MIE'
            when va2 < 47 then 'L1 PC'
            when va2 < 62 then 'L2 MI2'
            when va2 < 72 then 'L2 PC'
            when va2 < 73 then 'L2 MP'
            when va2 < 78 then 'L3 Chimie'
            when va2 < 86 then 'L3 Info'
            when va2 < 88 then 'L3 Méca'
            when va2 < 91 then 'L3 Phys'
            else                     'L3 Math' end ANN_MAT
from ta

[ojo77]

Suite à réception d'un MP je détaille plus avant la réponse.

On veut créer une table qui va contenir un certain pourcentage d'enregistrements pour des valeurs différentes de deux colonnes. les distributions sont données pour chacune des colonnes et on souhaite qu'il n'y ait pas de dépendance entre les deux colonnes.

Mathématiquement cela revient à générer deux variables aléatoires discrètes suivant chacune une loi connue différente.

Chacune de ces lois étant connue (pourcentage de valeurs de chaque occurrence) on peut les dériver d'une loi discrète uniforme non aléatoire.
Par exemple un entier incrémenté remis à 0 toutes les n occurrences.

Donc pour générer 30% d'étudiants d'un sexe et 70% de l'autre il suffit de faire une boucle comme suit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select case when <nombre qui s''incrémente modulo 10> < 3 then 's1'
       else                                               then 's2' end SEXE  
from dual
connect by rownum <= <nombre d''occurrences souhatées et multiple de 10>

on utilise rownum (le numéro de la ligne courante) comme nombre qui s'incrémente

Si on veut 30 occurrences on passe donc la requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select case when mod(rownum,10) < 3 then 's1'
       else                              's2' end SEXE  
from dual
connect by rownum <= 30

On obtient alors une suite et un nombre de s1 et de s2 tout à fait prévisible. Pour la rendre aléatoire il suffit de l'ordonner par un générateur aléatoire. Le nombre d'occurrence restera bon et l'ordre imprévisible

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
select case when mod(rownum,10) < 3 then 's1'
       else                              's2' end SEXE
from dual
connect by rownum <= 30
order by dbms_random.value()

Du coup c'est intéressant, car le numéro de la ligne après tri devient par rapport à la valeur une variable aléatoire discrète et uniforme, il peut donc être utilisé de la même manière pour générer une nouvelle valeur de colonne de loi discrète connue.

Revenons à votre problématique : Générer automatiquement des provenance d'élèves dont la distribution est connue et leur classe de destination dont la distribution est connue elle aussi mais indépendante de la provenance. Je commence par générer deux variables aléatoires discrètes et indépendante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT mod(rownum,100) va2, va1
FROM (SELECT mod(rownum,100) va1 
      FROM dual
      connect BY rownum <= 500
      ORDER BY dbms_random.value() )

Et à partir de ces deux variable je génère les distributions :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT case when va1 < 40 then 'Dijon'
            when va1 < 65 then 'Chenôve'
            when va1 < 95 then 'Talant'
            when va1 < 95 then 'Quetigny'
            when va1 < 99 then 'Chevigny'
            else                 'Bressey' end VILLE
     , case when va2 < 27 then 'L1 MIE'
            when va2 < 47 then 'L1 PC'
            when va2 < 62 then 'L2 MI2'
            when va2 < 72 then 'L2 PC'
            when va2 < 73 then 'L2 MP'
            when va2 < 78 then 'L3 Chimie'
            when va2 < 86 then 'L3 Info'
            when va2 < 88 then 'L3 Méca'
            when va2 < 91 then 'L3 Phys'
            else                     'L3 Math' end ANN_MAT
FROM ( SELECT mod(rownum,100) va2, va1
       FROM (SELECT mod(rownum,100) va1 
             FROM dual
             connect BY rownum <= 500
             ORDER BY dbms_random.value() )
      )

Vous pouvez utiliser le résultat de cette requête dans du PL/SQL ou directement en SQL pour alimenter votre table

[McM]

Je pense que le code fourni par Momo_m est préférable. D'une part parce que c'est un TP et que fournir du sql pur, ce n'est pas demandé, et qu'ensuite du connect by rownum, ça va se voir

Je suis plus pour corriger son erreur de code (signalé au premier post de Ojo point 2)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
IF pct < 40 THEN v_ville := 'Dijon';
ELSIF pct < 25 THEN v_ville := 'Chenôve';

Si pct = 10 => ville = Dijon (premier IF).. donc il faut tester du plus petit au plus gros.
Mais le test continue à être faux, il faut cumuler les % :
40%;25%;35% correspond à 0-39, 40 à 64 et 65 à 99 (ou 1-40, 41-65, 66-100)

Ce qui donne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE OR REPLACE PROCEDURE Insertion
AS
BEGIN
v_ville varchar(30)
v_promotion varchar(30)
 
FOR i IN 1..500
LOOP
pct := DBMS_RANDOM.VALUE(0, 100);
IF pct < 4 THEN v_ville := 'Chevigny';
ELSIF pct < 14 THEN v_ville := 'Quetigny';
ELSIF pct < 34 THEN v_ville := 'Talant';
ELSIF pct < 59 THEN v_ville := 'Chenôve';
ELSIF pct < 99 THEN v_ville := 'Dijon';
ELSE v_ville := 'Bressey'; 
END IF;
 
pct := DBMS_RANDOM.VALUE(0, 100);
A faire : recoder les % cumulés
 
INSERT INTO ETUDIANT(No_etu,Nom_etu,Pnom_etu,Ville,Promotion,Date_nais,Commentaire)
VALUES (i,Nom_etu,Pnom_etu,v_ville,v_promotion,Date_nais,Commentaire);
 
  END LOOP;
--Validation
COMMIT;
END;