Opération arithmétique : optimisation

**Casshern** · 18/10/2012, 15h12

Bonjour,
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
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
CREATE TABLE TEST_CALCUL (  ANNEE NUMBER(4,0) ,MOIS NUMBER(2,0),ID_EMPLOYE VARCHAR2(08) , CODE VARCHAR2(10), SOUS_CODE VARCHAR2(10),VALEUR1 NUMBER(10,2) , VALEUR2 NUMBER(10,2) , VALEUR3 NUMBER(10,2),PRIMARY KEY (ANNEE,MOIS,ID_EMPLOYE,CODE,SOUS_CODE))
Insert into TEST_CALCUL (ANNEE,MOIS,ID_EMPLOYE,CODE,SOUS_CODE,VALEUR1,VALEUR2,VALEUR3) values ('2012','10','EMP01','CODE1','Q','1','2','3');
Insert into TEST_CALCUL (ANNEE,MOIS,ID_EMPLOYE,CODE,SOUS_CODE,VALEUR1,VALEUR2,VALEUR3) values ('2012','10','EMP01','CODE2','W','4','5','6');
Insert into TEST_CALCUL (ANNEE,MOIS,ID_EMPLOYE,CODE,SOUS_CODE,VALEUR1,VALEUR2,VALEUR3) values ('2012','10','EMP01','CODE3','D','7','8','9');
Insert into TEST_CALCUL (ANNEE,MOIS,ID_EMPLOYE,CODE,SOUS_CODE,VALEUR1,VALEUR2,VALEUR3) values ('2012','10','EMP02','CODE1','D','10','11','12');
Insert into TEST_CALCUL (ANNEE,MOIS,ID_EMPLOYE,CODE,SOUS_CODE,VALEUR1,VALEUR2,VALEUR3) values ('2012','10','EMP02','CODE3','F','13','14','15');
Insert into TEST_CALCUL (ANNEE,MOIS,ID_EMPLOYE,CODE,SOUS_CODE,VALEUR1,VALEUR2,VALEUR3) values ('2012','10','EMP02','CODE3','D','16','17','18');
Insert into TEST_CALCUL (ANNEE,MOIS,ID_EMPLOYE,CODE,SOUS_CODE,VALEUR1,VALEUR2,VALEUR3) values ('2012','10','EMP02','CODE3','X','19','20','21');
Insert into TEST_CALCUL (ANNEE,MOIS,ID_EMPLOYE,CODE,SOUS_CODE,VALEUR1,VALEUR2,VALEUR3) values ('2012','10','EMP02','CODE4','X','22','23','24');
Insert into TEST_CALCUL (ANNEE,MOIS,ID_EMPLOYE,CODE,SOUS_CODE,VALEUR1,VALEUR2,VALEUR3) values ('2012','10','EMP02','CODE5','X','25','26','27');
Insert into TEST_CALCUL (ANNEE,MOIS,ID_EMPLOYE,CODE,SOUS_CODE,VALEUR1,VALEUR2,VALEUR3) values ('2012','10','EMP01','CODE4','X','28','29','30');
Insert into TEST_CALCUL (ANNEE,MOIS,ID_EMPLOYE,CODE,SOUS_CODE,VALEUR1,VALEUR2,VALEUR3) values ('2012','10','EMP01','CODE5','X','31','32','33');
ANNEE MOIS ID_EMPLOYE CODE       SOUS_CODE     VALEUR1    VALEUR2    VALEUR3
----- ---- ---------- ---------- ---------- ---------- ---------- ----------
 2012   10 EMP01      CODE1      Q                   1          2          3 
 2012   10 EMP01      CODE2      W                   4          5          6 
 2012   10 EMP01      CODE3      D                   7          8          9 
 2012   10 EMP02      CODE1      D                  10         11         12 
 2012   10 EMP02      CODE3      F                  13         14         15 
 2012   10 EMP02      CODE3      D                  16         17         18 
 2012   10 EMP02      CODE3      X                  19         20         21 
 2012   10 EMP02      CODE4      X                  22         23         24 
 2012   10 EMP02      CODE5      X                  25         26         27 
 2012   10 EMP01      CODE4      X                  28         29         30 
 2012   10 EMP01      CODE5      X                  31         32         33

et le but est d'obtenir le résultat suivant

Code :

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

1
2
3
4
ANNEE MOIS ID_EMPLOYE 'STOCK1' 'STOCK2'
----- ---- ---------- -------- --------
 2012   10 EMP01             5        4 
 2012   10 EMP02            64        4

sachant que

Code :

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

1
2
STOCK1=(VALEUR1 DE CODE1)-(VALEUR2 DE CODE2)+(VALEUR3 DE CODE3)
STOCK2=-(VALEUR2 DE CODE4)+(VALEUR3 DE CODE5)

Avez-vous une méthode plus performante que celle-ci?

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
WITH TABLE1 AS (
SELECT ANNEE ,MOIS  ,ID_EMPLOYE ,CODE,SUM(VALEUR1) AS VALEUR1,SUM(VALEUR2) AS VALEUR2,SUM(VALEUR3) AS VALEUR3
FROM TEST_CALCUL 
WHERE 
ANNEE = 2012 AND MOIS = 10 
AND CODE IN ('CODE1','CODE2','CODE3','CODE4','CODE5')
GROUP BY ANNEE ,MOIS  ,ID_EMPLOYE ,CODE
)
,TABLE2 AS (
SELECT ANNEE ,MOIS ,ID_EMPLOYE  ,'STOCK1' AS CODE, VALEUR1 AS VALEUR FROM TABLE1 WHERE  CODE = 'CODE1'
UNION
SELECT ANNEE ,MOIS ,ID_EMPLOYE  ,'STOCK1' AS CODE, VALEUR2*-1 AS VALEUR FROM TABLE1 WHERE  CODE = 'CODE2'
UNION
SELECT ANNEE ,MOIS ,ID_EMPLOYE  ,'STOCK1' AS CODE, VALEUR3 AS VALEUR FROM TABLE1 WHERE  CODE = 'CODE3'
UNION
SELECT ANNEE ,MOIS ,ID_EMPLOYE  ,'STOCK2' AS CODE, VALEUR2*-1 AS VALEUR FROM TABLE1 WHERE  CODE = 'CODE4'
UNION
SELECT ANNEE ,MOIS ,ID_EMPLOYE  ,'STOCK2' AS CODE, VALEUR3 AS VALEUR FROM TABLE1 WHERE  CODE = 'CODE5'
)
SELECT * FROM TABLE2 PIVOT (SUM(VALEUR) FOR CODE IN ('STOCK1','STOCK2'));

**McM** · 18/10/2012, 15h38

Avec un decode et une fonction de groupe

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT annee, mois, id_employe, 
		SUM(DECODE(code, 'CODE1', valeur1, 0)) 
	- SUM(DECODE(code, 'CODE2', valeur3, 0)) 
	+ SUM(DECODE(code, 'CODE3', valeur3, 0)) stock1,
  	SUM(DECODE(code, 'CODE5', valeur3, 0)) 
	- SUM(DECODE(code, 'CODE4', valeur2, 0)) stock2
FROM test_calcul
GROUP BY annee, mois, id_employe
 
ANNEE	MOIS	ID_EMPLOYE	STOCK1	STOCK2
2012	10	EMP01		4	4
2012	10	EMP02		64	4

Et tu as une erreur dans ton résultat : C'est 4 le premier résultat : 1 - 5 + 8