Test=# SELECT *, lag(t, 1) OVER (ORDER BY t)Īs you can see, the last column contains the date of the previous row. To do that in PostgreSQL (or in SQL in general) you can make use of the “lag” function: In most cases, it is simply vital to compare the current line with the previous line.
#Postgres lag function how to#
When dealing with timeseries, one of the most important things to learn is how to “look forward and backward”. PostgreSQL time series Preparing for timeseries analysis Our goal is to find and isolate them to do analysis on each of those continuous periods. There are three continuous periods in this set of data. Note that my timeseries is not continuous but interrupted. To make it easier, I just used two columns in my example. The next listing shows a little bit of sample data, which I used to write the SQL code you are about to see:ĬREATE TABLE t_series (t date, data int) For example: When was a user active? Or when did we receive data? This blog post tries to give you some ideas and shows, how you can actually approach this kind of problem. However, recently I stumbled across an interesting problem, which caught my attention: Sometimes you might want to find “periods” of activity in a timeseries. I have already written about timeseries and PostgreSQL in the past.
0 kommentar(er)
