vertica sql delta

Question

I want to calculate delta value between 2 records my table got 2 column id and timestamp i want to calculate the delta time between the records

    id  |timestamp |delta
----------------------------------
    1   |100       |0
    2   |101       |1 (101-100)
    3   |106       |5 (106-101)
    4   |107       |1 (107-106)

I work with a Vertica data base and I want to create view/projection of this table on my DB.

Is it possible to create this calculate without using udf function?

Answer 1

You can use lag() for this purpose:

select id, timestamp,
       coalesce(timestamp - lag(timestamp) over (order by id), 0) as delta
from t;