Cumul progressif [FAQ]

**lper** · 28/07/2005, 14h14

Bonjour,
j'aimerai connaître la technique si elle existe d'obtenir le cumul progressif d'un montant.

Exemple :

Record 1 : montant = 10
Record 2 : montant = 20
Record 3 : montant = 5

Résultat attendu :

10
30
35

Je travaille sous Oracle 8i.

Merci d'avance pour toutes suggestions.

Xo · 28/07/2005, 14h23

J'ai un exemple, dans un cas particulier :
Soit une table de lignes de factures (Ligne) comportant les champs suivants :
- idFacture
- Numero,
- Montant

Voici une requête qui devrait répondre à tes besoins (jointures Oracle 8, pas de JOIN, ce qui devrait te convenir) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
  Numero, 
  Montant,
  (SELECT SUM(S.Montant)
     FROM Ligne S
	WHERE P.idFacture = S.idFacture
	  AND P.Numero >= S.Numero) AS SOMME_PARTIELLE
FROM
  Ligne P
WHERE 
  idFacture = monNumeroDeFacture
ORDER BY Lif_Ordr

Tu constuit la somme partielle en faisant dans une sous-requête la somme des montants des lignes dont le n° est inférieur ou égal à celui en cours. Les alias sont P pour Principale et S pour secondaire.

**lper** · 28/07/2005, 14h26

Merci beaucoup, je m'empresse de tester tout en attendant d'autres éventuelles idées.

**lper** · 28/07/2005, 15h51

Hélas, la méthode marche si l'on a un numéro incrémenté dans l'ordre d'affichage des lignes, ce que je n'ai pas !

J'ai tenté d'utiliser le rownum mais qui change aussi en fonction du tri de la requête !

Pas d'autres idées ?

Xo · 28/07/2005, 17h11

Envoyé par lper

Hélas, la méthode marche si l'on a un numéro incrémenté dans l'ordre d'affichage des lignes

Effectivement, il faut avoir un critère de tri dans le cas que je t'ai proposé ... Il me semble avoir vu passer des sujets concernant les pseudos-colonnes pour le tri (forum Oracle ?) il y a déjà un moment de ça, peut-être devrais-tu chercher de ce côté ?

Pas d'autres suggestions en ce qui me concerne, désolé

[Edit] mets ta requête en ligne, si tu as un "ORDER BY", tu dois pouvoir écrire une clause de comparaison dans ta sous-requête, non ? [/Edit]

**lper** · 29/07/2005, 12h12

Voici la requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
select NO_CLIENT,
         ANNEE,
         MOIS,
         ID_SEQU,
         MONTANT 
from COMPTA
where NO_CLIENT = :NO_CLIENT_SEL
order by ANNEE, 
             MOIS,
             ID_SEQU

Le problème est que le ID_SEQU n'est pas dans l'ordre d'affichage par année et mois et je ne vois pas quelle clause de comparaison à écrire.

Xo · 29/07/2005, 14h02

Si id_Sequ est la clé de ta table, et (Annee, Mois, idSequ) l'ordre d'affichage, la requête suivante devrait fonctionner :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT NO_CLIENT,
  ANNEE,
  MOIS,
  ID_SEQU,
  MONTANT,
  (SELECT SUM(S.Montant)
     FROM COMPTA  S
    WHERE ((P.ANNEE > S.ANNEE)
        OR (P.ANNEE = S.ANNEE AND P.MOIS > S.MOIS) 
        OR (P.ANNEE = S.ANNEE AND P.MOIS = S.MOIS AND P.ID_SEQU > P.ID_SEQU) )
) AS SOMME_PARTIELLE
 FROM COMPTA P
 WHERE NO_CLIENT = :NO_CLIENT_SEL
 ORDER BY ANNEE, MOIS, ID_SEQU

**lper** · 29/07/2005, 15h37

Un mot : Génial !

Merci beaucoup pour ton aide très précieuse. (juste une petite correction sur P.ID_SEQU > S.ID_SEQU, plus une condition sur le No de client dans la requête imbriquée)..

Xo · 29/07/2005, 15h46

Envoyé par lper

Merci beaucoup pour ton aide très précieuse. (juste une petite correction sur P.ID_SEQU > S.ID_SEQU, plus une condition sur le No de client dans la requête imbriquée)..

Je me disait bien qu'il manquait une jointure, je n'avais pas retesté ...
Alors je corrige :

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
SELECT NO_CLIENT,
  ANNEE,
  MOIS,
  ID_SEQU,
  MONTANT,
  (SELECT SUM(S.Montant)
     FROM COMPTA  S
    WHERE P.NO_CLIENT = S.NO_CLIENT
      AND ((P.ANNEE > S.ANNEE)
        OR (P.ANNEE = S.ANNEE AND P.MOIS > S.MOIS)
        OR (P.ANNEE = S.ANNEE AND P.MOIS = S.MOIS AND P.ID_SEQU > S.ID_SEQU) )
) AS SOMME_PARTIELLE
 FROM COMPTA P
 WHERE NO_CLIENT = :NO_CLIENT_SEL
 ORDER BY ANNEE, MOIS, ID_SEQU

Envoyé par lper

Un mot : Génial !

Hum, pas tant que ça, je viens de relire un lien indiqué par Magnus dans un autre post, et il semblerait (vu que comme moi tu travailles sous Oracle) que le Row Value Constructor puisse fonctionner (et simplifier un peu la requête !) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT NO_CLIENT,
  ANNEE,
  MOIS,
  ID_SEQU,
  MONTANT,
  (SELECT SUM(S.Montant)
     FROM COMPTA  S
    WHERE P.NO_CLIENT = S.NO_CLIENT
      AND (P.ANNEE, P.MOIS, P.ID_SEQU) > (S.ANNEE, S.MOIS, S.ID_SEQU))) AS SOMME_PARTIELLE
 FROM COMPTA P
 WHERE NO_CLIENT = :NO_CLIENT_SEL
 ORDER BY ANNEE, MOIS, ID_SEQU

**Médiat** · 29/07/2005, 15h57

Pure curiosité de ma part : est-ce que la requête suivante fonctionne et laquelle est la plus performante ?

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT P.NO_CLIENT, 
  P.ANNEE, 
  P.MOIS, 
  ID_SEQU, 
  P.MONTANT, 
  SUM(S.Montant) - P.MONTANT AS SOMME_PARTIELLE 
FROM COMPTA P INNER JOIN COMPTA S ON P.NO_CLIENT = S.NO_CLIENT AND (P.ANNEE, P.MOIS, P.ID_SEQU) >= (S.ANNEE, S.MOIS, S.ID_SEQU)
WHERE P.NO_CLIENT = :NO_CLIENT_SEL 
GROUP BY P.NO_CLIENT, P.ANNEE, P.MOIS, ID_SEQU, P.MONTANT
ORDER BY P.ANNEE, P.MOIS, P.ID_SEQU

**lper** · 29/07/2005, 16h36

J'ai testé vos 2 solutions et j'ai toujours la même erreur : "Invalid relational operator" apparament sur le ">" du row value constructor.

Xo · 29/07/2005, 17h21

Envoyé par lper

Je travaille sous Oracle 8i.

Envoyé par Médiat

INNER JOIN

Seule la version 9 d'Oracle supporte les jointures "normalisées" (cf article de SQLPro)

Concernant le Row Value Constructor, je ne sais pas à partir de quelle version il est supporté ?

**Médiat** · 29/07/2005, 18h40

Envoyé par Xo

Concernant le Row Value Constructor, je ne sais pas à partir de quelle version il est supporté ?

Depuis très longtemps, sauf que cela ne marche pas comme cela, donc ma solution est nulle, sauf à remplacer la comparaison par un bidule compliqué et pas beau

!

Je suis revenu aux jointures vieille mode (pour être clean il faudrait donner un format au to_char, sur le N° de séquence en particulier):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT P.NO_CLIENT, 
  P.ANNEE, 
  P.MOIS, 
  ID_SEQU, 
  P.MONTANT, 
  SUM(S.Montant) - P.MONTANT AS SOMME_PARTIELLE 
FROM COMPTA P, COMPTA S 
WHERE P.NO_CLIENT = S.NO_CLIENT 
  AND to_char(P.ANNEE) || to_char(P.MOIS) || to_char(P.ID_SEQU >= to_char(S.ANNEE) || to_char(S.MOIS) || to_char(S.ID_SEQU) 
  AND P.NO_CLIENT = :NO_CLIENT_SEL 
GROUP BY P.NO_CLIENT, P.ANNEE, P.MOIS, ID_SEQU, P.MONTANT 
ORDER BY P.ANNEE, P.MOIS, P.ID_SEQU

**lper** · 15/08/2005, 09h38

Bonjour,

de retour de vacances

, je m'excuse pour cette réponse un peu tardive.

J'avais testé ta solution Médiat sous Oracle 9i, c'était donc bien un problème avec le Row value constructor.

Je reste donc sur la solution proposée par Xo qui me paraît satisfaisante pour l'instant et testerai la dernière solution quand j'en aurai le temps...

Merci à tous pour votre aide.

**lper** · 17/08/2005, 10h41

Rebonjour,
j'ai trouvé je pense une meilleure solution mais en utilisant les possiblités Oracle...
Je vous la livre quand même :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT P.NO_CLIENT,
            P.ANNEE, 
            P.MOIS,
            P.ID_SEQU,
            P.MONTANT,
SUM(P.MONTANT) over (PARTITION BY NO_CLIENT order by P.ANNEE, P.MOIS, P.ID_SEQU ROWS UNBOUNDED PRECEDING ) AS SOMME_PARTIELLE
FROM COMPTA P
WHERE P.NO_CLIENT = :NO_CLIENT_SEL
ORDER BY P.ANNEE, P.MOIS, P.ID_SEQU

Cumul progressif [FAQ]

Langage SQL

Discussions similaires

Partager

Partager