Discussion :

[fatsora]

Bonjour la liste,

voici ce qui ma tracasse j'aimerais
faire une operation du type prix_t1 -prix_t2 sur une suite de valeur

et j'ai pensé à lag et lead
mais un probleme a la fin et au debut car les lignes sont vides
comment faire pour remplir ces lignes avec la derniere valeur

ce que je veux obtenir

TIME|PRICE|BEFORE|AFTER
30/01/2008 08:00:00|49.52|49.52|49.53
30/01/2008 08:00:00|49.53|49.52|49.55
30/01/2008 08:00:00|49.55|49.53|49.56
30/01/2008 08:00:00|49.56|49.55|49.57
30/01/2008 08:00:00|49.57|49.56|49.6
30/01/2008 08:00:00|49.6|49.57|49.62
30/01/2008 08:00:00|49.62|49.6|49.7
30/01/2008 08:00:00|49.7|49.62|49.72
30/01/2008 08:00:00|49.72|49.7|49.73
30/01/2008 08:00:00|49.73|49.72|49.75
30/01/2008 08:00:00|49.75|49.73|49.78
30/01/2008 08:00:00|49.78|49.75|49.8
30/01/2008 08:00:00|49.8|49.78|49.81
30/01/2008 08:00:00|49.81|49.8|49.83
30/01/2008 08:00:00|49.83|49.83|49.83

voila ce que j'objtiens

TIME|PRICE|BEFORE|AFTER
30/01/2008 08:00:00|49.52|49.52|49.53
30/01/2008 08:00:00|49.53|49.52|49.55
30/01/2008 08:00:00|49.55|49.53|49.56
30/01/2008 08:00:00|49.56|49.55|49.57
30/01/2008 08:00:00|49.57|49.56|49.6
30/01/2008 08:00:00|49.6|49.57|49.62
30/01/2008 08:00:00|49.62|49.6|49.7
30/01/2008 08:00:00|49.7|49.62|49.72
30/01/2008 08:00:00|49.72|49.7|49.73
30/01/2008 08:00:00|49.73|49.72|49.75
30/01/2008 08:00:00|49.75|49.73|49.78
30/01/2008 08:00:00|49.78|49.75|49.8
30/01/2008 08:00:00|49.8|49.78|49.81
30/01/2008 08:00:00|49.81|49.8|49.83
30/01/2008 08:00:00|49.83|49.81|49.83

ie la derniere ligne 49.81 a remplacer par 49.83

la requete
select time ,price, lag (price) over (partition by time order by price) before ,
lead (price) over (partition by time order by price) after
from ( select distinct trunc(server_time, 'hh24') + (trunc(to_char(server_time,'mi')))/24/60 time ,id_stock,price from MY_TABLE
where id_stock='BLA BLA '
and server_time < to_timestamp('30012008:09:03:00','DDMMYYYY:HH24:MI:SS'))
group by time,price ,id_stock
order by time,price;

[Marc Musette]

peut-être n'ai-je rien compris , mais 49.81 est bien le price "before" ? non ?