Sql = how to calculate the return vector with a time series of prices?

Question

Hello, I have a table which has columns of price and date, ordered by the ascending dates. I need to calculate from this a return vector where return = price ( i) / price ( i- 1). The time is not time based, which means that one record can be at 9h34, the next at 9h35, then 9h40 etc...

I have found the following topic: SQL Syntax for calculating results of returned data but in Oracle I can't use order by in a subquery, could you please help me?

Answer 1

In Oracle, you could use the lag analytic function:

select 
     price / (lag(price) over (order by i))
,    ...
from PriceHistory

Here, lag(price) over (order by i) returns the price of the previous row, in a set ordered by the i column.