royb royb - 1 year ago 177
SQL Question

vertica sql delta

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

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

i work on vertica data base and i want to create view/projection of this table on my DB.

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

Answer Source

You can use lag() for this purpose:

select id, timestamp,
       coalesce(timestamp - lag(timestamp) over (order by id), 0) as delta
from t;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download