Get biggest difference in successive timestamps with additional constraint?

Question

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.

Please show us how the final result of the query should look like. — Vladimir Baranov, Mar 01 '17 at 10:09

MT0 · Accepted Answer · 2017-03-01T10:30:37.593

To answer the question prior to its edit:

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;

Updated

To get it for each group of start/end items (which appears to be what you expected output is showing - since it has multiple rows):

SELECT name,
       creation_time,
       next_creation_time,
       next_creation_time - creation_time AS difference,
       type
FROM   (
  SELECT i.*,
         ROW_NUMBER() OVER (
           PARTITION BY name, start_group
           ORDER BY next_creation_time - creation_time DESC, creation_time ASC
         ) AS rn
  FROM   (
    SELECT name,
           type,
           creation_time,
           LEAD( creation_time ) OVER ( PARTITION BY name ORDER BY creation_time )
             AS next_creation_time,
           SUM( CASE type WHEN 'start' THEN 1 END )
             OVER( PARTITION BY name ORDER BY creation_time )
             AS start_group
    FROM   items
  ) i
  WHERE type != 'end'
)
WHERE rn = 1;

Thanks! That function is exactly what I searched for, and magnitudes faster then the query I built upon. — Jdv, Mar 01 '17 at 10:22

score 0 · Answer 2 · answered Mar 01 '17 at 10:10

Per my uderstanding . This must be a inclusion of column "TYPE" in the subquery that can be used as a filter in Inline View. Hope it helps.

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,
    type
  FROM items i
  )
WHERE (NEXT_CREATION-CREATION) IS NOT NULL
and type                       <> 'start'
ORDER BY difference DESC;

score 0 · Answer 3 · answered Mar 01 '17 at 10:14

If you need the max difference for each name value, something like that should work:

SELECT i1.NAME, MAX(i2.creation_time - i1.creation_time) difference
FROM items i1
INNER JOIN items i2 ON i1.name = i2.name AND i1.creation_time <= i2.creation_time
WHERE 'start' NOT IN (i1.type, i2.type)
GROUP BY i1.name;

Get biggest difference in successive timestamps with additional constraint?

3 Answers3