#Postgres lag function how to

What is important to see here is that the first row contains a NULL entry because there is no known difference to the previous row.

Production - lag(production, 1) OVER (ORDER BY year) AS diff Once you find the correct row, the rest is easy: In my example I have ordered the data by year to make sure that the previous year can indeed be found in the previous row. To do that SQL provides the OVER-clause:Ĭypex=# SELECT country, year, production, What is the previous row? We will need some kind of order. However, there is a question naturally arising here: If we want to access the previous row. If you want to calculate the difference between two rows, you can make use of the “lag” function.

#Postgres lag function how to

lag: How to access a different row in SQL Otherwise you have to load the text file in a different way. If you are a superuser you can use COPY … FROM PROGRAM directly. Note that this is not a complete tutorial about analytics and windowing functions but just a short introduction to what can be done in general. To answer this question I have decided to share some simple queries outlining what can be done. How can you calculate the difference between the current and the previous row? One of the questions which interested them is related to calculating the difference between values in timeseries data. Recently we had some clients who had the desire to store timeseries in PostgreSQL.