CODEWITHSUNDEEP
postgresqllagwindow-functions
BullyWiiPlaza
BullyWiiPlaza

Reputation: 19225

Accessing current row value with lag function

I want to calculate the difference between the previous and the current column and make it a new column named increase. For this, I'm using the lag window function. The value of the first column is not defined since no previous column exists. I know that a 3rd parameter specifies the default value. However, it depends. For the first row, I want to use the value of another column e.g. the one of count from that current row. This assumes that 0 is increased to count for the first row which is what I need. Specifying the column name as 3rd argument for the lag function does not work correctly and neither does using 0. How can it be done? I'm getting strange results such as quite a random result or even negative numbers.

SELECT *, mycount - lag(mycount, 1) OVER (ORDER BY id, messtime ASC) AS increase FROM measurements;

Window functions cannot be nested either:

ERROR: window function calls cannot be nested

Upvotes: 0

Views: 1792

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

There is another issue with your query: So far your results are in random order, so you may think you are seeing problems that don't exist.

Add ORDER BY id, messtime to your query to see the rows in order. Now you can compare one row with its predecessor directly. Are there still issues? If so, which exactly?

SELECT *, "count" - lag("count", 1) OVER (ORDER BY id, messtime) AS increase
FROM measurements
ORDER BY id, messtime;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

COUNT is a reserved word in SQL. It seems the DBMS thinks you want to nest COUNT and LAG somehow.

Use another column name or use quotes for the column:

SELECT *, "count" - lag("count", 1) OVER

Upvotes: 0

Related Questions