access to previous/next row values

[mfulton26]

I'd like a way to compute the difference between a value in a column with the previous or next value over a partition. I believe several SQL solutions use LEAD() and LAG() functions but there may be other/better ways to do it.

[mfulton26]

A way to access other rows via user defined functions could be helpful too.

[Ownerbusinessjjfm]

I'd like a way to compute the difference between a value in a column with the previous or next value over a partition. I believe several SQL solutions use LEAD() and LAG() functions but there may be other/better ways to do it.

How and what should it be done

[mfulton26]

Example using window functions:

select year, 
       month,
       fixes,
       fixes - lag(fixes) over (order by year, month) as increase,
from the_table;

source: https://stackoverflow.com/questions/24691462/postgresql-calculate-difference-between-rows

References:

• https://www.postgresql.org/docs/current/tutorial-window.html
• https://www.postgresql.org/docs/current/functions-window.html
• https://www.sqlite.org/windowfunctions.html

[mechatroner]

Thank you for the feedback! I agree it would be cool to support window functions including lag but I haven't yet even started looking into how hard it would be to add them because I suspect it is a complex change. But IMO it is doable so when I have more time I will look into this.