Somme incrémentale comme Excel

Bonjour,

la question parait simple, mais je galère pour l'implémentation. (ORA 11g)

Je veux implémenter cette formule excel qui somme la valeur d'une colonne avec son précédent. Par exemple pour la 9 eme ligne. I9 = : H9+I8-1

J'ai H en entrée et je veux calculer I comme sur cette série

H I
0 0
4 3
1 3
2 4
1 4
3 6
9 14
1 14
1 14
0 13
0 12
3 14

Je n'arrive à rien même avec les requêtes analytiques

Bonjour,
Tu as une réponse là.

tatayo.

Essayez ainsi :
https://dbfiddle.uk/?rdbms=oracle_21...6c5032ec62ae69

Code:

---

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

create table data ( H number(1) , ord number(2) );   insert into data (H, ord) select 0, 1 from dual union all select 4, 2 from dual union all select 1, 3 from dual union all select 2, 4 from dual union all select 1, 5 from dual union all select 3, 6 from dual union all select 9, 7 from dual union all select 1, 8 from dual union all select 1, 9 from dual union all select 0, 10 from dual union all select 0, 11 from dual union all select 3, 12 from dual;   select H , 1 + sum(H-1) over(order by ord asc) as I from data order by ord asc;   H I -- -- 0 0 4 3 1 3 2 4 1 4 3 6 9 14 1 14 1 14 0 13 0 12 3 14

---

Grand merci Waldar, je valide ta réponse ! Je n'avais pas mis le 1+ avant le sum(h-1) over...

J'en profite (et c'est la dernière fois) en complexifiant la formule originale (excel like) par : MAX(0;H9+I8-1) pour empêcher les cumuls négatifs

Quand je fais un case when 1 + sum(H-1) over(order by ord asc) < 0 then 0 else 1 + sum(H-1) over(order by ord asc) end , le cumul négatif est mis à 0, mais le "vrai cumul est toujours négatif" empêchant toute remontée de la somme dans les positifs

On s'aperçoit des sommes négatives à partir de la 36em ligne où les I sont positifs alors que la formule plonge dans les négatifs

H I FORMULE 1+sum(h-1) etc....
0 0 0
4 3 3
1 3 3
2 4 4
1 4 4
3 6 6
9 14 14
1 14 14
1 14 14
0 13 13
0 12 12
3 14 14
0 13 13
1 13 13
0 12 12
0 11 11
2 12 12
0 11 11
0 10 10
0 9 9
0 8 8
0 7 7
0 6 6
0 5 5
0 4 4
0 3 3
0 2 2
2 3 3
3 5 5
1 5 5
0 4 4
0 3 3
0 2 2
0 1 1
0 0 0
0 0 -1
0 0 -2
0 0 -3
0 0 -4
0 0 -5
0 0 -6
1 0 -6
0 0 -7
0 0 -8
2 1 -7
0 0 -8
0 0 -9
0 0 -10
1 0 -10
4 3 -7
4 6 -4
0 5 -5
0 4 -6
0 3 -7
0 2 -8
0 1 -9
0 0 -10
0 0 -11
1 0 -11
2 1 -10
0 0 -11
0 0 -12
0 0 -13
0 0 -14
0 0 -15
0 0 -16
0 0 -17
0 0 -18
0 0 -19
0 0 -20
0 0 -21
0 0 -22
0 0 -23
1 0 -23
1 0 -23
0 0 -24
1 0 -24
0 0 -25
0 0 -26
0 0 -27
0 0 -28
0 0 -29
2 1 -28
1 1 -28
0 0 -29
0 0 -30
0 0 -31
0 0 -32
1 0 -32
0 0 -33
0 0 -34
0 0 -35

Bonjour,

Pour faire du Excel en SQL Oracle, rien de tel que d'utiliser la clause MODEL :

Code:

---

1 2 3 4 5 6 7

select * from t model dimension by (ord) measures(h, h as i) rules( i[ord > 1] = greatest(0, h[cv()] + i[cv()-1] -1) );

---

Tu vois que la formule dans la clause RULES s'écrit comme ta formule dans Excel. J'ai ajouté un GREATEST pour gérer les cumuls négatifs comme tu l'indiquais. Par contre j'ai considéré une colonne ORD qui permet de rendre unique l'adressage du modèle (d'ailleurs il faut bien se baser sur un ordre des lignes pour faire le cumul dans le bon ordre).

Ou sinon avec la récursivité :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

with cte_recurs (H, I, ord) as ( select H, greatest(H-1, 0), ord from data where ord = 1 union all select d.H , greatest(d.H-1 + c.I, 0) , d.ord from cte_recurs c join data d on d.ord = c.ord + 1 ) select H, I from cte_recurs order by ord asc;

---

Merci Vanagreg et Waldar pour votre célérité et la justesse de vos réponses.

Les approches MODEL et récursives répondent au mieux à cette problématique de transposition de la logique procédurale excel à celle non procédurale SQL