Jacob S Jacob S -4 years ago 91
SQL Question

Get biggest difference in successive timestamps with additional constraint?

I have a table

items
which represents moving items. Among others, this table has the PK
name
, the timestamp
creation_time
which contains the time of the creation of the entry and the string
type
which can either be
start
,
move
or
end
with
move
entries coming inbetween
start
and
end
.

A part of the table may look like this:

name creation_time type
_________________________________
i1 2017-03-01 10:00:01 start
i1 2017-03-01 10:00:02 move
i1 2017-03-01 10:00:08 move
i1 2017-03-01 10:00:10 end
i1 2017-03-01 10:00:31 start
i1 2017-03-01 10:00:33 move
i1 2017-03-01 10:00:37 end


What I want to get is the biggest difference(s) of successive timestamps where the type of the second timestamp is not
start
(I do not care for the times between an
end
and a
start
).

Based on this SO question I've come with the following query which does not yet take into account the type of the entries:

select name, creation, next_creation, (next_creation-creation) difference from (
select name, creation, (
select min(creation) from items i2
where i2.name=i.name
and i2.creation > i.creation
) as next_creation
from items i
)
where (next_creation-creation) is not null
order by difference desc


My problem is that I don't know how to properly filter for the type now. To my understanding, I'd have to get the type from the innermost query, because I care for the type of the second entry, and then add
and type<>'start'
to the where clause of the outermost query - but I can't get two values from the subquery, right?

Edit: The results I expect would look like this:

name creation next_creation difference action
i1 2017-03-01 10:00:02 2017-03-01 10:00:08 6s move
i1 2017-03-01 10:00:33 2017-03-01 10:00:37 4s end
[...]


As you see, there is no entry for the time between the first
end
and the second
start
, that's what want to accomplish by filtering for the type.

There is also, for example, no entry for the time between the very first and very last entry because I want the difference between successive entries.

Answer Source

Use the LAG analytic function:

SELECT name,
       MAX( time_difference ) AS max_time_difference
FROM   (
  SELECT name,
         type,
         creation_time
           - LAG( creation_time ) OVER ( PARTITION BY name ORDER BY creation_time )
           AS time_difference
  FROM   items
)
WHERE type != 'start'
GROUP BY name;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download