I need to generate an increment field based on the difference bettwen current and previous value from another field:
So for example, this table would look like this:
I have this data in postgresql and my query is currently generating the table in first image, but I need it to create the second one.
Would be thankful for any hints.
I would recommend using lag():
select t.*,
(totalreply -
lag(totalreply, 1, totalreply) over (order by month)
) as incremental_totalreply
from t;
Note that this uses the 3-argument form of lag() so the first value is 0 rather than NULL.
You can use WINDOW FUNCTION, try this:
select month, totalread, (totalread -
lead(totalread, -1, totalread) over(order by totalread))
from table1;
Reading doc, lead:
returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null
