Discussion :

[phyllobius]

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

[tatayo]

Bonjour,
Tu as une réponse là.

tatayo.

[Waldar]

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

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
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

[phyllobius]

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

[vanagreg]

Bonjour,

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

Code :

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

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).

[Waldar]

Ou sinon avec la récursivité :

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
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;

[phyllobius]

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