Menu
In SQL Server (Transact-SQL), the LAG function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table. To return a value from the next row, try using the LEAD function.
LAG() : SQL Server provides LAG() function which is very useful in case the current row values need to be compared with the data/value of the previous record or any record before the previous record. The previous value can be returned on the same record without the use of self join making it straightforward to compare.
The LEAD function is used to access data from SUBSEQUENT rows along with data from the current row. The LAG function is used to access data from PREVIOUS rows along with data from the current row. An ORDER BY clause is required when working with LEAD and LAG functions, but a PARTITION BY clause is optional.
LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.
I have a table like this,
event_id | date ----------+------------------------ 1703702 | 2013-06-25 07:50:57-04 3197588 | 2013-06-25 07:51:57-04 60894420 | 2013-06-25 07:52:57-04 60894420 | 2013-06-25 07:53:57-04 183503 | 2013-06-25 07:54:57-04 63116743 | 2013-06-25 07:55:57-04 63110451 | 2013-06-25 07:56:57-04 63116743 | 2013-06-25 07:57:57-04 63116743 | 2013-06-25 07:58:57-04 I'd like to apply the lag function but also a group by so I can find the time intervals between any particular event_id.
I'd like something like this:
SELECT event_id, difference FROM ( SELECT event_id, date - lag(date) over (order by date) as difference FROM table GROUP BY event_id ) t; I cannot however use GROUP BY with the LAG function. I'd like a result similar to the following:
63116743, {120, 60} 60894420, {60} ... ... So there was a 120s and 60s window between the events for the first id, and a 60s window for the second id.
Is there a way to do this? The output format is not too important as long as I can get it into an array in the end. I'm using Postgres 9.1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With