Claculer un cumul

**redabadache3** · 06/11/2007, 19h15

Bjr,

J'ai une table avec les champs suivants:

annee number,
mois number,
production number,

Mon but est de calculer le cumul de production atteint pour chaque mois, par ex: pour le mois de juin 2007, je veux avoir

annee: 2007
mois: juin
production: somme de production de tt les mois entre janvier et juin

Une idee?

Merci

Réda

**redabadache3** · 06/11/2007, 19h57

oups, j'ai deja essayé cela, ca donne la somme de la production pour le mois de Juin uniquement, mais mois ce que je veux, c'est avoir pour Juin (par exemple) production de janvier + production de Fervier + production de Mars + ... + production de Juin

Invité · 06/11/2007, 19h59

Oui j'ai m*** et je viens de supprimer mon message ce qui fait que c'est bizarre.
Perso, je ferais une fonction pour faire ça, mais il y a sûrement moyen de faire une belle requête avec des fonctions analytique mais je ne maîtrise pas...

Invité · 06/11/2007, 20h05

un éclair de génie :

Code :

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

1
2
3
4
5
SELECT a.annee, a.mois, sum(b.prod) 
FROM ma_table A, ma_table B 
WHERE a.annee = b.annee  
  AND b.mois <= a.mois 
GROUP BY a.annee, a.mois ;

**mnitu** · 06/11/2007, 21h43

Et regardez aussi du coté des vues matérialisés

**orafrance** · 07/11/2007, 09h48

pourquoi pas un trigger qui met à jour la colonne production à chaque nouveau mois inséré ?

**orafrance** · 07/11/2007, 09h58

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
CREATE OR REPLACE TRIGGER mon_trigger
BEFORE INSERT ON ma_table
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; -- evite ora-04091
BEGIN
IF :new.mois > 1 THEN
  SELECT prod + :new.prod
    INTO :new.prod
    FROM ma_table
   WHERE mois = :new.mois - 1
     AND annee = :new.annee;
END IF;
END;
/

Code :

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

1
2
insert into ma_table values (2007,1,10);
insert into ma_table values (2007,2,15);

Code :

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

1
2
3
4
5
6
SQL>select * from ma_table;
 
     ANNEE       MOIS       PROD
---------- ---------- ----------
      2007          1         10
      2007          2         25

**Loyd1974** · 07/11/2007, 10h26

En analytique, cela donne ceci :

Code :

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

1
2
3
4
5
6
7
 
WITH t AS (SELECT 2007 AS YEAR, 1 AS MONTH, 1000 AS production FROM dual UNION ALL
                      SELECT 2007, 2, 800 FROM dual UNION ALL
                    SELECT 2007, 3, 500 FROM dual UNION ALL
                    SELECT 2007, 4, 40 FROM dual)
SELECT YEAR, MONTH, SUM(production) over (ORDER BY YEAR,MONTH)
FROM t

Edit: au cas où tu ne serais pas familier avec la clause WITH, ne t'attarde pas dessus, cela me permet juste de me créer un jeu d'essai sans passer par la création d'une table, ce qui est important c'est le SUM(...) OVER (..)

**orafrance** · 07/11/2007, 11h28

si tu inclus le mois dans la SUM ça ne marche pas comme il veut si je ne m'abuse

**Loyd1974** · 07/11/2007, 11h51

Je ne sais pas, moi, j'ai juste lu :

Mon but est de calculer le cumul de production atteint pour chaque mois, par ex: pour le mois de juin 2007, je veux avoir

annee: 2007
mois: juin
production: somme de production de tt les mois entre janvier et juin

Donc, si on prend ma requête cela 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
WITH t AS (SELECT 2007 AS YEAR, 1 AS MONTH, 1000 AS production FROM dual UNION ALL
                      SELECT 2007, 2, 800 FROM dual UNION ALL
                    SELECT 2007, 3, 500 FROM dual UNION ALL
                    SELECT 2007, 4, 40 FROM dual)
SELECT YEAR, MONTH, SUM(production) over (ORDER BY YEAR,MONTH)
FROM t  
  
>
   	YEAR	MONTH	SUM(PRODUCTION)OVER(ORDERBYYEA
1	2007	1	1000
2	2007	2	1800
3	2007	3	2300
4	2007	4	2340

Donc pour le mois de mars, j'ai bien production mars +production février + production janvier
C'est pas cela qu'il veut ?

**Loyd1974** · 07/11/2007, 11h55

oups, je suis fatigué, j'ai écrit une énormité, le vrai code c'est :

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
 
WITH t AS (SELECT 2007 AS YEAR, 1 AS MONTH, 1000 AS production FROM dual UNION ALL
                      SELECT 2007, 2, 800 FROM dual UNION ALL
                    SELECT 2007, 3, 500 FROM dual UNION ALL
                    SELECT 2007, 4, 40 FROM dual UNION ALL
                                        SELECT 2006,12,10000 FROM dual)
SELECT YEAR, MONTH, SUM(production) over (PARTITION BY YEAR ORDER BY YEAR,MONTH) AS cumul
FROM t  
 
ce qui donne :
       YEAR    MONTH    CUMUL
1    2006    12    10000
2    2007    1    1000
3    2007    2    1800
4    2007    3    2300
5    2007    4    2340

J'avais oublié de partitionner par l'année, car je pense qu'au mois de janvier 2007 les compteurs repartent à zéro et que tu ne veux pas les données de 2006, sinon, si tu fais janvier 2007 = janvier 2007 + decembre 2006 + novembre 2006 + ... alors, tu n'as pas besoin de partitioner

Invité · 07/11/2007, 14h57

Envoyé par Jerome_Mtl

un éclair de génie :

Code :

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

1
2
3
4
5
SELECT a.annee, a.mois, sum(b.prod) 
FROM ma_table A, ma_table B 
WHERE a.annee = b.annee  
  AND b.mois <= a.mois 
GROUP BY a.annee, a.mois ;

Et ça, ça ne résoud pas le problème ?

**orafrance** · 07/11/2007, 15h02

Envoyé par Jerome_Mtl

Et ça, ça ne résoud pas le problème ?

probablement mais c'est moins performant puisque tu accédes 2 fois à la table... par ailleurs, un trigger est plus "trivial" puisqu'il fait le calcul à l'insertion au lieu de le faire à chaque SELECT

Invité · 07/11/2007, 15h12

Envoyé par orafrance

probablement mais c'est moins performant puisque tu accédes 2 fois à la table... par ailleurs, un trigger est plus "trivial" puisqu'il fait le calcul à l'insertion au lieu de le faire à chaque SELECT

Bon, ça me rassure.
Au niveau perf, j'imagine bien qu'un group by avec 2 fois la même table, on a déjà vu mieux. Mais si c'est une petite table, ça peut le faire.

**orafrance** · 07/11/2007, 15h15

autant prendre l'habitude tout de suite de choisir des solutions performantes

Note que je n'ai pas vérifié que ta solution fonctionne bien, faut faire le test

**Loyd1974** · 07/11/2007, 15h15

Envoyé par orafrance

probablement mais c'est moins performant puisque tu accédes 2 fois à la table... par ailleurs, un trigger est plus "trivial" puisqu'il fait le calcul à l'insertion au lieu de le faire à chaque SELECT

Juste une précision, le trigger peut être pénalisant si des insert en masse sont effectuées (mais bon, dans le cas présent, j'en doute)
Par contre, si les données sont modifiées après coup (ce qui me semble être le cas, une table sur laquelle on additionne au fur et à mesure), ton trigger ne prend pas en compte cette possibilité. Il faut peut-être l'adapter pour prendre en compte les UPDATE voire les DELETE.

La méthode analytique est largement plus performante que d'accéder 2 fois la table effectivement.
La méthode du trigger permet d'avoir le résultat stockée donc c'est encore plus performant au moment de l'interrogation mais elle comporte un risque si on ne maitrise pas les flux fonctionnels (par exemple, si on ajoute lors de l'insert mais qu'on ne retranche pas lors du delete)

**orafrance** · 07/11/2007, 15h57

Parfaitement, le compromis pourrait être la vue matérialisée rafraichie ON COMMIT

**mnitu** · 07/11/2007, 16h14

Sauf que cella ne marche pas

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
 
drop table ma_table
/
create table ma_table(
annee number,
mois number,
prod number
)
/
CREATE OR REPLACE TRIGGER mon_trigger
BEFORE INSERT ON ma_table
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; -- evite ora-04091
BEGIN
IF :new.mois > 1 THEN
  SELECT prod + :new.prod
    INTO :new.prod
    FROM ma_table
   WHERE mois = :new.mois - 1
     AND annee = :new.annee;
END IF;
END;
/
INSERT INTO ma_table VALUES (2007,2,15)
/

Et il faut aussi bien préciser à l'utilisateur qu'il doit faire commit après chaque insert

Avec toute le respect, mais mois je n'appel pas ça une solution

**orafrance** · 07/11/2007, 16h18

euh... si je ne m'abuse pour enregistrer une commande UPDATE, DELETE ou INSERT, il faut faire un COMMIT... j'vois pas bien pourquoi il faudrait le préciser.

Quand tu rollbackes la ligne n'est pas insérée donc le cumul non plus... j'vois pas en quoi ça ne fonctionne pas

PS : évidemment dans la solution je considère qu'on n'insére pas mars avant février, je suis sûr que ceux qui passeront sur ce topic sauront adapté l'exemple à leur besoin

**mnitu** · 07/11/2007, 16h28

Envoyé par orafrance

euh... si je ne m'abuse pour enregistrer une commande UPDATE, DELETE ou INSERT, il faut faire un COMMIT... j'vois pas bien pourquoi il faudrait le préciser.

Sauf si il s'agit d'une transaction

Essayez quelque chose de type

Code :

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

1
2
3
4
5
 
insert into ma_table
select 2007,1,10 from dual
union 
select 2007,2,15 from dual

Claculer un cumul

SQL Oracle

Discussions similaires

Partager

Partager