Discussion :

[tinmarbusir]

Bonjour
Un petit problème complexe pour une requête analytique.
Je souhaite rapprocher pour chaque résultat dans la colonne C le minimum dans la colonne E avant le résultat C précédent (ou le début).
Un exemple de ce que je souhaite ci dessous.

E	C	Résultat
1
2	2	1
3
4
5
6	6	3
7	7	7
8
10	11	8
13	14	13
14

Avec le résultat ci dessous

C	Premier E
2	1
6	3
7	7
11	8
14	13

Savez vous le faire en requête analytique?

Bien à vous et merci d'avance

Martin

[skuatamad]

Par exemple :

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
SQL> with t as (
select 1 as e, NULL as c from dual union all
select 2 as e, 2 as c from dual union all
select 3 as e, NULL as c from dual union all
select 4 as e, NULL as c from dual union all
select 5 as e, NULL as c from dual union all
select 6 as e, 6 as c from dual union all
select 7 as e, 7 as c from dual union all
select 8 as e, NULL as c from dual union all
select 10 as e, 11 as c from dual union all
select 13 as e, 14 as c from dual union all
select 14 as e, NULL as c from dual
),
       groupe as (
select e, c
     , sum(c) over (order by e desc) as gp
  from t
)
select min(c) as c, min(e) as resultat
  from groupe
 group by gp
having min(c) is not NULL
 order by c;
 
         C   RESULTAT
---------- ----------
         2          1
         6          3
         7          7
        11          8
        14         13
 
SQL>

[tinmarbusir]

Ah super! Merci!
Mais mon champ c est de type date et il n'accepte pas les sommes. Comment puis je le convertir ou contourner le problème??

[skuatamad]

Peut être comme ça :

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
SQL> with t as (
select 1 as e, NULL as c from dual union all
select 2 as e, trunc(sysdate)+2 as c from dual union all
select 3 as e, NULL as c from dual union all
select 4 as e, NULL as c from dual union all
select 5 as e, NULL as c from dual union all
select 6 as e, trunc(sysdate)+6 as c from dual union all
select 7 as e, trunc(sysdate)+7 as c from dual union all
select 8 as e, NULL as c from dual union all
select 10 as e, trunc(sysdate)+11 as c from dual union all
select 13 as e, trunc(sysdate)+14 as c from dual union all
select 14 as e, NULL as c from dual
),
       groupe as (
select e, c
     , sum(case when c is not null then 1 end) over(order by e desc) as gp
  from t
)
select min(c) as c, min(e) as resultat
  from groupe
 group by gp
having min(c) is not NULL
 order by c;
 
C          RESULTAT
-------- ----------
29/06/17          1
03/07/17          3
04/07/17          7
08/07/17          8
11/07/17         13
 
SQL>

[tinmarbusir]

Merciiii
C'est super bien pensé! Merci beaucoup pour ton aide. C'est résolu!
J'ai modifié la requête un poil. Ceci afin d'avoir le groupe des e>max(c):

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
with t as (
select 1 as e, NULL as c from dual union all
select 2 as e, 2 as c from dual union all
select 3 as e, NULL as c from dual union all
select 4 as e, NULL as c from dual union all
select 5 as e, NULL as c from dual union all
select 6 as e, 6 as c from dual union all
select 7 as e, 7 as c from dual union all
select 8 as e, NULL as c from dual union all
select 10 as e, 11 as c from dual union all
select 13 as e, 14 as c from dual union all
select 14 as e, NULL as c from dual
),
       groupe as (
select e, c
     , NVL(sum(c) over (order by e desc) , 0)  as gp
  from t
)
select min(c) as c, min(e) as resultat
  from groupe
 group by gp
/*having min(c) is not NULL*/
 order by c;