Discussion :

[PoichOU]

Bonjour,


Admettons que j'ai la table suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
--------------------------------------------------------------
| id | Famille |   Type   | Sous_type | Qte | P_ST_T | P_T_F |
--------------------------------------------------------------
|  1 | Fruit   |   Orange | Mandarine |  2  | NULL   | NULL  |
|  2 | Fruit   |   Orange | Clementine| 10  | NULL   | NULL  |
|  3 | Fruit   |   Orange | Sanguine  |  8  | NULL   | NULL  |
|  4 | Fruit   |   Pomme  | Granny    | 20  | NULL   | NULL  |
|  5 | Fruit   |   Pomme  | Golden    |  8  | NULL   | NULL  |
|  5 | Fruit   |   Pomme  | Rouge     | 12  | NULL   | NULL  |
--------------------------------------------------------------

Je voudrais remplir les colonnes P_ST_T, P_T_F et P_F où :
- P_ST_T est le pourcentage de quantité de sous_type par rapport à un type
- P_T_F est le pourcentage de quantité d'un type par rapport à une famille



exemple pour l'id 1 :
P_ST_T = Qté Mandarine / Qté Orange * 100
P_ST_T = 2 / 20 * 100
P_ST_T = 10

P_T_F = Qté Orange / Qté Fruit * 100
P_T_F = 20 / 60 * 100
P_T_F = 33,33


Il faut faire ce calcul pour chaque ligne et les mettre à jour.

C'est-à-dire obtenir le tableau suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
--------------------------------------------------------------
| id | Famille |   Type   | Sous_type | Qte | P_ST_T | P_T_F | 
--------------------------------------------------------------
|  1 | Fruit   |   Orange | Mandarine |  2  |   10   | 33.33 | 
|  2 | Fruit   |   Orange | Clementine| 10  |   50   | 33.33 |
|  3 | Fruit   |   Orange | Sanguine  |  8  |   40   | 33.33 |
|  4 | Fruit   |   Pomme  | Granny    | 20  |   50   | 66.67 |
|  5 | Fruit   |   Pomme  | Golden    |  8  |   20   | 66.67 |
|  5 | Fruit   |   Pomme  | Rouge     | 12  |   30   | 66.67 |
--------------------------------------------------------------

Est-il possible de faire ça avec une procèdure stockée ?


Merci

PoichOU

[al1_24]

Il suffit de calculer les totaux dans des tables dérivées :

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
select    X.ID 
    ,    X.FAMILLE
    ,    X.TYPE
    ,    X.SOUS_TYPE
    ,    X.QTE
    ,    (ST.QTE / T.QTE)    as P_ST_T
    ,    (T.QTE / F.QTE)    as P_T_F 
from    MATABLE    as X
    inner join
        (    select    SOUS_TYPE
                ,    sum(QTE)    as QTE
            from    MATABLE
            group by    SOUS_TYPE
        )    as ST
        on    X.SOUS_TYPE = ST.SOUS_TYPE
    inner join
        (    select    TYPE
                ,    sum(QTE)    as QTE
            from    MATABLE
            group by    TYPE
        )    as T
        on    X.TYPE = F.TYPE
    inner join
        (    select    FAMILLE
                ,    sum(QTE)    as QTE
            from    MATABLE
            group by    FAMILLE
        )    as F
        on    X.FAMILLE = F.FAMILLE
;

On peut aussi le faire sans tables dérivées en utilisant les fonctions de regroupement analytiques...

[al1_24]

On peut aussi le faire sans tables dérivées en utilisant les fonctions de regroupement analytiques...

Comme ça :

Code :

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

1
2
3
4
5
6
7
8
select    ID 
    ,    FAMILLE
    ,    TYPE
    ,    SOUS_TYPE
    ,    QTE
    ,    (sum(QTE) over(partition by SOUS_TYPE) / sum(QTE) over(partition by TYPE))    as P_ST_T
    ,    (sum(QTE) over(partition by TYPE) / sum(QTE) over(partition by FAMILLE))    as P_T_F 
from    MATABLE

[PoichOU]

Ouahou !! j'suis bluffé par les soluces !


j'ai même trouvé une p'tite erreur dans la première soluce (preuve que je l'ai comprise)

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
SELECT    X.ID 
    ,    X.FAMILLE
    ,    X.TYPE
    ,    X.SOUS_TYPE
    ,    X.QTE
    ,    (ST.QTE / T.QTE)    AS P_ST_T
    ,    (T.QTE / F.QTE)    AS P_T_F 
FROM    MATABLE    AS X
    INNER JOIN
        (    SELECT    SOUS_TYPE
                ,    sum(QTE)    AS QTE
            FROM    MATABLE
            GROUP BY    SOUS_TYPE
        )    AS ST
        ON    X.SOUS_TYPE = ST.SOUS_TYPE
    INNER JOIN
        (    SELECT    TYPE
                ,    sum(QTE)    AS QTE
            FROM    MATABLE
            GROUP BY    TYPE
        )    AS T
        ON    X.TYPE = T.TYPE
    INNER JOIN
        (    SELECT    FAMILLE
                ,    sum(QTE)    AS QTE
            FROM    MATABLE
            GROUP BY    FAMILLE
        )    AS F
        ON    X.FAMILLE = F.FAMILLE
;

merci al1_24 pour tes réponses aussi bonnes et rapide

Il me reste plus qu'à trouver comment faire ça dans une procèdure stockée...

[PoichOU]

Quelqu'un connait un bon tuto qui explique comment créer une procèdure stockée ?

Je voudrais mettre à jour chaque ligne de la table.

Pour info, j'utilise une base de données Oracle.

Voici le sript de création de la table :

Code :

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

1
2
3
4
5
6
7
8
9
10
create table TUTOS_PROC_STOCKEE  (
	ID 		INTEGER 	not null,
	FAMILLE		VARCHAR2(20)	not null,
	TYPE		VARCHAR2(20)	not null,
	SOUS_TYPE	VARCHAR2(20)	not null,
	QUANTITE	NUMBER		not null,
	P_ST_T 		NUMBER,
	P_T_F 		NUMBER,
    primary key (ID)
);

Voici le sript d'initialisation des données :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
insert into TUTOS_PROC_STOCKEE (ID,FAMILLE, TYPE, SOUS_TYPE, QUANTITE, P_ST_T, P_T_F) 
values (1, 'Fruit', 'Orange', 'Mandarine', 2, NULL, NULL);
insert into TUTOS_PROC_STOCKEE (ID,FAMILLE, TYPE, SOUS_TYPE, QUANTITE, P_ST_T, P_T_F) 
values (2, 'Fruit', 'Orange', 'Clementine', 10, NULL, NULL);
insert into TUTOS_PROC_STOCKEE (ID,FAMILLE, TYPE, SOUS_TYPE, QUANTITE, P_ST_T, P_T_F) 
values (3, 'Fruit', 'Orange', 'Sanguine', 8, NULL, NULL);
insert into TUTOS_PROC_STOCKEE (ID,FAMILLE, TYPE, SOUS_TYPE, QUANTITE, P_ST_T, P_T_F) 
values (4, 'Fruit', 'Pomme', 'Granny', 20, NULL, NULL);
insert into TUTOS_PROC_STOCKEE (ID,FAMILLE, TYPE, SOUS_TYPE, QUANTITE, P_ST_T, P_T_F) 
values (5, 'Fruit', 'Pomme', 'Golden', 8, NULL, NULL);
insert into TUTOS_PROC_STOCKEE (ID,FAMILLE, TYPE, SOUS_TYPE, QUANTITE, P_ST_T, P_T_F) 
values (6, 'Fruit', 'Pomme', 'Rouge', 12, NULL, NULL);

Merci de m'aider

PoichOU

[al1_24]

Pourquoi vouloir le faire avec une procédure stockée alors qu'une vue est largement suffisante ?