Get start of a time period from repeated groups

id – id of place where action happened
t – time of action

+----+----------+ | id |    t     | +----+----------+ |  1 | 12:10:00 | |  1 | 12:10:05 | |  1 | 12:11:00 | |  1 | 13:04:03 | |  2 | 14:18:05 | |  2 | 15:00:09 | |  3 | 17:33:50 | |  1 | 20:03:14 | |  1 | 20:03:55 | |  1 | 20:10:23 | +----+----------+ 

Goal is to get this output

+----+----------+ | id |  start   | +----+----------+ |  1 | 12:10:00 | |  2 | 14:18:05 | |  3 | 17:33:50 | |  1 | 20:03:14 | +----+----------+ 

start – time of first action at id

Scripts with rank, min, etc. keep grouping rows with id=1
I do not know how to resolve this issue and did not find similar post
Here is sqlfiddle with scripts
Thanks in advance!

Asked on September 1, 2020 in Sql.
Add Comment
3 Answer(s)

This type of problem can be termed as gaps-and-islands problem and can be achieved by the difference of row numbers and aggregation.

select id,min(t),min(h) from ( select id       ,t       ,extract(hour from t) h       ,row_number() over (order by t) as seq1       ,row_number() over (partition by id order by t) as seq2   from records ) t group by id,(seq1-seq2) order by min(t); 

Reference: db<>fiddle

Answered on September 1, 2020.
Add Comment

It’s a typical gap and islands problem in which you can use some analytic functions such as ROW_NUMBER(), LAG(), LEAD() etc. mostly we consider applying analytic functions twice through manipulating PARTITION option, and substracting one result from the other such as

SELECT DISTINCT tt.ID, FIRST_VALUE(t) OVER W AS start   FROM (SELECT t.*,                ROW_NUMBER() OVER(ORDER BY t)               - ROW_NUMBER() OVER(PARTITION BY ID ORDER BY t) AS rn                   FROM (SELECT ID, t FROM records) t) tt WINDOW W AS           (PARTITION BY rn ORDER BY t ROWS               BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  ORDER BY start;   +----+----------+  | id |  start   |  +----+----------+  |  1 | 12:10:00 |  |  2 | 14:18:05 |  |  3 | 17:33:50 |  |  1 | 20:03:14 |  +----+----------+ 

Demo

Answered on September 1, 2020.
Add Comment

The simplest way to solve this is to use lag():

select id, t as start from (select t.*, lag(id) over (order by t) as prev_id       from t      ) t where prev_id is distinct from id; 

Basically, you just want the values when the id changes.

Note: I think that looking at this as a "typical" gaps-and-islands problem is overkill and complicates the solution.

Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.