Calculation off of a calculated field in Vertica

Question

I have a list of records from which I need to calculate a new field - I'll call it "calculation" Each row depends upon the previous rows number. The first index of each ID's "calculation" field is provided. See below for a screenshot from excel displaying what I'm trying to do, but for millions of records, partitioned by ID

My query below breaks, since calculation does not yet exist

select ID, transaction, transaction-lag(calculation) over (partition by ID) as calculation from db

Is this possible in Vertica?

Answer 1

Your results depend on the ordering of the data. However, SQL tables represent unordered sets. If I assume you have a column that specifies the ordering, then you want something like this:

select t.*,
       (first_value(calculation) over (partition by id order by <ordering col>) -
        sum(transaction) over (partition by id order by <ordering col>)
       ) as calculation
from t