Discussion :

[twixi]

Bonjour à vous tous,

Bien que nous sommes presque mi-janvier, je vous souhaite, vous lecteur de ce post une très bonne année 2013.

Mon problème est le suivant :

jVoici mon jeu de données :

Code :

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

1
2
3
4
5
ID_CLI    DATE_MOIS        MNT_1  MNT_ 2
1        01/01/2013         10       5       
1        01/12/2012         10       5
2        01/01/2013         20       30
2        01/12/2012         20       30

Je dois récupérer la somme des MNT_1 et MNT2 de chaque ID_CLI mais du mois précédent.

Résultat voulu :
[

Code :

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

1
2
3
4
5
ID_CLI    DATE_MOIS        MNT_1  MNT_ 2  SOM_M1(MNT)
1        01/01/2013         10       5        35
1        01/12/2012         20       15      null 
2        01/01/2013         30       25       75 
2        01/12/2012         40       35      null 

j'ai essayé de chercher au niveau des requête analytique, notamment grace au 1 rows preceding mais je sèche .

Mon jeu de données :

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
create table test ( id_cli number(1), dat_mois date , mnt_1 number(3) , mnt_2 number(3),  som number(4));
 
insert into test (id_cli, dat_mois, mnt_1, mnt_2,som)
values
(1,'01/01/2013',10,10,20);
insert into test (id_cli, dat_mois, mnt_1, mnt_2,som)
values
(1,'01/12/2012',10,0,10);
insert into test (id_cli, dat_mois, mnt_1, mnt_2,som)
values
(1,'01/11/2012',10,5,15);
insert into test (id_cli, dat_mois, mnt_1, mnt_2,som)
values
(2,'01/01/2013',20,10,30);
insert into test (id_cli, dat_mois, mnt_1, mnt_2,som)
values
(2,'01/12/2012',30,40,70);
insert into test (id_cli, dat_mois, mnt_1, mnt_2,som)
values
(2,'01/11/2012',20,5,25);
insert into test (id_cli, dat_mois, mnt_1, mnt_2,som)
values
(2,'01/10/2012',200,500,700);

Ma requête :

Code :

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

1
2
3
4
5
with somme_m1 as (select id_cli, dat_mois, mnt_1, mnt_2, som from test order by id_cli, dat_mois desc)
select id_cli, dat_mois, mnt_1,  mnt_2,som,
first_value(som) over(partition by id_cli order by dat_mois asc  rows 1 preceding) as somme_m1
from somme_m1;
from vect_fm;

Cela fonctionne bien sauf pour le MIN du DAT_MOIS qui ne retourne pas la valeur NULL mais la valeur SOM.
Une idée ?
Merci à vous !!

[jkofr]

Hello,

Regarde un peu sur ce poste:http://www.developpez.net/forums/d11...te-sql-tordue/

Cela devrai t'aider pour la méthode.

Jko

[owenho]

ce code marche seulement si vous avez 2 enregistrements avec le méme id,

Code :

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

1
2
3
4
update cli
set somme = (  select MNT_1+MNT_2from cli  where date_mois= (select min(date_mois) from cli where ID_CLI=&ID_CLI) and ID_CLI=&ID_CLI)
where ID_CLI=&ID_CLIand date_mois=(select max(date_mois) from cli where ID_CLI=&ID_CLI)
/

une variable de substitution pour id , pour faire entrez l'id que vous souhaitez calculez ,
je sais bien que ce code n'est pas pratique, mais marche avec 2 enregistrements avec le méme id,

Crdt

[jkofr]

Hello,

En admettant que nous ayons une ligne par article/mois je le ferais comme ceci.

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
with test as 
(
select 1 id_cli,'01/01/2013' dat_mois,10 mnt_1,5 mnt_2 from dual
union all
select 1 ,'01/12/2012' ,20 ,15 from dual
union all
select 1 ,'01/11/2012' ,15 ,45 from dual
union all
select 2 ,'01/01/2013' ,30 ,25 from dual
union all
select 2 ,'01/12/2012' ,40 ,35 from dual
)
select id_cli,dat_mois, mnt_1,mnt_2, case 
                                      when lead (id_cli,1) over(ORDER BY id_cli asc, to_char (to_date (dat_mois),'yyyy-mm') desc) <> id_cli or 
                                           lead (id_cli,1) over(ORDER BY id_cli asc, to_char (to_date (dat_mois),'yyyy-mm') desc) is null 
                                           Then null else to_number(lead (mnt_1,1) over(ORDER BY id_cli asc, to_char (to_date (dat_mois),'yyyy-mm') desc)) +  to_number(lead (mnt_2,1) over(ORDER BY id_cli asc, to_char (to_date (dat_mois),'yyyy-mm') desc))
                                     end as Som
 from test

Code :

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

1
2
3
4
5
6
7
8
 
D_CLI DAT_MOIS   MNT_1 MNT_2 SOM
------ ---------- ----- ----- ---
     1 01/01/2013    10     5  35 
     1 01/12/2012    20    15  60 
     1 01/11/2012    15    45     
     2 01/01/2013    30    25  75 
     2 01/12/2012    40    35

Bonne journée
Jko

[Waldar]

Ça me paraît bien inutilement compliqué jkofr, il suffit d'utiliser la notion de partition dans la fenêtre :

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 test AS 
(
SELECT 1 id_cli, date '2013-01-01' dat_mois, 10 mnt_1,  5 mnt_2 FROM dual union ALL
SELECT 1       , date '2012-12-01'         , 20      , 15       FROM dual union ALL
SELECT 1       , date '2012-11-01'         , 15      , 45       FROM dual union ALL
SELECT 2       , date '2013-01-01'         , 30      , 25       FROM dual union ALL
SELECT 2       , date '2012-01-01'         , 40      , 35       FROM dual
)
  select id_cli
       , dat_mois
       , mnt_1
       , mnt_2 
       , lag(mnt_1 + mnt_2) over(partition by id_cli order by dat_mois asc) as mnt_1_2
    from test
order by id_cli    asc
       , dat_mois desc;

Résultat :

ID_CLI   DAT_MOIS MNT_1 MNT_2 MNT_1_2
------ ---------- ----- ----- -------
     1 01/01/2013    10     5      35
     1 12/01/2012    20    15      60
     1 11/01/2012    15    45        
     2 01/01/2013    30    25      75
     2 01/01/2012    40    35        

[jkofr]

Magnifique:-)

Au début j'avais envisagé, à tord, que plusieurs lignes pouvaient porter sur le même article dans le même mois...

J'ai pas pensé à simplifier.

jko